История о том, как я пропустил свидание с очаровательной блондинкой…
Вы никогда не попадали впросак из-за того, что неправильно читали дату? «Да детей в первом классе учат читать календарь, называть месяца по порядку, считать количество дней, прошедших с того или иного момента и так далее», – скажете вы.
И я с вами соглашусь. Но, позвольте спросить: 07-08-2017 и 07.08.2017 – это одна и та же дата?
Вот и мне казалось, что это глупый вопрос, пока не познакомился с одной симпатичной девушкой. Мы долго переписывались, выяснилось, что она живет в Америке и скоро приедет в Россию. Мы договорились о встрече. День Х был назначен на 07-08-2017.
Наступил июль. А я с нетерпением ждал августа. 7 июля рассерженная девушка разрывала мою телефонную трубку, крича на весь ресторан какой я «сякой» (цитировать не буду). Проблема была в том, что я сидел дома, считая дни до 7 августа, а она ждала меня в назначенном месте встречи.
Вот тогда стало все понятно: в США формат даты выглядит следующим образом: MM-DD-YYYY, а мы привыкли к иной записи: DD.MM.YYYY. Во многих европейских странах используется совсем другой стандарт: YYYY-MM-DD. Вот и поди, разберись какая дата написана! Да и в голову не придет ни нам, ни им, что бывает по-разному.
С тех пор я очень внимательно отношусь к написаниям даты. Хорошо, что хоть формат времени у всех одинаковый… а то представляете, какая путаница была бы!
Вообще всегда дата и время были самыми распространенными типами данных и одновременно самыми сложными. Любые таблицы содержат дату или время, будь то данные о транзакциях, о продажах, расписание чего-либо, статистика, финансы…
Поэтому этой теме уделяется пристальное внимание. Мы вам расскажем о том, какие процессы и преобразования с датами можно делать с помощью специальных функций в Excel.
Как в Excel хранятся даты
Источником большей части путаницы с датами и временем в Excel является способ хранения информации программой.
Иногда бывает, что пишешь в ячейку дату, например, 03.06.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/1440)) = 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ЕГОДНЯ()
Функция СЕГОДНЯ всегда возвращает текущую дату в формате даты:
ТДАТА()
Аналогичная функция всегда возвращает текущую дату и время:
ДАТАМЕС () и КОНМЕСЯЦА ()
Функция ДАТАМЕС возвращает порядковый номер даты, отстоящей на заданное количество месяцев вперед или назад от заданной даты.
Функция КОНМЕСЯЦА возвращает порядковый номер последнего дня месяца, отстоящего на указанное количество месяцев от даты, указанной в аргументе.
Аргумент число_месяцев может быть любым числом, но будет использоваться только целочисленное значение (например, если Вы напишете 2,8, то программа округлит до 2).
РАБДЕНЬ()
Иногда может быть полезно рассчитывать дату отстоящую от некоторой даты на несколько рабочих дней вперед или назад. Нам часто говорят: «Посылка придёт через 10 рабочих дней». Вот и сиди-считай, когда ее нужно получать… В такой ситуации удобно использовать функцию Excel РАБДЕНЬ. Функция возвращает порядковый номер даты. Поэтому, если вам нужна дата в формате: ДД.ММ.ГГГГ, то ячейку придется отформатировать.
В качестве аргументов функции выступают: начальная дата (от которой считаем); количество рабочих дней; праздники (если таковые имеются, то указываются даты праздников).
Если нужно найти дату, следующую за известной нам, то количество дней указывается целым положительным числом, как на рисунке выше. Если нужно найти предыдущие даты, то – целым отрицательным.
Аргумент [праздники] не является обязательным. Но если таковые имеются, то нужно указывать дату праздника. Если такой день один, то его можно указать непосредственно в формуле в кавычках. Если же праздники выпадают на несколько дней, например, на 21.08.2020 и 25.08.2020, то эти даты нужно внести в отдельные ячейки и в качестве параметра указать диапазон этих ячеек.
РАБДЕНЬ.МЕЖД ()
Для более новых версий Excel (начиная с 2010) существует еще одна версия предыдущей формулы: РАБДЕНЬ.МЕЖД. Она тоже возвращает порядковый номер даты, отстоящей вперед или назад на заданное количество рабочих дней. Но РАБДЕНЬ.МЕЖД дает возможность настраивать дни, которые являются выходными.
Синтаксис функции:
= РАБДЕНЬ.МЕЖД (нач_дата, число_дней, [выходные], [праздники])
Предположим, человек работает в понедельник, среду и пятницу, а вторник и четверг у него выходные. При этом праздники те же, что и в предыдущем примере.
Параметры: нач_дата, число_дней, и [праздники] те же, что и в примере работы функции РАБДЕНЬ.
Основная «фишка» этой функции – моделирование любого графика.
В качестве параметра [выходные] могут выступать следующие значения:
Если Вы не нашли подходящего режима в предложенной таблице, то можете задать любой график с помощью строки из семи цифр (0 или 1). 0 – рабочий день, 1- выходной. Каждое значение соответствует дню недели.
Так, если человек работает только в понедельник, среду и пятницу, то его рабочий режим можно описать следующей строкой: «0101011».
Функции извлечения дат
ДЕНЬ(), МЕСЯЦ() и ГОД()
Все предыдущие функции в качестве результата возвращают, как мы уже упомянули, порядковый номер даты. Но эти номера не позволяют легко извлечь годы, месяцы и дни, не говоря уже о часах, минутах и секундах.
Для этого в Excel есть специальные функции для извлечения каждого из этих значений. Для работы с календарем есть ДЕНЬ, МЕСЯЦ и ГОД. Синтаксис прост:
Функции извлечения времени
ЧАС(), МИНУТА() и СЕКУНДА()
Данные функции аналогичны функциям извлечения дат.
Посмотрим на примере:
Дополнительные функции извлечения даты
ДЕНЬНЕД() и НОМНЕДЕЛИ()
Довольно часто, бывает так: нам говорят: «Давайте встретимся 24.08.2020!». Многие люди не могут по дате сориентировать: свободны они или нет. У нас так условно «заведено» – мыслить днями недели. Для нас понедельник, например, (какая бы дата ни была) – день тяжелый, а вот пятница – «проказница».
Иногда полезно найти зависимость продаж от дней недели. Как это сделать?! Не станешь же все даты размечать.
На такой случай есть полезная функция: ДЕНЬНЕД(). Эта функция выдает в качестве результата число от 1 до 7, которое соответствует дню недели. Синтаксис очень прост:
= ДЕНЬНЕД (дата_в_числовом_формате, [тип])
Параметр [тип] показывает, с какого числа начинается неделя и может принимать следующие значения:
Замечание! Кто внимательно смотрел, те заметили, что варианты 1 и 17; 2 и 11 – одинаковые. Действительно, функционально эти значения одинаковы. Но первые значения предназначены для совместимости с более ранними версиями Excel.
По умолчанию параметр [тип] принимает значение 1 (то есть неделя начинается с воскресенья). Но в России первым днем недели принято считать понедельник, поэтому для расчетов мы будет использовать значение параметра [тип] = 2.
Функция НОМНЕДЕЛИ() возвращает, как понятно из названия, номер недели, на которую приходится конкретная дата.
Синтаксис совпадает с предыдущей формулой.
Рассмотрим работу функции на примере:
Видно, что 20 августа 2020 года – это четвертый день недели (четверг) и выпадает на 34 неделю с начала года.
Значение параметра [тип_возвр] принимает фиксированные значения из списка:
Подсчет и отслеживание дат
Как мы уже говорили, благодаря тому, что Excel хранит даты в виде чисел, с датами можно делать операции сложения и вычитания. Операцию сложения иллюстрировали функции РАБДЕНЬ и РАБДЕНЬ.МЕЖДУ. Рассмотрим функции, позволяющие вычитать одну дату из другой.
ДНИ()
Самая простая функция. Возвращает число, соответствующее количеству дней между датами. При этом результат может быть и отрицательным, если начальная дата больше конечной.
ЧИСТРАБДНИ() и ЧИСТРАБДНИ.МЕЖДУ()
ЧИСТРАБДНИ – функция рассчитывающая количество рабочих дней при пятидневной рабочей неделе. Синтаксис формулы следующий:
= ЧИСТРАБДНИ (нач_дата, кон_дата, [праздники])
Аргументы нач_дата и кон_дата могут быть любыми датами в кавычках или ссылками на ячейки.
Параметр [праздники], как и в функции РАБДЕНЬ является необязательным, но позволяет дисквалифицировать определенные дни из расчета.
ЧИСТРАБДНИ.МЕЖДУ используются в более новых версиях Excel (2010 и более поздних версий). По аналогии с РАБДЕНЬ.МЕЖДУ функция дает возможность настраивать режим «выходных» дней.
Синтаксис ЧИСТРАБДНИ.МЕЖДУ() следующий:
= ЧИСТРАБДНИ.МЕЖДУ (нач_дата, кон_дата, [выходные], [праздники])
Аргументы нач_дата, кон_дата и [праздники] работают точно так же, как и в ЧИСТРАБДНИ().
[Выходные] могут принимать следующие значения:
ДОЛЯГОДА ()
Иногда полезно измерить, какова разница между двумя датами относительно целого года.
Давайте проведем грубые расчеты того, сколько россияне находятся в «нетрезвом» состоянии на новогодних праздниках относительно всего года. Воспользуемся функцией ДОЛЯГОДА():
=ДОЛЯГОДА нач_дат, кон_дата, [базис])
Первые аргументымогут быть любыми датами или ссылками на ячейки.
[базис] является необязательным аргументом, он позволяет указать «правила» для измерения разницы. В большинстве случаев используется вариант 1:
0.02(2) года… Какая маленькая цифра. А еще говорят, что у нас самый «загульный» народ. А мы опровергли ложные домыслы!
Преобразование даты и времени из текста
ДАТАЗНАЧ () и ВРЕМЯЗНАЧ ()
Все вышеперечисленные функции отлично работают с порядковыми номерами в формате даты в Excel. К сожалению, даты и время часто импортируются на листы в виде текста. Чтобы перевести текстовые строки в формат даты и времени используют функции ДАТАЗНАЧ() и ВРЕМЯЗНАЧ(). Синтаксис для каждого следующий:
= ДАТАЗНАЧ (дата_как_текст)
= ВРЕМЯЗНАЧ (время_как_текст)
Аргументы функций могутпринимать любую текстовую строку, которая выглядит как дата или время.
Примеры работы функций:
Будьте внимательны!
Одна из проблем, с которой часто сталкиваются пользователи Excel, заключается в том, что программа иногда неверно интерпретирует текстовые поля как даты. Пример здесь:
Видите, что разное положение цифры 20 влияет на исходный результат. Поэтому будьте осторожны при вводе дат, особенно если Вы импортируете данные из других источников. Чтобы не получилось, как в моей истории в начале статьи))
Помните: время очень ценный ресурс. И каждая секунда на счету. Excel знает об этом все. Поэтому будьте внимательнее и не теряйте время!