Изменение формата даты в excel

Изменение системы дат, формата даты и двузначного представления года

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

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

Excel хранит даты в виде последовательных чисел, которые называются последовательными значениями. Например, в Excel для Windows 1 января 1900 является порядковым номером 1, а 1 января 2008 — порядковым номером 39448, так как это 39 448 дня после 1 января 1900 г.

Excel хранит значения времени как десятичные дроби, так как время рассматривается как часть суток. Десятичное число — это значение в диапазоне от 0 (нуля) до 0,99999999, представляющее собой время от 0:00:00 (12:00:00 утра) до 23:59:59 (11:59:59 вечера).

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

В Excel для Mac и Excel для Windows поддерживаются системы дат 1900 и 1904. Система дат по умолчанию для Excel для Windows — 1900; а система дат по умолчанию для Excel для Mac — 1904.

Первоначально приложение Excel для Windows основано на системе дат 1900, поскольку оно позволило улучшить совместимость с другими программами для работы с электронными таблицами, разработанными для операционной системы MS-DOS и Microsoft Windows, и поэтому она стала стандартной системой дат. Первоначально приложение Excel для Mac основано на системе дат 1904, поскольку оно позволило улучшить совместимость с ранними компьютерами Macintosh, которые не поддерживали даты до 2 января 1904 г., поэтому она стала системой дат по умолчанию.

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

1 января 1900
г. (последовательное значение 1)

31 декабря 9999
г. (последовательное значение 2958465)

2 января 1904
г. (последовательное значение 1)

31 декабря 9999
г. (последовательное значение 2957003)

Поскольку в двух системах дат используются разные начальные дни, одинаковая дата представлена различными порядковыми номерами в каждой системе дат. Например, 5 июля 2007 могут иметь два различных последовательных значения в зависимости от используемой системы дат.

Последовательное значение 5 июля 2007 г.

Разница между двумя системами дат составляет 1 462 дней; Это значит, что порядковый номер даты в системе 1900 даты всегда равен 1 462 суток, чем последовательное значение той же даты в системе дат 1904. И наоборот, порядковый номер даты в системе дат 1904 всегда составляет 1 462 дней меньше, чем последовательное значение той же даты в системе дат 1900. 1 462 дней равно 4 годам и одному дню (включая один високосный день).

Важно: Чтобы значения года интерпретировались правильно, введите четыре цифры (например, 2001, а не 01). Вводя четырехзначные года, Excel не будет интерпретировать этот столети.

Если вы ввели дату, состоящий из двух цифр года, в текстовой ячейке или текстовом аргументе в функции, например «= год» («1/1/31»), Excel интерпретирует год следующим образом:

00 – 29 интерпретируется как годы с 2000 по 2029. Например, при вводе даты 5/28/19Excel считает, что дата может быть 28 мая 2019 г.

от 30 до 99 интерпретируется как годы с 1930 по 1999. Например, при вводе даты 5/28/98Excel считает, что дата может быть 28 мая 1998 г.

В Microsoft Windows можно изменить способ интерпретации двух цифр года для всех установленных программ Windows.

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

В разделе часы, язык и регионщелкните изменить форматы даты, времени и чисел .

Щелкните значок Язык и региональные стандарты.

В диалоговом окне регион нажмите кнопку Дополнительные параметры.

Откройте вкладку Дата .

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

По мере изменения верхнего лимита год автоматически меняется на нижний предел.

Нажмите кнопку ОК.

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

В разделе часы, язык и регионщелкните изменить форматы даты, времени или чисел.

В диалоговом окне регион нажмите кнопку Дополнительные параметры.

Откройте вкладку Дата .

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

По мере изменения верхнего лимита год автоматически меняется на нижний предел.

Нажмите кнопку ОК.

Нажмите кнопку Пуск и выберите пункт Панель управления.

Выберите пункт язык и региональные стандарты.

В диалоговом окне регион нажмите кнопку Дополнительные параметры.

Откройте вкладку Дата .

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

По мере изменения верхнего лимита год автоматически меняется на нижний предел.

Нажмите кнопку ОК.

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

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

В разделе часы, язык и регионщелкните изменить форматы даты, времени и чисел .

Щелкните значок Язык и региональные стандарты.

В диалоговом окне регион нажмите кнопку Дополнительные параметры.

Откройте вкладку Дата .

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

Нажмите кнопку ОК.

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

В разделе часы, язык и регионщелкните изменить форматы даты, времени или чисел.

В диалоговом окне регион нажмите кнопку Дополнительные параметры.

Откройте вкладку Дата .

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

Нажмите кнопку ОК.

Нажмите кнопку Пуск и выберите пункт Панель управления.

Выберите пункт язык и региональные стандарты.

В диалоговом окне регион нажмите кнопку Дополнительные параметры.

Откройте вкладку Дата .

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

Нажмите кнопку ОК.

Система дат автоматически меняется при открытии документа, созданного на другой платформе. Например, если вы работаете в Excel и открыли документ, созданный в Excel для Mac, флажок » система дат 1904 » установлен автоматически.

Вы можете изменить систему дат, выполнив указанные ниже действия.

Выберите Файл > Параметры > Дополнительно.

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

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

Например, если вы копируете дату 5 июля 2007 из книги, в которой используется система дат 1900, а затем вставляете ее в книгу, в которой используется система дат 1904, дата отображается как 5 июля 2011 г., что составляет 1 462 дней позже. Кроме того, если вы копируете дату 5 июля 2007 из книги, в которой используется система дат 1904, а затем вставляете ее в книгу, в которой используется система дат 1900, дата будет состоять из 4 июля 2003 г., что составляет 1 462 дней раньше. Общие сведения можно найти в разделе системы дат в Excel.

Исправление ошибки при копировании и вставке

В пустой ячейке введите значение 1462.

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

Выделите все ячейки, содержащие неверные даты.

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

На вкладке Главная в группе буфер обмена нажмите кнопку Вставитьи выберите команду Специальная Вставка.

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

Чтобы установить дату как четыре года и один день позже, нажмите кнопку Добавить.

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

Исправление проблемы с внешней ссылкой

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

Чтобы установить дату как четыре года и один день позже, добавьте в нее 1 462. Пример.

Чтобы установить дату в виде четырех лет и более ранней версии, вычтите 1 462 из нее. Пример.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Ввод и форматирование дат и времени в Excel

Весь курс: урок 1 | урок 2 | урок 3 | урок 4 | урок 5

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

Ввод дат и времени в Excel

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

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

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

Это не все возможные варианты, которые поддерживает Excel. Но даже этих вариантов Вам будет предостаточно.

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

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

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

Получить доступ ко всем возможным форматам Вы можете на вкладке Число диалогового окна Формат ячеек. В категории Дата приведены встроенные форматы дат:

А в категории Время – встроенные форматы времени:

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

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

1. Выделите ячейку, к которой необходимо применить требуемый числовой формат.
2. Откройте диалоговое окно Формат ячеек и перейдите на вкладку Число.
3. В категории Все форматы в поле Тип введите необходимый код числового формата. В нашем случае мы введем: ДДДД ДД.ММ.СС чч:мм:cc и нажмем ОК.
4. Пользовательский числовой формат будет применен к ячейке.

Как видите все достаточно просто!

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

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в EXCEL)

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

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

Как правило, в таких ячейках установлен текстовый формат. Если изменить формат ячейки на числовой (через Формат ячеек или Главная/ Число/ Числовой формат ), то изменений не последует.

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

После преобразования числа будут выравнены по правому краю ячейки (если, конечно, в формате ячейки установлено Выравнивание по горизонтали — по значению ).

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

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

• введите в любую пустую ячейку число 1 ;
• выделите эту ячейку и скопируйте ее значение в Буфер обмена ( CTRL+C );
• выделите ячейки, которые содержат числа сохраненные как текст;
• выберите Специальная ставка (на вкладке Главная в группе Буфер обмена щелкните стрелку рядом с командой Вставить , а затем выберите в списке пункт Специальная ставка );
• выберите Умножить ;
• нажмите ОК .

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

Для EXCEL 2007: Если до умножения ячейки имели текстовый формат ячеек, то он будет сохранен (числовые значения все равно будут преобразованы, но будут выровнены по левому краю, как текст). Изменить формат ячеек на числовой можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

Этот же прием поможет преобразовать ДАТЫ, сохраненные как текст в формат ДАТ ( Если, конечно, EXCEL поймет ту форму, в которой записана ДАТА ). Правда, после преобразования в ячейках будет содержаться число, соответствующее датам . Изменить формат ячеек на формат ДАТЫ можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

СОВЕТ: О преобразовании чисел из ТЕКСТового формата в ЧИСЛОвой можно также прочитать в одноименной статье Преобразование чисел из текстового формата в числовой (часть 1) .