Vba excel. функции для работы с датой и временем

YEARFRAC ()

Эта функция вычисляет количество дней между двумя датами как десятичное число, которое представляет собой долю. Эта функция особенно полезна, потому что другие функции даты и времени Excel возвращают только целое число. Используйте эту формулу для расчета сроков выхода на пенсию, получения возраста от дня рождения, расчета лет между датами, определения процента года, который является полным (до сих пор) и т. Д.

Примечание. Excel использует целые дни между две даты для вычисления доли года как десятичного значения.

Аргументы для этой функции:

start_date: дата начала

end_date: дата окончания

(необязательно): тип (или код) для использования (см. таблицу ниже)

Синтаксис выглядит следующим образом: = YEARFRAC (start_date, end_date, basis)

Примечание: Хотя Excel теперь позволяет пробелы между аргументами, вы все еще не могут использовать пробелы между функцией и открывающимися круглыми скобками. Большинство старых таймеров просто удаляют все пробелы из своих формул, чтобы избежать каких-либо шансов на такие ошибки.

Введите эту формулу в ячейку D4: = YEARFRAC (TODAY (), C4,1). Start_date в этой формуле — TODAY () — в отличие от фактической даты. Функция TODAY () возвращает сегодняшнюю дату. Дата в ячейке C4 является конечной_датой, а фаза — 1, что означает фактические дни в месяцах и фактических днях в годах.

Это может показаться запутанным, но Excel предоставляет пять вариантов расчета дней и лет в этой формуле. Европейские бухгалтеры, бухгалтеры и некоторые американские бухгалтеры работают в течение 30-дневных месяцев и 360-дневных лет; некоторые системы создаются для фактических дней в месяц, но только 360 дней в году; и некоторые из них определяются как фактические дни в каждом месяце и году. Коды для каждой опции следующие:

0 = US NASD 30-дневные месяцы / 360-дневные годы

1 = Фактические дни в месяцах / Фактические дни в годах

2 = Фактические дни в месяцах / 360 дней в годах

3 = Фактические дни в месяцах / 365 дней в годах

4 = Европейские 30 дней в месяцах / 360 дней в годах

Когда функция вводится, если вы нажимаете любую клавишу (например, пробел) после последний аргумент (C4), Excel предоставляет всплывающее меню, в котором перечислены пять параметров выше. Выберите нужный код из списка и нажмите Ввод .

Если вы не вводите базовый номер, значение по умолчанию равно 0, что означает 30-дневные месяцы и 360-дневные годы — не лучший выбор если вы ищете результаты в реальном времени.

Примечание: Последний столбец (E) в этой таблице показывает, как выглядит столбец D, когда он отформатирован как фракция с использованием кварталов. Например, E4 — это волосы менее двух лет, а E5 — волосы в течение одного года. E6 составляет 1-1 / 4 года, E7 — 8-3 / 4 года и т. Д.

EDATE ()

EDATE — это практическая функция для возвращения даты в несколько месяцев в будущем или в прошлом, используя положительное значение для будущих дат, отрицательное значение для прошлых дат. Например, вы можете использовать эту функцию для расчета даты выхода на пенсию или даты истечения срока действия, для расчета чей-то возраста с даты рождения или для добавления заданного количества лет к указанной дате.

Аргументы для этой функции:

start_date: укажите дату для представления даты начала (должен быть в действительном формате серийного номера Excel).

месяцев: количество месяцев до или после start_date

Синтаксис выглядит следующим образом: = EDATE (start_date, months)

1. В этом примере давайте рассчитаем некоторые даты выхода на пенсию для наших коллег на работе. Откройте пустой лист и введите следующие имена полей в заголовки столбцов в ячейках A3, B3, C3, D3 и E3 (соответственно): имя, день рождения, дата выхода на пенсию, время влево и в годах.

2. Введите имена и дни рождения в столбцах A и B.

3. Щелкните ячейку C4, затем перейдите к Формулы > Дата и время и выберите EDATE из списка. Вы также можете ввести формулу EDATE () вручную в ячейке C4: = EDATE (B4,12 * 62).

Примечание: Если вы не хотите вычислять и вводить определенное количество месяцев, это много легче умножить 12-кратный возраст выхода на пенсию (то есть 12 месяцев в год в возрасте 62 лет).

4. Скопируйте формулу из ячейки C4 в ячейки C5-C13, и вы получите дату, когда каждый человек может уйти в отставку.

См. Следующую функцию YEARFRAC () для вычисления времени, оставшегося для каждого сотрудника.

Как в Excel задать формулу для расчёта времени?

Как в Excel задать формулу, чтобы считалось время? Например, к предыдущему времени прибавить 3 минуты?

Время, в Excel является числом, точнее, десятичной дробью меньше единицы. (Соответственно дата — число, больше единицы. А дата с временем — это сумма этих чисел.).

Что бы данное число смотрелось в клетке как Дата, или Время, или Дата с Временем — для этого необходимо задать определенный формат с свойствах ячейки (Втор. кл. мыши по ячейке — пункт «Формат Ячейки»). Следственно, над любой датой и над любым временем можно производить операции сложения и вычитания. Так же надо помнить, что в Excel есть функция (ВРЕМЯ()), которая преобразовывает три значения (часы,минуты,секунды) в специальную десятичную дробь, которая, по сути, является временем «чч:мм:сс», если изменить формат ячейки на «Время-13:30:55».

Пусть в ячейке A1 у нас есть время «23:23:59», тогда
Пусть, клетках B1,C1,D1 у нас будет количество часов,минут,секунд, (целые числа) которое мы хотим добавить к нашему времени.
Для получения результата, запишем в клетке E1 формулу: =A1+ВРЕМЯ(B1;C1;D1)

Функция ВРЕМЗНАЧ

EOMONTH ()

Используйте эту функцию для определения даты последнего дня месяца (в будущем или в прошлом месяцы). Почему бы просто не проверить календарь или использовать один из тех мнемических стихотворений, как «30 дней в сентябре …»? Потому что при создании таблиц с множеством десятков вычислений стихи и календари не могут быть включены в формулу, необходимую для получения числовых результатов. Эта функция возвращает порядковый номер, который представляет определенную дату в Excel.

Аргументы для этой функции:

start_date: дата, которая представляет дату начала в действительном формате серийного номера Excel

месяцев: количество месяцев до или после start_date

Синтаксис выглядит следующим образом: = EOMONTH (start_date, months)

Примечание: В течение нескольких месяцев используйте положительное число для будущих дат и отрицательное число для прошлых дат.

1. Введите 10 дат в ячейках от A4 до A13.

2. Введите следующую формулу в ячейку B4: = EOMONTH (A4,1). A4 — это дата в этой ячейке, а число «1» означает один месяц. Это означает, что последний день месяца в течение месяца, прошедшего с даты в ячейке A4

Обратите внимание, что программа возвращает серийный номер Excel

3. Затем поместите курсор на ячейку B4 и нажмите функциональную клавишу F2 (Edit). Переместите курсор на букву «A» в формуле и нажмите функциональную клавишу F4 (абсолютная ссылка) три раза или пока формула не будет выглядеть так: = EOMONTH ($ A4,1).

4 , Для тех, кто не знаком с этой функцией, знак доллара перед буквой «А» означает, что ссылка столбца не изменится при копировании формулы. Теперь, когда мы копируем формулу в A4 вниз и вперед, строки ячейки меняются, но столбец остается тем же.

5. Затем отформатируйте серийный номер в ячейке B4 на среднесрочную дату; то есть, пн 26 февраля 2016.

6. Скопируйте ячейку B4 вниз от B5 до B13

Обратите внимание, что формат средних длин также копирует формулу

7. Затем скопируйте формулу в B4 — C4, D4 и E4. Отредактируйте каждую формулу, чтобы отразить новые месяцы: измените «1» на C4 на номер 6. Измените «1» в D4 на «12» и «1» в E4 на номер «18». Теперь скопируйте C4-E4 в C13-E13.

Не забудьте расширить столбцы для размещения новых форматов, а затем обратите внимание на то, как быстро вы сможете узнать эту информацию, используя эту ценную функцию

Примеры использования функции ВРЕМЯ для расчетов в Excel

Функция ВРЕМЯ возвращает десятичное число, выражающее определенное значение во времени. Возвращаемое значение зависит от формата ячейки. Если ячейка имеет формат «Общий», функция ВРЕМЯ вернет результат, отформатированный в виде даты. Числовой результат работы данной функции – десятичное число из интервала от 0 до 0,99988426, которое представляет собой значение в единицах измерения времени от 0:00:00 до 23:59:59.

Особенности работы функции ВРЕМЯ в Excel

Функция ВРЕМЯ в Excel из категории «Дата и время» предназначена для использования специалистами, выполняющими временные расчеты, например, экономистам-нормировщикам.

Функция имеет следующий синтаксис:

=ВРЕМЯ(часы; минуты; секунды)

Рассмотрим параметры, принимаемые на вход данной функцией:

Часы – обязательный параметр, принимающий значения в интервале от 0 до 32767, задающие часы. При вводе чисел со значениями свыше 23 выполняется вычитание наибольшего ближайшего меньшего значения, кратного 24, результат которого принимается в качестве входного параметра. Например, результат выполнения функции ВРЕМЯ(31;0;0) эквивалентен результату выполнения функции (7;0;0) и равен 07:00.
Минуты – обязательный параметр, принимающий числа в интервале от 0 до 32767, определяющие минуты. При вводе значений, превышающих 59, производится перерасчет в часы и минуты. Например, результат выполнения функции (0;134;0) эквивалентен результату выполнения функции ВРЕМЯ(2;14;0) и равен 02:14.
Секунды – обязательный параметр, принимающий значения от 0 до 32767, определяющий секунды. При вводе чисел свыше 59 производится автоматический перерасчет в часы, минуты и секунды. Например, ВРЕМЯ(0;0;190) эквивалентно значению (0;03;10) и равно 00:03:10.

Перечисленные параметры – обязательные. Если один или несколько из них имеют значение 0 (нуль), это должно быть указано явно.

Примеры использования функции ВРЕМЯ в Excel

Пример 1. Длина маршрута общественного транспорта составляет 34 минуты. Время отправки из депо – 8:25 утра. Необходимо узнать, во сколько транспорт прибудет в депо пройдя полный маршрут, пренебрегая возможными задержками на пути.

Заполним таблицу исходных данных. Укажем часы, минуты и секунды отправки транспортного средства из депо, а также длину маршрута:

Для решения задачи необходимо в ячейке E3 ввести следующую формулу:

Значения аргументов функций:

A3 – часы отправки из депо;
B3 – минуты выезда;
C3 – секунды выезда;
D3 – протяженность маршрута, выраженная в минутах.

То есть, транспортное средство вернется в депо в 8:59 AM.

Как рассчитать прибытие автомобиля к пункту назначения?

Пример 2. Автомобиль движется из одного города в другой со средней скоростью 80 км/ч. Известно, что он выехал в 12:10, а расстояние между городами составляет 420 км. Необходимо определить время прибытия в пункт назначения.

Внесем в таблицу исходных данных часы, минуты и секунды отправки автомобиля:

Определим длительность нахождения автомобиля в пути, зная, что время равно частному от деления расстояния на скорость:

В ячейке F будет использована следующая формула:

E3 – расстояние между городами, км;
D3 – средняя скорость движения авто, км/ч;
60 – коэффициент для перевода десятичной дроби в минуты.

Введем в ячейке G2 следующую формулу:

Значения аргументов функций:

A3 – часы отправки из депо;
B3 – минуты выезда;
C3 – секунды выезда;
F3 – вычисленное ранее время нахождения в пути, выраженное в минутах.

То есть, автомобиль приедет во второй город в 5:25 PM.

Как посчитать длительность производственного процесса на оборудовании в Excel?

Пример 3. Длительность производственного процесса составляет 739 секунд. Необходимо выразить эту длительность в минутах и секундах.

Укажем исходное значение минут в ячейке A2:

Воспользуемся рассматриваемой формулой для вычисления времени:

Результатом выполнения формулы является значение 12:12 AM, что не соответствует условию задачи. Воспользуемся функцией МИНУТЫ, чтобы выделить искомую часть времени:

B2 – ячейка с полученным промежуточным результатом вычислений.

То есть длительность производственного процесса для изготовления единицы продукции составляет 12 минут на одной единице оборудования цеха.

6 функций Google Sheet, которые помогают работать с данными

Google Sheet – универсальный инструмент для организации и вычисления данных. Этот облачный сервис – большое преимущество перед Excel. С ним легко наладить совместную работу, автоматизировать данные и не переживать об их возможной утере – изменения сохраняются каждую секунду.

Google Sheets похожи на Excel, многие функции программируются точно так же, как и в привычной электронной таблице. Но есть несколько лайфхаков, которые помогают упростить работу с данными.

Присоединение текста в «Гугл Таблицах»

Иногда в таблицах нужно использовать текст. Может, вам необходимо суммировать некоторые ключевые значения, а может, надо генерировать html-коды.

Знак & объединяет значения ячеек и выводит их в текстовом формате в другой ячейке. Кавычки “” заменяют текст, который вы хотите вставить.

Например, у вас есть такая таблица с ячейками:

Пример объединения текста в «Гугл Таблицах»

Вам нужно, чтобы в одной ячейке появилось общее: «4 ящика и 5 коробок». Вручную писать, если ячеек много – долго, с помощью простой формулы это можно автоматизировать.

https://youtube.com/watch?v=qRSERChS0Pc

Для нашего примера формула: = A1 & «» & B1 & «и» & A2 & «» & B2 объединяет значения. Выделите ячейку, где должна отображаться сводная информация, и введите эту формулу. Готово. В новой ячейке появилось «4 ящика и 5 коробок».

Объединяем разные значения в ячейках таблиц и выводим их в одну

Если вам нужно объединить множество значений, то в помощь – функция JOIN. Укажите, какой символ надо добавить между значениями ячеек, к которым вы хотите присоединиться.

Например, нужно объединить в строку данные со столбцов:

Пример для объединения ячеек в столбце в одну

Пишем в новой ячейке формулу:

=JOIN(«-«; A1:A100)

Объединение цифровых ячеек в одну

Если вместо знака «−» поставить, к примеру, «+», то результат будет такой:

Ставьте нужный вам знак между кавычками

INDEX: возврат первого или последнего значения в листе «Гугл Таблицы»

С таблицами работать проще, если есть фиксированный набор данных. Но при добавлении новых данных через определенные регулярные промежутки времени, например новой строки каждую неделю, требуется такое же регулярное обновление, чтобы все функции работали четко.

Вам надо рассчитать изменение между нижней ячейкой с предыдущим значением. Это делает функция INDEX (). Она позволяет вернуть значение ячейки, указав, какую строку и столбец искать в массиве данных.

Формула:

= INDEX (A: A; 1;1) – всегда возвращает первую ячейку в столбце А.

Функция INDEX возвращает значение выбранной ячейки

А если объединить INDEX () и COUNTA (), то будет возвращаться последнее значение в таблице

= INDEX (A:A; COUNTA (A:A); 1)

Импорт данных в «Гугл Таблицы»

«Гугл Таблицы» умеют импортировать данные. Вы можете извлекать нужные вам цифры из электронных таблиц с различных источников, включая XML, HTML, RSS и CSV – удобно для импорта списков сообщений в блогах, каких-либо настроек, списка товарных запасов и так далее. Это можно сделать при помощи функции IMPORTXML:

= IMPORTXML (» https://en.wikipedia.org/wiki/Moon_landing «; «//a/@href»)

Импорт содержимого списка или таблицы

Содержимое списка или таблицы можно импортировать из указанного URL-адреса с помощью такой формулы:

=IMPORTHTML(«ссылка»; «table»; 4)

Импорт содержимого ссылок в «Гугл Таблицы»

Импорт CSV-файла из указанного URL-адреса:

= IMPORTDATA («ссылка на файл CSV» )

Импорт данных из файла CSV

Импорт данных из RSS или atom-канала

Импортировать данные из RSS-канала или atom тоже не сложно:

= IMPORTFEED (» http://news.google.com/?output=atom» )

Импорт данных из файла RSS или atom

Эти 6 функций помогут вам сделать работу с «Гугл Таблицами» проще и эффективней. Используйте их.

Как рассчитать и отобразить отрицательное время в Excel

Создание пользовательских форматов

NETWORKDAYS.INTL ()

Эта функция вычисляет количество рабочих дней между двумя указанными датами, за исключением выходных. Это полезно, если вы считаете дни работы (или школьные дни) в течение четверти, семестра или года.

Разница между этой функцией и другими подобными — это возможность выбрать, какие дни считаются выходными днями. Не все получают по субботам и воскресеньям. Некоторые рабочие получают понедельники и вторники, некоторые получают среду и пятницы. С помощью этой функции вы можете установить выходные дни в своем индивидуальном расписании.

Кроме того, эта функция позволяет вам выбирать и устанавливать отдельные каникулы в заданные сроки. Например, в четвертом квартале календарного года в октябре два, два в ноябре и два в декабре, если вы включили Хэллоуин и Рождество. Если нет, то вы можете установить праздники на четыре в четвертом квартале вместо шести.

Это номера кодов, которые соответствуют дням недели, которые необходимо настроить для ваших дней выходных дней.

Число выходных дней

1 Суббота, Воскресенье

2 Воскресенье, Понедельник

3 Понедельник, вторник

4 Вторник, среда

5 среда, четверг

6 Четверг, пятница

7 Пятница, Суббота

11 Только воскресенье

12 Только понедельник

Только 13 Только

Только 14 Только

15 Только четверг

16 Только пятница

17 Только суббота

Если вы оставите этот параметр пустым (или неидентифицированным), по умолчанию он будет равен номеру 1.

Введите праздничные дни в виде диапазона ячеек, где указаны фактические даты отпуска (например, F4: F10) или в виде списка серийных номеров которые представляют фактические даты отпуска.

Аргументы для этой функции:

start_date: дата начала

end_date: дата окончания

выходные: настройка, для которой дни недели следует рассматривать как выходные ( необязательный параметр)

праздники: ссылка на даты, которые должны считаться нерабочими днями (необязательный параметр)

Синтаксис выглядит следующим образом: = NETWORKDAYS.INTL (start_date, end_date, , )

1. Введите следующие заголовки полей / столбцов по столбцам A, B, C, F и G соответственно: Дата начала , Дата окончания , Число рабочих дней , Наконец, введите Праздники (центрированные и объединенные над F и G).

2. Введите некоторые случайные даты в столбцах A и B. Убедитесь, что конечная дата не является датой перед начальной датой.

3. Введите некоторые случайные праздники (имена и даты) в столбцы F и G.

4. Поместите курсор в ячейку C4. Перейдите к Формулы > Дата и время и выберите NETWORKDAYS.INTL .

5. В диалоговом окне «Аргументы функций» щелкните внутри поля «Пуск_Дата», затем нажмите на свой курсор в ячейке A4.

6. Нажмите клавишу Tab до поля поля End_Date и щелкните курсор в ячейке B4.

7. Нажмите клавишу Tab до поля поля Weekend и введите один из определенных кодов выходных дней (помните, 1 = по субботам и воскресеньям).

8. Нажмите клавишу Tab до поля поля Праздники , затем выберите / выделите ячейки с G4 по G11. Нажмите OK .

Важно: Прежде чем копировать эту формулу из ячейки C4 вниз до C5-C11, используйте функциональную клавишу F4, чтобы сделать ячейки праздника абсолютными: = NETWORKDAYS.INTL (A4, B4,1, $ G $ 4: $ G $ 11), поэтому дни в диапазоне Holidays всегда от G4 до G11

Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

Сложение / вычитание времени в Excel

Как вычитать даты в Excel

6 функций Google Sheet, которые помогают работать с данными

Google Sheet — это универсальный инструмент для систематизации и расчета данных. Этот облачный сервис — большое преимущество перед Excel. С ним легко сотрудничать, автоматизировать данные и не беспокоиться о их возможной потере — изменения сохраняются каждую секунду.

Google Таблицы похожи на Excel, многие функции программируются так же, как в знакомой электронной таблице. Но есть несколько уловок, которые помогут упростить работу с данными.