Форматирование даты в Excel

Обновлено: 21.11.2024

Первая часть нашего руководства посвящена форматированию дат в Excel и объясняет, как установить форматы даты и времени по умолчанию, как изменить формат даты в Excel, как создать собственное форматирование даты и преобразовать даты в другая локаль.

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

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

Формат даты Excel

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

Excel хранит даты в виде последовательных чисел, и только форматирование ячейки приводит к тому, что число отображается как дата, время или дата и время.

Даты в Excel

Все даты хранятся в виде целых чисел, представляющих количество дней с 1 января 1900 года, которое хранится как число 1, до 31 декабря 9999 года, которое сохраняется как 2958465.

  • 2 – 2 января 1900 года.
  • 3 – 3 января 1900 года.
  • 42005 – 1 января 2015 года (поскольку прошло 42 005 дней после 1 января 1900 года).

Время в Excel

Время хранится в Excel в виде десятичных дробей от 0,0 до 0,99999, которые представляют собой долю дня, где 0,0 – 00:00:00, а 0,99999 – 23:59:59.

  • 0,25 — это 06:00.
  • 0,5 — это 12:00.
  • 0,541655093 — 12:59:59

Даты и время в Excel

Excel хранит дату и время в виде десятичных чисел, состоящих из целого числа, представляющего дату, и десятичной части, представляющей время.

  • 1.25 – 1 января 1900 года, 6:00.
  • 42005.5 — 1 января 2015 г., 12:00.

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

Если вы хотите узнать, какой серийный номер соответствует определенной дате или времени, отображаемому в ячейке, вы можете сделать это двумя способами.

1. Диалоговое окно «Формат ячеек»

Выделите ячейку с датой в Excel, нажмите Ctrl+1, чтобы открыть окно Формат ячеек, и перейдите на вкладку Общие.

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

2. Функции Excel DATEVALUE и TIMEVALUE

Используйте функцию ЗНАЧДАТА() для преобразования даты Excel в порядковый номер, например =ЗНАЧДАТА("1/1/2015") .

Используйте функцию ВРЕМЗНАЧ(), чтобы получить десятичное число, представляющее время, например =ВРЕМЗНАЧ("6:30") .

Чтобы узнать и дату, и время, объедините эти две функции следующим образом:

=ДАТАЗНАЧ("1/1/2015") И ВРЕМЯЗНАЧ("6:00")

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

Если вы имеете дело со значениями даты и времени и хотите преобразовать время в десятичное число, ознакомьтесь с формулами, описанными в этом руководстве: Как преобразовать время в десятичное число в Excel.

Формат даты по умолчанию в Excel

При работе с датами в Excel краткий и длинный форматы даты извлекаются из региональных настроек Windows. Эти форматы по умолчанию отмечены звездочкой (*) в диалоговом окне Формат ячейки:

Форматы даты и времени по умолчанию в поле Формат ячейки изменяются, как только вы изменяете настройки даты и времени в панели управления, что приводит нас прямо к следующему разделу.

Как изменить форматы даты и времени по умолчанию в Excel

Если вы хотите установить другой формат даты и/или времени по умолчанию на своем компьютере, например изменить формат даты для США на стиль для Великобритании, перейдите на панель управления и нажмите "Регион и язык". Если панель управления открывается в представлении по категориям, нажмите Часы, язык и регион > Регион и язык > Изменить формат даты, времени или числа.

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

Совет. Если вы не уверены, что означают разные коды (например, mmm, ddd, yyy), нажмите ссылку "Что означает обозначение" в разделе Форматы даты и времени. или ознакомьтесь с пользовательскими форматами даты Excel в этом руководстве.

Если вас не устраивает какой-либо формат времени и даты, доступный на вкладке Форматы, нажмите кнопку Дополнительные настройки в нижней правой части окна Регион и язык диалоговое окно. Откроется диалоговое окно Настройка, в котором вы переключитесь на вкладку Дата и введете пользовательский короткий и/или длинный формат даты в соответствующем поле.

Как быстро применить формат даты и времени по умолчанию в Excel

Microsoft Excel имеет два формата даты и времени по умолчанию — краткий и длинный, как описано в формате даты Excel по умолчанию.

Чтобы быстро изменить формат даты в Excel на формат по умолчанию, выполните следующие действия:

  • Выберите даты, которые хотите отформатировать.
  • На вкладке Главная в группе Число щелкните маленькую стрелку рядом с полем Формат числа и выберите нужный формат — краткая дата, длинная дата или время.

Если вам нужны дополнительные параметры форматирования даты, либо выберите Дополнительные числовые форматы в раскрывающемся списке, либо нажмите кнопку Диалоговое окно рядом с элементом Число. Откроется знакомое диалоговое окно Формат ячеек, где вы сможете изменить формат даты.

Как изменить формат даты в Excel

В Microsoft Excel даты могут отображаться различными способами. Когда дело доходит до изменения формата даты данной ячейки или диапазона ячеек, проще всего открыть диалоговое окно «Формат ячеек» и выбрать один из предопределенных форматов.

  1. Выберите даты, формат которых вы хотите изменить, или пустые ячейки, в которые вы хотите вставить даты.
  2. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек. Кроме того, вы можете щелкнуть выбранные ячейки правой кнопкой мыши и выбрать Формат ячеек… в контекстном меню.
  3. В окне Формат ячеек перейдите на вкладку Число и выберите Дата в списке Категория.
  4. В разделе Тип выберите нужный формат даты. После этого в поле Образец отобразится предварительный просмотр формата с первой датой в выбранных данных.
  5. Если вас устраивает предварительный просмотр, нажмите кнопку ОК, чтобы сохранить изменение формата и закрыть окно.

Если формат даты на вашем листе Excel не меняется, скорее всего, ваши даты отформатированы как текст, и вам нужно сначала преобразовать их в формат даты.

Как преобразовать формат даты в другой язык

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

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

  • Выберите столбец дат, который вы хотите преобразовать в другой язык.
  • Нажмите Ctrl+1, чтобы открыть Формат ячеек
    • .
  • Выберите нужный язык в разделе «Язык (местоположение)» и нажмите «ОК», чтобы сохранить изменения.

Если вы хотите, чтобы даты отображались на другом языке, вам нужно будет создать собственный формат даты с кодом локали.

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

Если ни один из предопределенных форматов даты Excel вам не подходит, вы можете создать свой собственный.

  1. На листе Excel выберите ячейки, которые нужно отформатировать.
  2. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
  3. На вкладке Число выберите «Пользовательский» в списке Категория и введите нужный формат даты в поле «Тип».
  4. Нажмите "ОК", чтобы сохранить изменения.

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

При настройке пользовательского формата даты в Excel можно использовать следующие коды.

< /tr>

При настройке пользовательского формата времени в Excel можно использовать следующие коды.

Код Описание Пример (1 января 2005 г.)
m Номер месяца без начального нуля 1< /td>
mm Номер месяца с начальным нулем 01
mmm Название месяца, короткая форма Янв
мммм Название месяца, полная форма Январь
ммммм Месяц как первая буква J (обозначает январь, июнь и июль)< /td>
d Номер дня без ведущего нуля 1
dd Номер дня с начальным нулем 01
ddd День недели, краткая форма Пн
dddd День недели, полная форма Понедельник
yy Год (последние 2 цифры) 05
yyyy Год (4 цифры) 2005
< /tr>
Код Описание Отображает as
h Часы без начального нуля 0-23
hh Часы с ведущим нулем 00-23
m Минуты без начального нуля 0-59
мм Минуты с начальным нулем 00-59
s Секунды без нуля в начале 0-59
ss Секунды с нулем в начале 00-59
AM/PM Периоды дня
(если опущено, используется 24-часовой формат времени)		 AM или PM

Чтобы настроить формат даты и времени, включите единицы измерения даты и времени в код формата, например м/д/гггг ч:мм AM/PM. Если вы используете "m" сразу после "hh" или "h" или непосредственно перед "ss" или "s", Excel отобразит минут, а не месяца.

При создании пользовательского формата даты в Excel можно использовать запятую (,), тире (-), косую черту (/), двоеточие (:) и другие символы.

Например, одна и та же дата и время, например 13 января 2015 г., 13:03, могут отображаться по-разному:

Формат Отображается как
дд-ммм-гг 13-янв-15
мм/дд/гггг 13/01/ 2015
м/дд/гг 13/01/15
дддд, м/ д/гг ч:мм AM/PM Вторник, 13/01/15 13:03
ддд, мммм дд, гггг чч:мм :ss Вт, 13 января 2015 г., 13:03:00

Как создать собственный формат даты Excel для другой локали

Если вы хотите отображать даты на другом языке, вам необходимо создать собственный формат и добавить к дате префикс соответствующего кода локали. Код локали должен быть заключен в [квадратные скобки] со знаком доллара ($) и дефисом (-). Вот несколько примеров:

  • [$-409] – английский, штаты без названия
  • [$-1009] – английский, Канада
  • [$-407] – немецкий, Германия
  • [$-807] — немецкий, Швейцария.
  • [$-804] — Бенгальский, Индия
  • [$-804] — Китайский, Китай
  • [$-404] – Китай, Тайвань

Вы можете найти полный список кодов локалей в этом блоге.

Например, вот как можно настроить собственный формат даты Excel для китайского языка в формате год-месяц-день (день недели) и время:

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

Формат даты Excel не работает — исправления и решения

Обычно Microsoft Excel очень хорошо понимает даты, и вы вряд ли столкнетесь с какими-либо препятствиями при работе с ними. Если у вас возникла проблема с форматом даты в Excel, ознакомьтесь со следующими советами по устранению неполадок.

Ячейка недостаточно широка, чтобы вместить всю дату

Решение. Дважды щелкните правую границу столбца, чтобы изменить его размер в соответствии с датами. Кроме того, вы можете перетащить правую границу, чтобы установить нужную ширину столбца.

Отрицательные числа форматируются как даты

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

Решение 1. Переключитесь на систему дат 1904 года.

Перейдите в раздел Файл > Параметры > Дополнительно, прокрутите вниз до раздела При расчете этой книги и выберите установите флажок Использовать систему дат 1904 и нажмите ОК.

В этой системе 0 соответствует 1 января 1904 года; 1 — 2 января 1904 г.; а -1 отображается как отрицательная дата -2 января 1904 года.

Конечно, такое представление очень необычно и требует времени, чтобы привыкнуть к нему, но это правильный путь, если вы хотите выполнять расчеты с более ранними датами.

Решение 2. Используйте функцию ТЕКСТ Excel.

Еще один возможный способ отображения отрицательных чисел в виде отрицательных дат в Excel — использование функции ТЕКСТ. Например, если вы вычитаете C1 из B1, а значение в C1 больше, чем в B1, вы можете использовать следующую формулу для вывода результата в формате даты:

=ТЕКСТ(ABS(B1-C1),"-d-ммм-гггг")

Возможно, вы захотите изменить выравнивание ячеек по правому краю, и, естественно, вы можете использовать любые другие настраиваемые форматы даты в формуле ТЕКСТ.

Примечание. В отличие от предыдущего решения, функция ТЕКСТ возвращает текстовое значение, поэтому вы не сможете использовать результат в других вычислениях.

Даты импортируются в Excel в виде текстовых значений

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

Решение. Вы можете преобразовать «текстовые даты» в формат даты, используя функцию Excel DATEVALUE или функцию Text to Columns. Полную информацию см. в следующей статье: Как преобразовать текст в дату в Excel.

Вот как вы форматируете даты в Excel. В следующей части нашего руководства мы обсудим различные способы вставки дат и времени в листы Excel. Спасибо за внимание и увидимся на следующей неделе!

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

В этой статье

Отображать числа в виде даты или времени

Дату и время можно форматировать по мере ввода. Например, если вы введете 2/2 в ячейку, Excel автоматически интерпретирует это как дату и отобразит в ячейке 2 февраля. Если это не то, что вам нужно — например, если вы предпочитаете отображать в ячейке 2 февраля 2009 г. или 2 февраля 2009 г., — вы можете выбрать другой формат даты в диалоговом окне «Формат ячеек», как описано ниже. процедура. Точно так же, если вы введете в ячейке 9:30 a или 9:30 p, Excel интерпретирует это как время и отобразит 9:30 или 21:30. Опять же, вы можете настроить способ отображения времени в диалоговом окне «Формат ячеек».

На вкладке "Главная" в группе "Число" нажмите кнопку запуска диалогового окна рядом с номером.

Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно "Формат ячеек".

В списке «Категория» нажмите «Дата» или «Время».

В списке Тип выберите нужный формат даты или времени.

Примечание. Форматы даты и времени, начинающиеся со звездочки (*), соответствуют изменениям в региональных настройках даты и времени, указанных в панели управления. На форматы без звездочки настройки панели управления не влияют.

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

Число в активной ячейке выбора на листе отображается в поле «Образец», чтобы вы могли предварительно просмотреть выбранные параметры форматирования чисел.

Создать собственный формат даты или времени

На вкладке "Главная" нажмите кнопку вызова диалогового окна рядом с номером.

Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно "Формат ячеек".

В поле «Категория» нажмите «Дата» или «Время», а затем выберите числовой формат, наиболее близкий по стилю к тому, который вы хотите создать. (При создании пользовательских числовых форматов проще начать с существующего формата, чем с нуля.)

В поле "Категория" нажмите "Пользовательский". В поле Тип вы должны увидеть код формата, соответствующий формату даты или времени, который вы выбрали на шаге 3. Встроенный формат даты или времени нельзя изменить или удалить, поэтому не беспокойтесь о его перезаписи.< /p>

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

Дни, месяцы и годы

Используйте этот код

Месяцы с января по декабрь

Месяцы с января по декабрь

Месяцы как первая буква месяца

Дни с воскресенья по субботу

Годы как 1900–9999

Если вы используете "m" сразу после кода "h" или "hh" или непосредственно перед кодом "ss", Excel отображает минуты вместо месяца.

Часы, минуты и секунды

Используйте этот код

Минуты от 00 до 59

Секунды в формате 00–59

Время как 4:36:03 P

Прошедшее время в часах; например, 25.02

Прошедшее время в минутах; например, 63:46

Прошедшее время в секундах

Доли секунды

AM и PM Если формат содержит AM или PM, час основан на 12-часовом формате, где "AM" или "A" указывает время с полуночи до полудня, а "PM" или "P" указывает время. с полудня до полуночи. В противном случае час основан на 24-часовом формате. Код "m" или "mm" должен стоять сразу после кода "h" или "hh" или непосредственно перед кодом "ss"; в противном случае Excel отображает месяц вместо минут.

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

Советы по отображению даты или времени

При попытке отменить формат даты или времени, выбрав «Общие» в списке «Категория», Excel отображает числовой код. Когда вы снова вводите дату или время, Excel отображает формат даты или времени по умолчанию. Чтобы ввести конкретный формат даты или времени, например январь 2010 г., вы можете отформатировать его как текст, выбрав Текст в списке категорий.

Чтобы быстро ввести текущую дату на листе, выберите любую пустую ячейку и нажмите CTRL+; (точка с запятой), а затем при необходимости нажмите клавишу ВВОД. Чтобы вставить дату, которая будет обновляться до текущей даты каждый раз, когда вы снова открываете лист или пересчитываете формулу, введите =СЕГОДНЯ() в пустой ячейке и нажмите клавишу ВВОД.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Когда вы вводите дату в Microsoft Excel, программа отформатирует ее в соответствии с настройками даты по умолчанию. Например, если вы хотите ввести дату 6 февраля 2020 г., дата может отображаться как 6 февраля, 6 февраля 2020 г., 6 февраля или 06 февраля 2020 г., все зависит от ваших настроек. Вы можете обнаружить, что если вы измените форматирование ячейки на «Стандартное», ваша дата будет сохранена в виде целых чисел. Например, 6 февраля 2020 г. станет 43865 , поскольку форматирование даты в Excel основано на 1 января 1900 г. Каждый из этих параметров представляет собой способ форматирования дат в Excel. Чтобы упростить организацию данных в Excel, узнайте, как изменить формат даты в Excel .

Выбор формата даты из списка

Форматировать даты в Excel проще всего с помощью списка форматов дат.Большинство форматов даты, которые вы можете использовать, можно найти в этом меню.

  1. Выберите ячейки, которые хотите отформатировать.
  2. Нажмите Ctrl+1 или Command+1.
  3. Выберите вкладку "Числа".
  4. В категориях выберите «Дата».
  5. В меню "Тип" выберите нужный формат даты.

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

Чтобы настроить формат даты, выполните шаги по выбору параметра из списка форматов даты. Выбрав наиболее близкий формат даты к желаемому, вы можете настроить его.

  1. В меню "Категория" выберите "Пользовательская".
  2. Появится тип, который вы выбрали ранее. Вносимые вами изменения будут применяться только к вашим индивидуальным настройкам, а не к настройкам по умолчанию.
  3. В поле "Тип" введите правильный код, чтобы изменить дату.

Преобразование форматов даты в другие локали

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

  1. Выберите нужные ячейки.
  2. Нажмите Ctrl+1 или Command+1.
  3. В меню "Числа" выберите "Дата".
  4. Под меню "Тип" есть раскрывающееся меню "Язык".
  5. Выберите правильный «Язык».

Вы также можете настроить региональные параметры:

Советы по отображению дат в Excel

Если у вас есть правильный формат даты, есть дополнительные советы, которые помогут вам понять, как организовать данные в Excel для ваших наборов данных.

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

Подготовка данных в Excel

Форматирование данных является частью более крупного процесса, известного как "подготовка данных", или всех шагов, необходимых для очистки, стандартизации и подготовки данных для аналитического использования.

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

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

Даты и время в Excel могут отображаться различными способами. Чтобы применить формат даты или времени, выполните следующие действия.

<р>1. Выберите ячейку A1.

<р>2. Щелкните правой кнопкой мыши и выберите "Формат ячеек".

<р>3. В списке Категория выберите Дата и выберите формат даты.

Примечание. Чтобы применить формат времени, в списке "Категория" выберите "Время".

<р>5. Даты хранятся в Excel в виде чисел и подсчитывают количество дней, прошедших с 0 января 1900 года. Время внутренне обрабатывается как числа от 0 до 1. Чтобы это было ясно, измените числовой формат ячеек A1, B1 и C1 на Общий.< /p> Ширина

Примечание: очевидно, что 42 544 дня после 0 января 1900 года совпадают с 23 июня 2016 года. 6:00 представлены как 0,25 (четверть дня).

<р>6. Вы можете ввести время как 6:00, но Excel отобразит это время как 6:00:00 в строке формул. AM используется для времени ночью и утром. PM используется для дневного и вечернего времени.

<р>7. Измените числовой формат ячейки C1 только на дату.

Примечание: ячейка C1 по-прежнему содержит число 42544,25. Мы изменили только внешний вид номера, но не сам номер.

Читайте также: