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

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

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

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

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

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

• Что такое формат даты
• Формат даты по умолчанию
• Как изменить формат даты
• Как изменить язык даты
• Создание собственного формата отображения даты
• Дата в числовом формате
• Формат времени
• Время в числовом формате
• Создайте собственный формат времени
• Формат Дата — Время
• Потому что не работает? Проблемы и их решение.

Формат даты в Excel

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

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

Дата в Excel

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

В этой системе:

• 2 — 2 января 1900 г
• 44197 — 1 января 2021 г. (потому что прошло 44 197 дней после 1 января 1900 г.)

Время в 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

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

Например: 44197,5 — 1 января 2021 г., 12:00.

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

Короткий и длинный форматы даты, которые по умолчанию установлены как основные, извлекаются из региональных настроек Windows. Они отмечены звездочкой (*) в диалоговом окне:

 

 

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

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

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

 

 

Если вас не устраивают параметры, предлагаемые на этой вкладке, нажмите кнопку «Дополнительные параметры» в правом нижнем углу диалогового окна. Откроется новое окно «Настройки…», в котором вы переключитесь на вкладку «Дата» и в соответствующем поле введите собственный короткий или длинный формат.

 

 

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

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

Чтобы быстро изменить один из них, сделайте следующее:

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

 

 

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

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

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

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

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

 

 

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

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

Как сменить язык даты.

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

Самый простой способ сделать это заключается в следующем:

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

 

 

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

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

1. Выберите нужные ячейки на листе.
2. Нажмите Ctrl + 1.
3. На вкладке «Число» выберите «Все форматы» из списка и введите нужный формат даты в поле «Тип». Это показано на следующем снимке экрана.
4. Нажмите OK, чтобы сохранить изменения.

 

 

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

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

Код	Описание	Пример
метро	Номер месяца без начального нуля	а
миллиметр	Номер месяца с ведущим нулем	01
м-м-м	Название месяца, сокращенная форма	Ян
м-м-м	Название месяца, полная форма	Январь
мммммм	Первая буква месяца	М (обозначает март и май)
г	Номер дня без ведущего нуля	а
дд	Номер дня с ведущим нулем	01
ддд	День недели, сокращенная форма	Пн
дддд	День недели, полная форма	понедельник
грамм	Год (последние 2 цифры)	05
аааа	Год (4 цифры)	2020

Вы также можете использовать дополнительные коды, которые должны быть заключены в квадратные скобки .

Код	Объяснение
x-sysdate	Системный длинный формат. Месяц в родительном падеже.
х системное время	Системное время.
x-genlower	Родительный падеж используется в нижнем регистре для любых полных названий месяцев (только для русского языка). Рекомендуется использовать вместе с кодом языка [ru-RU-x-genlower].
x-genupper	Родительный падеж пишется с большой буквы для любых полных названий месяцев (только на русском языке). Например, [ru-RU-x-genupper].
x-номниже	Для любых полных названий месяцев используется строчный именительный падеж (только русский): [ru-RU-x-nomlower].

Вот как это может выглядеть на примерах:

 

 

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

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

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

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

• [-419$] — Россия
• [$-409] — Английский (США)
• [-422$] — Украина
• [$-423] — Беларусь
• [-407$] — Германия

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

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

 

 

Как показать число вместо даты.

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

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

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

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

 

 

Вы также можете выбрать формат «Общий» на ленте под разделом «Число». Дата будет немедленно заменена соответствующей ей цифрой.

2. Функции ДАТАЗНАЧ и ВРЕМЗНАЧ

Вы также можете использовать функцию DATEVALUE() для преобразования даты в соответствующее число 

=ДАТАЗНАЧ(«20.01.2021»)

Используйте функцию TIMEVALUE(), чтобы получить десятичное число, представляющее время 

=ВРЕМЗНАЧ(«16:30»)

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

= ЗНАЧЕНИЕ ДАТЫ («20.01.2021») И ЗНАЧЕНИЕ ВРЕМЕНИ («16:30»)

Будет получено число, записанное в текстовом виде, соответствующее дате-времени.

 

 

А если применить операцию сложения –

= ЗНАЧЕНИЕ ДАТЫ («20.01.2021») + ЗНАЧЕНИЕ ВРЕМЕНИ («16:30»)

Тогда мы получаем число, которое можно отформатировать как дату-время и с которым можно производить математические операции (находить разницу дат и т.д.)

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

Если написать, скажем, 31.12.1812, то это будет текстовое значение, а не дата. Это означает, что вы не сможете выполнять над ним обычные арифметические операции. Чтобы убедиться в этом, вы можете ввести формулу =ЗНАЧДАТА(«31.12.1812») в какую-нибудь ячейку, и вы получите ожидаемый результат: ошибку #ЗНАЧ!.

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

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

Например:

• 00:00:00 сохраняется как 0.0
• 23:59:59 сохраняется как 0,99999
• 06:00 — 0.25
• 12:00 — 0,5

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

Формат времени по умолчанию.

При изменении формата времени в диалоговом окне «Формат ячеек» вы могли заметить, что один из элементов начинается со звездочки (*). Это формат времени по умолчанию в вашем Excel. Как и дата, она определяется региональными настройками Windows.

 

 

Чтобы быстро применить формат времени по умолчанию к выбранной ячейке или диапазону ячеек, щелкните стрелку раскрывающегося списка в группе «Число» на вкладке «Главная» и выберите «Время.

 

 

Измените формат времени по умолчанию, перейдя в Панель управления и выбрав «Региональные настройки» > «Изменить формат даты, времени и чисел». Мы подробно описали этот процесс ранее, когда рассматривали настройку даты по умолчанию.

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

Десятичное представление времени.

Быстрый способ выбрать десятичное число, представляющее определенное время, — использовать диалоговое окно «Формат ячеек”.

Просто выберите ячейку, содержащую время, и нажмите Ctrl + 1, чтобы открыть окно настроек. На вкладке «Число» выберите «Общие» в «Категории», и вы увидите десятичное число в поле «Шаблон».

 

 

Теперь вы можете записать этот номер и нажать «Отмена», чтобы закрыть окно. Либо нажмите OK и замените время соответствующим десятичным числом в ячейке. 

На самом деле, это самый быстрый, простой и не требующий формул способ преобразования времени в десятичное число. 

Как применить или изменить формат времени.

Microsoft Excel достаточно умен, чтобы распознавать время при вводе и соответствующим образом форматировать ячейку. Например, если вы наберете 10:30 или 18:40, программа интерпретирует и отобразит это как время на основе формата времени по умолчанию.

Если вы хотите отформатировать некоторые числа как часы или применить другой формат времени к существующим значениям, вы можете сделать это с помощью диалогового окна «Формат ячеек», как описано ниже.

1. В электронной таблице Excel выберите ячейки, к которым вы хотите применить или изменить формат времени.

2. Откройте диалоговое окно «Формат ячеек», нажав Ctrl + 1 или щелкнув значок «Диалоговое окно» рядом с полем «Число» на вкладке «Главная”.

3. На вкладке «Число» выберите из списка «Время» и укажите соответствующий шаблон в поле «Тип» .

4. Нажмите OK, чтобы применить выбор.

 

 

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

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

 

 

Созданный вами пользовательский формат времени останется в списке «Тип» в следующий раз, когда он вам понадобится.

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

При этом вы можете использовать следующие коды.

Код	Описание	Отображается как
час	Часы без начального нуля	0:35:00
х.Х	Часы с ведущим нулем	03:35:00
метро	Минуты без начального нуля	0:0:59
миллиметр	Минуты с ведущим нулем	00:00:59
с	Секунды без начального нуля	00:00:9
х.Х	Секунды с ведущим нулем	00:00:09

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

 

 

Пользовательские форматы для отрицательных значений времени

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

Если вы хотите ввести отрицательное время, у вас есть следующие варианты:

• Показывать пустую ячейку для отрицательного времени. Для этого введите точку с запятой в конце формата времени, например [ч]:мм;
• Показывает сообщение об ошибке. Введите точку с запятой в конце формата времени, а затем заключите сообщение в кавычки, например 

[Хм; «негативное время»

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

Если вы хотите отображать отрицательные значения точно так же, как отрицательные значения, например -11:15, проще всего изменить систему дат Excel на систему 1904. Для этого нажмите «Файл» > «Параметры» > «Дополнительно», прокрутите вниз до раздела «Вычисления» и установите флажок Использовать систему дат 1904 года.

Формат «Дата – Время».

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

На скриншоте ниже вы увидите несколько вариантов того, как будут выглядеть ваши значения:

 

 

Ничего сложного: сначала описываем дату, потом время.

Формат даты Excel не работает – как исправить?

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

Но если у вас по-прежнему возникают проблемы с отображением дня, месяца и года, ознакомьтесь с приведенными ниже советами по устранению неполадок.

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

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

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

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

Во всех современных версиях Excel 2013, 2010 и 2007 знак решетки (#####) также отображается, когда ячейка, отформатированная как дата или время, содержит отрицательное значение. Обычно это результат, возвращаемый некоторой формулой. Но это также может произойти, когда вы вводите отрицательное значение в ячейку, а затем отображаете эту ячейку как дату.

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

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

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

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

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

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

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

=ТЕКСТ(АБС(B1-C1),»-ДД ММ ГГГГ»)

Получаем результат «-01 01 1900».

Вы можете использовать любой другой формат даты в формуле ТЕКСТ.

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

Даты импортированы в Excel как текст

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

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