Вы когда-нибудь злились из-за того, что неправильно прочитали дату? «Да, первоклассников учат читать календарь, называть месяцы по порядку, считать количество дней, прошедших с того или иного времени, и так далее», — скажете вы.
И я с тобой согласен. Но позвольте мне спросить вас: 08.07.2017 и 08.07.2017 совпадают?
Так что мне казалось, что это глупый вопрос, пока я не встретил симпатичную девушку. Мы долго переписывались, выяснилось, что он живет в Америке и скоро приедет в Россию. Решили встретиться. День X был назначен на 08.07.2017.
Наступил июль. И я с нетерпением ждала августа. 7 июля рассерженная девушка сняла мой телефон с крючка и кричала на весь ресторан, что «это я» (упоминать не буду). Проблема заключалась в том, что я сидел дома и считал дни до 7 августа, а она ждала меня в назначенном месте встречи.
именно тогда все стало ясно: в США формат даты такой: ММ-ДД-ГГГГ, но мы привыкли к другой записи: ДД.ММ.ГГГГ. Многие европейские страны используют совершенно другой стандарт: ГГГГ-ММ-ДД. Так что пойди выясни, какая дата написана! И ни нам, ни им никогда бы в голову не пришло, что это произойдет по-разному.
С тех пор я очень внимательно следил за написанием даты. Хорошо, что хоть формат времени у всех одинаковый . а то можно представить, как это было бы запутанно!
В общем, дата и время всегда были самыми распространенными и в то же время самыми сложными типами данных. Любая таблица содержит дату или время, будь то данные о транзакциях, продажах, планировании чего-либо, статистике, финансах…
Поэтому этой теме уделяется много внимания. Мы расскажем, какие процессы и преобразования с датами можно выполнить с помощью специальных функций в Excel.
Как в Excel хранятся даты
Источником большой путаницы в отношении даты и времени в Excel является способ хранения информации в программе.
Иногда случается написать дату в ячейке, например 06.03.2014, и на выходе получается что-то непонятное: 41793. Откуда это число?!
Дело в том, что Excel хранит даты как целые числа, которые представляют количество дней, прошедших с начала 1900 года, поэтому числа отформатированы для отображения как даты.
То есть 1 января 1900 года — 1.
2 января 1900 года — 2 года.
20 августа 2020 года будет сохранено как 44063 и так далее.
Чтобы узнать, какое целое число присвоено определенной дате, просто преобразуйте формат ячейки с даты в числовой.
Это звучит запутанно, но благодаря такому подходу намного проще складывать, вычитать и считать количество дней между датами (операция с двумя, даже большими числами, проще и быстрее, чем с шестью: день, месяц, год).
Для более наблюдательных уже должен был назреть вопрос: как сохраняются даты до 1 января 1900 года? Дело в том, что они не распознаются как дата в Excel. То есть на дату нет «отрицательных» порядковых номеров. Следовательно, как бы вы не меняли формат ячейки, поскольку дата была — 19.02.1861, так и останется.
Как в Excel хранится время
Excel сохраняет время, используя тот же формат последовательной нумерации, что и даты. День Excel «начинается», как и мы в полночь. Каждый час составляет 1/24 дня, поэтому он представлен в виде десятичного числа: 0,041666…
Если вы хотите знать, что Excel «видит», когда вы вводите 9:00 в ячейку, умножьте 0,041666 на 9.
Минуты в Excel представлены числом: 1 / (24 * 60) = 1/1440 = 0,000694.
Второй — 1 / (24 * 60 * 60) = 1/86400 = 0,00001157407…
Итак, если вы напишете в ячейке 8:30, Excel выполнит следующий расчет:
(8 * (1/24)) + (30 * (1/440)) = 0,354167
Так что, если вы опаздываете на работу на полчаса и хотите сообщить об этом своему боссу, напишите ему / ей и скажите ему, что вы будете там по телефону 0,02083333….
Если время указано без даты, например 9:00, Excel сохраняет его как 9:00 0 января 1900 г. Это может затруднить выполнение математических вычислений для значений времени (без даты), поскольку вычитание 6 : 00 с 3:00 станет отрицательным и будет считаться ошибкой, поскольку 0,125 — 0,25 = -0,125, что будет отображаться как #########.
Если время указано вместе с датой, проблем не будет. Тогда 11:00 20 августа 2020 года будет сохранено как 44063.46.
Функции, задающие дату и время
Excel предоставляет несколько функций для упрощения объявления дат. Чтобы узнать о других важных функциях, которые упростят вашу работу с Excel, ознакомьтесь с нашим бесплатным руководством.
CЕГОДНЯ()
Функция СЕГОДНЯ всегда возвращает текущую дату в формате даты:
ТДАТА()
Аналогичная функция всегда возвращает текущую дату и время:
ДАТАМЕС () и КОНМЕСЯЦА ()
Функция EDATE возвращает порядковый номер даты, которая находится на указанное количество месяцев вперед или назад указанной даты.
Функция EONMONTH возвращает порядковый номер последнего дня месяца, который представляет собой количество месяцев, заданное датой, указанной в аргументе.
Аргумент num_months может быть любым числом, но будет использоваться только целочисленное значение (например, если вы напишете 2.8, программа округлится до 2).
РАБДЕНЬ()
Иногда бывает полезно рассчитать дату, которая на несколько рабочих дней впереди или позже определенной даты. Нам часто говорят: «Посылка прибудет через 10 рабочих дней». Так что расслабьтесь и считайте, когда вам нужно его получить… В такой ситуации удобно использовать функцию РАБОЧИЙ ДЕНЬ Excel. Функция возвращает порядковый номер даты. Следовательно, если вам нужна дата в формате: ДД.ММ.ГГГГ, ячейку нужно будет отформатировать.
Аргументы функции: дата начала (от которой ведется отсчет); количество рабочих дней; праздники (если есть, указываются даты праздников).
Если вам нужно найти дату после известной нам, количество дней указывается положительным целым числом, как на рисунке выше. Если нужно найти более ранние даты, то — с отрицательным целым числом.
Аргумент [праздники] не обязателен. Но если они есть, то нужно указать дату праздника. Если есть только один из этих дней, его можно указать прямо в формуле в кавычках. Если праздничные дни приходятся на несколько дней, например 21.08.2020 и 25.08.2020, эти даты должны быть введены в отдельные ячейки, а диапазон этих ячеек должен быть указан в качестве параметра.
РАБДЕНЬ.МЕЖД ()
Для более новых версий Excel (начиная с 2010 г.) существует другая версия старой формулы: WORKDAY.INTL. Он также возвращает порядковый номер даты вперед или назад на указанное количество рабочих дней. Но WORKDAY.INTL позволяет настраивать праздники.
Синтаксис функции:
= WORKDAY.INTL (начальная_дата, число_дней, [выходные], [праздники])
Предположим, человек работает в понедельник, среду и пятницу, а выходные — по вторникам и четвергам. В этом случае праздничные дни такие же, как в предыдущем примере.
Параметры: начальная_дата, число_дней и [праздники] такие же, как в примере функции РАБДЕНЬ.
Основная особенность этой функции — моделирование любого графика.
Следующие значения могут использоваться в качестве [вывода]:
Если вы не нашли подходящий режим в предложенной таблице, вы можете установить любую диаграмму, используя семизначную строку (0 или 1). 0 — рабочий день, 1 — выходной. Каждое значение соответствует дню недели.
Итак, если человек работает только по понедельникам, средам и пятницам, его способ работы можно описать следующей строкой: «0101011».
Функции извлечения дат
ДЕНЬ(), МЕСЯЦ() и ГОД()
Следовательно, все предыдущие функции возвращают, как мы уже сказали, порядковый номер даты. Но эти числа не позволяют легко извлечь годы, месяцы и дни, не говоря уже о часах, минутах и секундах.
Для этого в Excel есть специальные функции для извлечения каждого из этих значений. Для работы с календарем есть ДЕНЬ, МЕСЯЦ и ГОД. Синтаксис прост:
Функции извлечения времени
ЧАС(), МИНУТА() и СЕКУНДА()
Эти функции аналогичны функциям для получения дат.
Посмотрим на пример:
Дополнительные функции извлечения даты
ДЕНЬНЕД() и НОМНЕДЕЛИ()
Очень часто такое случается: нам говорят: «Давай встретимся 24.08.2020!». Многие люди не могут сориентироваться по дате, свободны они или нет. У нас так условно «настроено» — думать днями недели. Для нас, например, понедельник (независимо от даты) — тяжелый день, а пятница — «плохая девочка».
Иногда бывает полезно найти зависимость продаж от дней недели. Как это сделать?! Вы не можете отметить все даты.
В этом случае есть полезная функция: WEEKDAY (). Эта функция возвращает число от 1 до 7, которое соответствует дню недели. Синтаксис очень прост:
= WEEKDAY (date_num_format; [тип])
Параметр [type] показывает дату, с которой начинается неделя, и может принимать следующие значения:
Комментарий! Любой, кто приглядывался, заметил, что варианты 1 и 17; 2 и 11 одинаковы. Фактически, функционально эти значения совпадают. Однако первые значения предназначены для совместимости с более ранними версиями Excel.
По умолчанию для параметра [type] установлено значение 1 (то есть неделя начинается в воскресенье). Но в России первым днем недели считается понедельник, поэтому для расчетов мы будем использовать значение параметра [type] = 2.
Функция НОМЕР НЕДЕЛИ () возвращает, как следует из названия, номер недели, на которую приходится определенная дата.
Синтаксис такой же, как у предыдущей формулы.
Рассмотрим, как работает функция, на примере:
Видно, что 20 августа 2020 года — четвертый день недели (четверг), и он приходится на 34-ю неделю с начала года.
Значение параметра [return_type] принимает фиксированные значения из списка:
Подсчет и отслеживание дат
Как мы упоминали ранее, поскольку Excel хранит даты как числа, вы можете выполнять операции сложения и вычитания с датами. Операция сложения проиллюстрирована функциями РАБДЕНЬ и РАБДЕНЬ. Рассмотрим функции, позволяющие вычесть одну дату из другой.
ДНИ()
Самая простая функция. Возвращает число, соответствующее количеству дней между датами. В этом случае результат может быть отрицательным, если дата начала больше даты окончания.
ЧИСТРАБДНИ() и ЧИСТРАБДНИ.МЕЖДУ()
ЧИСТРАБДНИ — функция, рассчитывающая количество рабочих дней для пятидневной рабочей недели. Синтаксис формулы следующий:
= РАБОЧИЕ ДНИ (начальная_дата, конечная_дата, [праздники])
Аргументы start_date и end_date могут быть любой датой в кавычках или ссылкой на ячейку.
Параметр [праздники], как и в функции РАБОЧИЕ ДНИ, является необязательным, но позволяет исключить определенные дни из расчета.
NETWORKDAYS BETWEEN используются в более новых версиях Excel (2010 и новее). По аналогии с WORKDAY.BETWEEN эта функция позволяет настроить режим «выходные.
Синтаксис NETWORKDAYS.BETWEEN () следующий:
= NETWORKDAYS.BETWEEN (начальная_дата, конечная_дата, [выходные], [праздники])
Аргументы начальная_дата, конечная_дата и [праздники] работают так же, как ЧИСТРАБДНИ().
[Праздники] могут иметь следующие значения:
ДОЛЯГОДА ()
Иногда полезно измерить, насколько разница между двумя датами по отношению к полному году.
Приблизительно посчитаем, сколько россиян «выпито» в новогодние праздники по сравнению с целым годом. Мы используем функцию LENGTH():
= LENGTH начальная_дата, конечная_дата, [базовая])
Первыми аргументами могут быть даты или ссылки на ячейки.
[base] — необязательный аргумент, позволяющий указать «правила» для измерения разницы. В большинстве случаев используется вариант 1:
0,02 (2) года… Какая маленькая сумма. А еще говорят, что у нас самые «веселые» люди. И мы опровергли ложные домыслы!
Преобразование даты и времени из текста
ДАТАЗНАЧ () и ВРЕМЯЗНАЧ ()
Все вышеперечисленные функции хорошо работают с порядковыми числами в формате даты в Excel. К сожалению, даты и время часто импортируются в листы в виде текста. Чтобы преобразовать текстовые строки в формат даты и времени, используйте функции DATEVALUE () и TIMEVALUE (). Синтаксис для каждого следующий:
= DATEVALUE (date_as_text)
= ВРЕМЗНАЧ (время_в_тексте)
Аргументы функции могут принимать любую текстовую строку, напоминающую дату или время.
Примеры работы функций:
Осторожно!
Одна из проблем, с которой часто сталкиваются пользователи Excel, заключается в том, что программа иногда неверно интерпретирует текстовые поля как даты. Пример здесь:
Вы можете видеть, что различное положение числа 20 влияет на исходный результат. Поэтому будьте осторожны при вводе дат, особенно если вы импортируете данные из других источников. Чтоб не получилось, как в моем рассказе в начале статьи))
Помните, время — очень ценный ресурс. И каждая секунда на счету. Excel знает об этом все. Поэтому будьте осторожны и не теряйте время зря!