Резюме
- Даты и время хранятся в виде серийных номеров в Microsoft Excel, что позволяет быстро выполнять вычисления и сравнения.
- Порядковые номера дат начинаются с 1 января 1900 года, а время представляет собой десятичное представление 24-часового периода.
- Понимание порядковых номеров даты и времени в Excel может помочь при вычислении разницы между датами, добавлении дней, корректировке времени и многом другом.
В Microsoft Excel, если выбрать ячейки, содержащие дату или время, и применить общий числовой формат, это сгенерирует то, что кажется случайными числами. На самом деле, эти числа совсем не случайны — это последовательные серийные номера, которые служат определенной цели.
Даты и время хранятся в Excel в виде серийных номеров
В Microsoft Excel можно отформатировать ячейку как дату или время. В этом примере ячейка F1 отформатирована как дата.
Excel распознает даты, введенные разными способами. Например, вы можете вводить даты с дефисами (например, 2024-12-16 или 16-дек-2024), косыми чертами (например, 2024/12/16 или 24/12/16) или пробелами и запятыми (например, 16 дек, 2024). Однако если вы используете точку для разделения года, месяца и дня, Excel распознает это как текст.
С другой стороны, значение в ячейке F2 отформатировано как время.
Вы можете ввести время в Excel как часы и минуты, разделенные двоеточием (например, 10:00), и вы также можете включить секунды (например, 10:00:00), если хотите. Чтобы включить AM или PM, просто добавьте пробел и введите AM or PM после ввода времени. Для часового времени можно ввести час, а затем AM или PM (например, 10 AM).
Форматирование по умолчанию дат, времени, валют и других элементов в Excel зависит от настроек вашей системы. Вы можете изменить эти настройки в Windows 11, изменив свой регион.
Однако, под поверхностью, ячейки с датами и временем содержат скрытые последовательные серийные номера. Вы можете увидеть их, выбрав ячейки, отформатированные как даты и время, и нажав «Общие» в группе «Число» вкладки «Главная» на ленте.
Обратите внимание, что серийные номера дат — это целые числа, а серийные номера времени — десятичные. Это потому, что время считается частью дня, и вы можете иметь дату и время вместе в одной ячейке. Например, если вы введете 2024-12-16 10:00:00 в ячейку, серийный номер представляет собой как целое число даты, так и десятичное число времени.
Как генерируются порядковые номера даты и времени в Excel
Порядковые номера дат во всех современных версиях Microsoft Excel начинаются с 1, что соответствует 1 января 1900 года, и увеличиваются с каждым последующим днем.
Например, серийный номер для 31 января 1900 года — 31, так как это 31 день после начальной точки. Однако эта система включает серийный номер для 29 февраля 1900 года, хотя 1900 год не был високосным. Таким образом, каждый серийный номер после этой даты на единицу отличается от того, что вы ожидаете, но это важно отметить только в том случае, если вы имеете дело с датами в первом квартале 1900 года.
Кроме того, Excel не распознает даты до 1 января 1900 года. В результате более ранние даты не имеют порядковых номеров, поэтому их нельзя использовать в вычислениях.
Порядковые номера дат в ранних версиях Excel для Mac начинаются с 1 января 1904 года. Чтобы избежать проблем совместимости при копировании дат между версиями Excel, можно изменить систему дат в параметрах вычислений Excel, нажав Файл > Параметры > Дополнительно на ПК с Windows или Меню Excel > Настройки > Формулы и списки на Mac.
Для времени 12 AM имеет порядковый номер 0, и он увеличивается на 1/1440 с каждой последующей минутой, так как в сутках 1440 минут. Так, например, порядковый номер для 1 AM равен 0.0416666666666667, что эквивалентно 60/1440.
Как использовать порядковые номера даты и времени в вычислениях Excel
Теперь, когда вы знаете, как серийные номера используются для представления дат и времени в Excel, вы готовы максимально эффективно использовать эти знания в вычислениях.
Пример 1: Нахождение разницы между двумя датами
В этом примере предположим, что вы хотите вычислить количество дней между 1 января и 31 марта 2025 года.
Поскольку порядковые номера дат увеличиваются на единицу каждый день, в ячейке D2 можно просто вычесть конечную дату из начальной даты, но при этом убедиться, что ячейка, содержащая результат, отформатирована как число, а не как дата:
= C2-B2
При расчете разницы между двумя днями результат не включает дату начала.
Пример 2: Добавление дней к дате
Здесь ваша цель — прибавить 60 дней к 1 января 2025 года.
Для этого в ячейке C2 добавьте ячейку, содержащую разницу в днях, к начальной дате, убедившись, что ячейка отформатирована как дата.
=В2+Д2
Пример 3: Взять выходные на свидании
Помимо добавления дней к дате, вы также можете использовать аналогичный принцип для вычитания дней из даты. Например, вы можете захотеть узнать, какая дата была за 35 дней до 1 июля 2025 года.
В ячейке B2 введите:
= C2-D2
и отформатируйте ячейку как дату.
Пример 4: Нахождение разницы между двумя значениями времени в один и тот же день
Первое, что следует отметить при поиске разницы между двумя временами, это то, что они должны быть в пределах одного дня. Это связано с тем, что серийные номера времени отсчитываются от начала дня до конца дня, а затем снова начинаются на следующий день.
Например, если вы попытаетесь найти разницу между 7 вечера одного дня и 7 утра следующего дня, то порядковый номер для 7 вечера больше, чем порядковый номер для 7 утра, и Excel не может отображать отрицательное время. Другими словами, первый аргумент в сумме должен быть более поздним временем, чем второй аргумент.
Чтобы исправить это, вам нужно будет преобразовать все данные в форматы время-дата. Подробнее см. пример 7.
Вместо этого предположим, что вы хотите вычислить разницу во времени между 7:12:45 утра и 10:14:57 утра одного и того же дня.
Для этого в ячейке D2 введите:
= C2-B2
Поскольку ячейка D2 отформатирована как время, она имеет серийный номер. В примере выше это означает, что она приняла «AM» из ячеек B2 и C2.
Итак, чтобы удалить эти буквы, выберите ячейку D2 и нажмите Ctrl+1, чтобы открыть диалоговое окно «Формат ячеек». Затем на вкладке «Число» нажмите «Время» и выберите формат времени, который отображает только часы, минуты и секунды. Когда закончите, нажмите «ОК».
Теперь у вас есть более четкое представление о разнице во времени между ячейками B2 и C2, а соответствующий серийный номер подтверждает правильность расчета.
Пример 5: Добавление часов и минут ко времени
В этом примере вы хотите узнать время после прибавления 12.5 часов к 7:XNUMX.
Вы заметите, что разница во времени в ячейке D2 отформатирована как время. Однако вы знаете, что это будет работать так, как и ожидалось, поскольку серийный номер представляет собой чуть больше половины дня, что и нужно добавить к начальному времени.
Тем не менее, если вы хотите привести это в порядок, вы можете переформатировать ячейку D2 в диалоговом окне Формат ячеек, чтобы отображались только часы, минуты и секунды. Подробнее о том, как это сделать, см. в примере 4.
Теперь в ячейке C2 введите:
=В2+Д2
Порядковый номер для времени окончания больше 1, поскольку добавление 12.5 часов к 7 вечера переносит вас в следующий день. Однако, поскольку ячейка C2 отформатирована для отображения только времени, возвращается только часть после десятичной точки.
Пример 6: сокращение времени на часы и минуты
Как и в примере 4, важно отметить, что вычитание часов и минут из времени будет работать только в том случае, если результат попадает в тот же день, что и время в расчете. В противном случае вы увидите ряд символов решетки, поскольку Excel не может отображать отрицательное время. В этом случае вам нужно будет ввести значения даты и времени вместо значений времени, как в примере 7.
Предположим, вы хотите узнать время, вычтя пять часов, 30 минут и 20 секунд из 9 утра.
Для этого в ячейке B2 введите:
= C2-D2
Пример 7: Выполнение вычислений со значениями даты и времени
В качестве последнего примера предположим, что ваша цель — вычислить разницу во времени между 7 утра 12 декабря 2023 года и 9 утра 31 января 2024 года.
Сначала примените Общий или Числовой формат к ячейке D2. Затем в той же ячейке введите:
= C2-B2
Excel может справиться с этим вычислением, поскольку порядковый номер значения в ячейке C2 больше порядкового номера значения в ячейке B2.
Теперь, чтобы сделать эту информацию более понятной, вы можете разделить результат на дни и часы. Сначала в ячейке E2 введите:
=ЦЕЛОЕ(D2)
округлить десятичное число до ближайшего целого числа. Это даст вам количество полных дней.
Наконец, в ячейке F2 введите:
=СУММ(ОСТАТ(D2,1)*24)
извлечь десятичную дробь и превратить ее в почасовую цифру.
Помимо использования последовательных чисел даты и времени для выполнения этих типов вычислений, Microsoft Excel также предлагает различные функции времени и даты, включая DATEDIF и NETWORKDAYS, которые могут помочь вам получить необходимые вам дату и время.
