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

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

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

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

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

Более глубокое понимание форматов времени может сэкономить вам много времени. И как раз в этом цель нашего подробного руководства.

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

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

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

Дата в Excel

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

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

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

Время в Excel

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

Например:

• С 0,25 до 06:00
• 0.5 — 12 часов.
• 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. Щелкните ОК, чтобы сохранить изменения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= ЗНАЧЕНИЕ ДАННЫХ («20.01.2021»)

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

= ВРЕМЕННОЕ ЗНАЧЕНИЕ («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 предоставляет несколько форматов времени, вы можете создать тот, который лучше всего подходит для вашего бизнеса. Для этого откройте привычное окно настроек, выберите «Все форматы» и введите подходящее время в поле «Тип» .

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

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

Для этого можно использовать следующие коды.

Например, при расчете расписания сумма может превышать 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-янв-1904.

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

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

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

= ТЕКСТ (ABS (B1-C1); «- ДД ММ ГГГГ»)

Результат будет «-01 01 1900».

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

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

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

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

Решение. Вы можете преобразовать «текстовые даты» в их правильную форму с помощью функции ДАТАЗНАЧ или функции «Текст по столбцу». 

Мы рассмотрели возможные способы представления даты и времени в Excel. Спасибо за прочтение!