Как преобразовать дату в число в Excel (3 примера)
В этом руководстве объясняется, как преобразовать дату в число в трех различных сценариях:
1. Преобразуйте одну дату в число
2. Преобразуйте несколько дат в числа
3. Преобразование даты в количество дней с другой даты
Пример 1: преобразование одной даты в число
Предположим, мы хотим преобразовать дату «10.02.2022» в число в Excel.
Для этого мы можем использовать функцию ДАТАЗНАЧ в Excel:
По умолчанию эта функция вычисляет количество дней между заданной датой и 01.01.1900 .
На следующем снимке экрана показано, как использовать эту функцию на практике:
Это говорит нам о том, что между 10.02.2022 и 01.01.1900 существует разница в 44 602 дня.
Пример 2. Преобразование нескольких дат в числа
Предположим, у нас есть следующий список дат в Excel:
Чтобы преобразовать каждую из этих дат в число, мы можем выделить диапазон ячеек, содержащих даты, затем щелкнуть раскрывающееся меню «Числовой формат » на вкладке « Главная » и выбрать « Число »:
Это автоматически преобразует каждую дату в число, представляющее количество дней между каждой датой и 01.01.1900 :
Пример 3. Преобразование даты в количество дней, прошедших с другой даты
Мы можем использовать следующую формулу для преобразования даты в количество дней, прошедших с другой даты:
Эта конкретная формула вычисляет количество дней между датой в ячейке B2 и датой в ячейке A2 .
На следующем снимке экрана показано, как использовать формулу DATEDIF для расчета количества дней между датами в столбце A и 01.01.2022:
Вот как интерпретировать значения в столбце B:
- Между 01.01.2022 и 01.04.2022 есть 3 дня.
- Между 01.01.2022 и 01.01.2022 8 дней.
- Между 01.01.2022 и 15.01.2022 14 дней.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как в экселе изменить дату на число
Когда вы введите дату или время в ячейку, она отображается в формате даты и времени по умолчанию. Этот формат по умолчанию основан на региональных параметрах даты и времени, заданных на панели управления, и изменяется при их настройке на панели управления. Числа можно отобразить в нескольких других форматах даты и времени, на большинство из которых параметры панели управления не влияют.
В этой статье
Отображение чисел в качестве даты или времени
Вы можете форматирование даты и времени по мере их ввести. Например, если ввести в ячейку 2/2, Excel будет автоматически интерпретирован как дата и отобразит в ячейке 02.фев. Если это не то, что вам нужно (например, если вы хотите, чтобы в ячейке были 2 февраля 2009 г. или 02.02.09), в диалоговом окне Формат ячеек можно выбрать другой формат даты, как покажем в следующей процедуре. Аналогично, если ввести в ячейку 9:30 a или 9:30 p, Excel интерпретирует это как время и отображает 9:30 или 21:30. Вы также можете настроить способ времени в диалоговом окне Формат ячеек.
На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
В списке Категория выберите дата иливремя.
В списке Тип выберите нужный формат даты или времени.
Примечание: Форматы даты и времени, которые начинаются со звездочки (*), отвечают на изменения в региональных параметрах даты и времени, заданных на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
Чтобы отобразить даты и время в формате других языков, выберите нужный язык в поле Языковой стандарт (расположение).
Число в активной ячейке выбранного на этом сайте отображается в поле Образец, чтобы можно было просмотреть выбранные параметры форматирования.
Создание пользовательского формата даты или времени
На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
В поле Категория выберите дата или время ,а затем выберите числовом формате, наиболее близком по стилю к тому, который вы хотите создать. (При создании пользовательских числных форматов проще начать с существующего, чем с нуля.)
В списке Категория выберите пункт (все форматы). В поле Тип вы увидите код формата, совпадающий с форматом даты или времени, выбранным на шаге 3. Встроенный формат даты или времени нельзя изменить или удалить, поэтому не беспокойтесь о переописи.
В поле Тип введите необходимые изменения формата. Вы можете использовать любой из кодов в следующих таблицах:
Дни, месяцы и годы
Для отображения
Используйте код
Месяцев в виде чисел от 1 до 12
Месяцев в виде чисел от 01 до 12
Месяцев в виде «янв», . «дек»
Месяцев в виде «январь», . «декабрь»
Месяцев в виде первой буквы месяца
Дней в виде чисел от 1 до 31
Дней в виде чисел от 01 до 31
Дней в виде «Пн», . «Вс»
Дней в виде «понедельник», . «воскресенье»
Лет в виде чисел от 00 до 99
Лет в виде чисел от 1900 до 9999
Если вы используете «м» сразу после кода «ч» или «чч» или непосредственно перед кодом «сс», Excel отображает минуты вместо месяца.
Часы, минуты и секунды
Для отображения
Используйте код
Часы в качестве 0–23
Часы в качестве 00–23
Минуты в качестве 0–59
Минуты в качестве 00–59
Секунды в качестве 0–59
Секунды в качестве 00–59
Часы с 04:00 до 04:0
Время в 4:36:03 P
Заслон времени в часах; например, 25,02
Заслон времени в минутах; например, 63:46
За считанные секунды
AM и PM Если формат содержит am или PM, часы основаны на 12-часовом формате, где «AM» или «A» указывает время от полуночи до полудня, а «PM» или «P» — время от полудня до полуночи. В противном случае используется 24-часовой цикл. Код «м» или «мм» должен отображаться сразу после кода «ч» или «чч» или непосредственно перед кодом «сс»; в противном Excel отображается месяц, а не минуты.
Создавать пользовательские числовые форматы может быть непросто, если вы этого еще не сделали. Дополнительные сведения о создании пользовательских числных форматов см. в теме Создание и удаление пользовательских числов.
Советы для отображения дат и времени
Чтобы быстро использовать стандартный формат даты или времени, щелкните ячейку с датой или временем и нажмите CTRL+SHIFT+# или CTRL+SHIFT+@.
Если после применения к ячейке формата даты или времени в ней отображаются ####, вероятно, ширины ячейки недостаточно для отображения данных. Чтобы увеличить ширину столбца, дважды щелкните правую границу столбца, содержащего ячейки. Ширина столбца будет автоматически изменена таким образом, чтобы вместить содержимое ячеек. Можно также перетащить правую границу столбца до необходимой ширины.
При попытке отменить формат даты или времени с помощью выбора в списке Категория общего Excel отображает числовом коде. При повторном вводе даты или времени Excel формат даты или времени по умолчанию. Чтобы ввести определенный формат даты или времени, например январь 2010г., можно отформать его как текст, выбрав текст в списке Категория.
Чтобы быстро ввести текущую дату, выйдите из любой пустой ячейки и нажмите CTRL+; (точка с за semicolon) и при необходимости нажмите ввод. Чтобы вставить дату, которая будет обновляться до текущей даты при каждом повторном повторном пересчете или пересчете формулы, введите =СЕГОДНЯ() в пустую ячейку и нажмите ввод.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Исправление превратившихся в даты чисел в Microsoft Excel
Пользователи регулярно вставляют различные исходные данные на листы в Excel, содержащие числа разных форматов и с разным отделением дробной части от целой. Некоторые из них могут быть считаны алгоритмом как даты и, соответственно, формат ячейки поменяется на соответствующий. Вручную изменение формата ячейки в этом случае не дает никакого результата, поэтому нужно искать другие варианты решения, исправляя превратившиеся в даты числа. Как раз об этом и пойдет речь в рамках данного материала.
Описание сути проблемы
Посмотрите на следующий скриншот. Я специально несколько чисел записал с одинаковым разделителем дробной части от целой, чтобы показать, как Эксель автоматически форматирует их в даты. Такое конвертирование происходит только в том случае, если само число может являться датой. Например, это будет 6.1995 – такое число отобразится как июн.95 и вернуть ему прежний формат уже проблематично.
Связано это с тем, что Excel очень сильно зависит от региональных настроек и может воспринимать разные записи чисел как потенциальные даты, автоматически переводя их в такие. Особенно это касается тех случаев, когда пользователь указывает нестандартный разделитель, то есть использует точку вместо запятой.
Вариант 1: Предварительное изменение параметров программы
Первый вариант является подготовительным и подойдет в тех случаях, когда вы только собираетесь вставить числа с возможными неправильными разделителями. Суть заключается в изменении параметров правки и выборе своего разделителя, чтобы конвертирование не происходило автоматически. Для решения данной задачи вам понадобится выполнить следующие шаги:
Если какая-либо книга уже открыта, перейдите на вкладку «Файл», выбрав ее на панели слева.
В главном окне Microsoft Excel на панели слева щелкните по пункту «Параметры».
Выберите раздел с настройками «Дополнительно» и снимите галочку с пункта «Использовать системные разделители».
В качестве разделителя целой и дробной части укажите точку или любой другой знак, который в вашем случае является проблемным. Примените изменения и переходите к импорту чисел, проверяя их на отображение.
Вариант 2: Использование формулы
Далее поговорим о том, как исправить уже вставленные числа, которые подверглись автоматическому конвертированию. Для этого понадобятся вспомогательные ячейки, а также использование простой формулы с несколькими видами вычислений. Вот она:
В этой формуле происходит анализ выбранной ячейки на ее тип. Если он оказывается текстовым, то есть датой, происходит замена точки на запятую с помощью функции ТЕКСТ. Само преобразование обеспечивается двойным умножением на -1, для чего два минуса и стоят в начале формулы. Выглядит это немного непонятно, но работает идеально во всех случаях, что видно на следующем изображении.
Полученные данные можно скопировать и заменить оригиналы, а также формула, как и любые другие, доступна для растягивания. Если значения в ячейке нет, вы получите соответствующее уведомление. В случае, когда число не требует преобразования обратно, оно будет показано в оригинальном виде.
Вариант 3: Создание и активация макроса
В завершение хотелось бы обратиться к среде разработки, встроенной в Microsoft Excel, которая называется Visual Basic. С ее помощью будет создан скрипт, проверяющий данные внутри выбранной ячейки и заменяющий разделитесь на нужный с конвертированием в подходящий формат ячейки. Сам код выглядит следующим образом:
Для создания макроса с его использованием и дальнейшего применения код нужно будет вставить в модуль и сохранить его для данной книги. Если вы еще не знаете, как осуществляется взаимодействие с Visual Basic, следуйте предложенной инструкции:
На верхней панели выберите вкладку «Разработчик» и запустите инструмент «Visual Basic».
Откройте меню «Insert» и выберите из него пункт «Module».
В окно редактирования модуля вставьте указанный выше код и закройте его.
Перед выходом из Visual Basic обязательно сохраните изменения, кликнув по значку с изображением шестеренки.
Остается выделить ячейку для конвертирования, а на той же вкладке «Разработчик» выбрать инструмент управления кодом «Макрос».
В списке макросов найдите только что созданный и дважды нажмите по нему левой кнопкой мыши, чтобы применить.
Возвратитесь к таблице и убедитесь в том, что число в ячейке было успешно исправлено на нужный формат. Повторите то же самое с остальными, а для быстрого применения макроса можете использовать сочетание клавиш Alt + F8.
Таким образом вы узнали, что при помощи трех разных вариантов можно быстро исправить числа, поменявшиеся на даты в Excel, а также ограничить их автоматическое преобразование в дальнейшем. Выбор варианта остается за вами, после чего следует только повторить инструкцию.
Исправление чисел, превратившихся в даты
При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема — дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью — точка), а в Excel у вас российские настройки (где между целой и дробной частью — запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике — если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату — оно будет конвертировано в дату. Всё, что на дату не похоже — останется текстом.
Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:
- В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
- В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года — самая ранняя дата, с которой может работать Excel — 1 января 1900 г.
- А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:
Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel — количество дней от 01.01.1900 до текущей даты:
Лечится вся эта история тремя принципиально разными способами.
Способ 1. Заранее в настройках
Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл — Параметры — Дополнительно (File — Options — Advanced) :
Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .
После этого можно смело импортировать данные — проблем не будет.
Способ 2. Формулой
Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:
=—ЕСЛИ( ЯЧЕЙКА(«формат»;A1)=»G» ; ПОДСТАВИТЬ(A1;».»;»,») ; ТЕКСТ(A1;»М,ГГГГ») )
В английской версии это будет:
=—IF (CELL («format «;A1)=»G»; SUBSTITUTE (A1;».»;»,»); TEXT (A1;»M ,YYYY «))
Логика здесь простая:
- Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата «G» для текста/чисел или «D3» для дат.
- Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
- Если в исходной ячейке дата, то выводим её в формате «номер месяца — запятая — номер года» с помощью функции ТЕКСТ (TEXT) .
- Чтобы преобразовать получившееся текстовое значение в полноценное число — выполняем бессмысленную математическую операцию — добавляем два знака минус перед формулой, имитируя двойное умножение на -1.
Способ 3. Макросом
Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставляем в нашу книгу новый пустой модуль через меню Insert — Module и копируем туда такой код:
Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer — Macros) . Все испорченные числа будут немедленно исправлены.