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

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

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

Общий;
Денежный;
Числовой;
Финансовый;
Текстовый;
Дата;
Время;
Дробный;
Процентный;
Дополнительный.

Кроме того, существует разделение на более мелкие структурные единицы вышеуказанных вариантов. Например, форматы даты и времени имеют несколько подвидов (ДД.ММ.ГГ., ДД.месяц.ГГ, ДД.М, Ч.ММ PM, ЧЧ.ММ и др.).

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

Способ 1: контекстное меню

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

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

Способ 2: блок инструментов «Число» на ленте

Форматирование также можно изменить, используя инструменты, находящиеся на ленте. Этот способ выполняется даже быстрее предыдущего.

Способ 3: блок инструментов «Ячейки»

Ещё одним вариантом настройки данной характеристики диапазона является использования инструмента в блоке настроек «Ячейки» .

Способ 4: горячие клавиши

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

Кроме того, отдельные комбинации горячих клавиш позволяют менять формат ячеек после выделения диапазона даже без вызова специального окна:

Ctrl+Shift+- — общий формат;
Ctrl+Shift+1 — числа с разделителем;
Ctrl+Shift+2 — времени (часы.минуты);
Ctrl+Shift+3 — даты (ДД.ММ.ГГ);
Ctrl+Shift+4 – денежный;
Ctrl+Shift+5 – процентный;
Ctrl+Shift+6 — формат О.ООЕ+00.

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

| урок 2 | урок 3 | урок 4 | урок 5

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

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

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

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

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

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

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

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

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

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

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

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

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

Строится на хранении дат в виде последовательных чисел. При этом дату 1 января 1900 года обозначили числом 1, а 1 января 2008 года - числом 39 448 (интервал между этими датами в днях как раз равен 39 448). Значение времени хранится в виде десятичной дроби, так как время является частью даты.

При использовании операционной системы Windows 2000 и выше Excel при вводе двухзначного числа года интерпретирует их в соответствии с настройками «Язык и стандарты». При этом при вводе даты как текстового значения, то есть с использованием точки, двухзначные годы интерпретируются следующим образом:

числа от 00 до 29 – эти двухзначные значения года воспринимаются как годы с 2000 по 2029.
числа от 30 до 99 – как годы с 1930 по 1999.

1 способ:

В группе «Число» раскройте список форматов в графе «Числовой формат» и выберите:
– «Краткая дата» – отображение даты только числами;
– «Длинный формат даты» – отображение даты числами и словами.

2 способ:

В окне открытого листа выделите нужные ячейки и перейдите к вкладке «Главная».
Ctrl+Shift+F ).
В окне «Формат ячеек» на вкладке «Число» в группе «Числовые форматы» выберите пункт «Дата».
После того как справа в окне отобразятся возможности данного формата, в группе «Тип» выберите нужный способ отображения даты в ячейках. При этом форматы даты, отмеченные * , зависят от настройки даты операционной системы, а неотмеченные – не зависят.

Информация

В пункте «Язык» при этом должен отображаться русский язык.

Закройте окно кнопкой «ОК».

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

В окне открытого листа выделите нужные ячейки и перейдите к вкладке «Главная».
В группе «Шрифт» щелкните по кнопке «Формат ячеек» (или используйте сочетание клавиш Ctrl+Shift+F ).
В окне «Формат ячеек» на вкладке «Число» в группе «Числовые форматы» выберите пункт «Все форматы».
Щелкните в строке «Тип» и наберите:
- М – для отображения месяцев в виде числе от 1 до 12;
- ММ – для отображения месяцев в виде числе от 1 до 12;
- МММ – для отображения месяцев в виде «янв»–«дек»;
- ММММ – для отображения месяцев в виде «январь»–«декабрь»;
- МММММ – для отображения месяцев в виде первой буквы месяца;
- д – для отображения дней в виде числа от 1 до 31;
- дд – для отображения дней в виде числа от 1 до 31;
- ддд – для отображения дней в виде «Пн»–«Вс»;
- дддд – для отображения дней в виде «понедельник»–«воскресенье»;
- гг – для отображения лет в виде чисел от 00 до 99;
- гггг – для отображения лет в виде чисел от 1900 до 9999;
Закройте окно кнопкой «ОК».

Знаменитые автомобили Порше Москва приятно радуют покупателей ценами.

Формат даты используется для отображения дат и времени, представленных числами, в виде дат. Система дат в Excel устроена так, что дату 01.01.1900 года приняли за отправную точку и обозначили числом 1, таким образом, любая дата представляет собой число дней, пройденных с 1 января 1900 года до этой даты. Значение времени является составной частью даты и хранится в виде десятичной дроби. Со значениями даты и времени можно производить математические действия, так как они являются числами.

Как задать формат даты в Excel 2007/2010?

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

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

Это окно также можно вызвать щелчком левой кнопки мыши на правый нижний уголок группы,

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

В появившемся окне «Формат ячеек» переходим на вкладку «Число» и в группе «Числовые форматы» выбираем пункт «Дата»

В правой части окна отображаются возможности данного формата, в группе «Тип» выбераем нужный способ отображения даты. Форматы даты, перед которыми стоит значок *(звездочка), зависят от даты операционной системы, форматы, перед которыми этого значка нет – не зависят.

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

Выделяем нужные ячейки в окне открытого листа и вызываем окно «Формат ячеек» любым из перечисленных выше способов. В окне «Формат ячеек» выбираем вкладку «Число» и в группе «Числовые форматы» выбираем пункт «Все форматы». После этого помещаем курсор в поле «Тип» и набираем код числового формата в русской раскладке клавиатуры:

М – для отображения месяцев в виде числа от 1 до 12;

ММ - для отображения месяцев в виде числа от 01 до 12;

МММ – для отображения месяцев в виде «янв» - «дек»;

ММММ - для отображения месяцев в виде «январь» - «декабрь»;

МММММ – для отображения месяцев виде первой буквы месяца;

Форматы с кодом "М" можно использовать для преобразования даты в месяц.

Д – для отображения дней в виде числа от 1 до 31;

Дд – для отображения дней в виде числа от 01 до 31;

Ддд – для отображения дней в виде «Пн» - «Вс»;

Дддд – для отображения дней в виде «понедельник» - «воскресенье»;

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

Гг – для отображения лет в виде чисел от 00 до 99;

Гггг – для отображения лет в виде чисел от 1900 до 9999.

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

Как установить систему дат 1904?

Кроме вышеописанной системы дат, Excel поддерживает еще одну систему – 1904 года, которая используется на компьютерах Macintosh. Excel автоматически производит смену системы дат, но при необходимости, это можно сделать самостоятельно вручную. Для этого нажимаем кнопку «Office», выбираем кнопку «Параметры Excel», заходим в раздел «Дополнительно» и находим группу «При пересчете этой книги». Смена системы дат производится установкой/снятием флажка перед пунктом «Использовать систему дат 1904».

Как вводить дату в ячейку?

Для того, чтобы Excel воспринимал информацию, вводимую в ячейку, как дату, необходимо использовать в качестве разделителя точку, тире либо наклонную черту «/». При этом все введенные числа, меньшие 31.12, 31-12 либо 31/12 будут автоматически восприниматься как даты, а большие - как текст. Как уже говорилось выше, ячейки с датами могут использоваться в расчетах.

Как в Excel вводить даты при помощи надстройки?

Для тех, кто активно работает с датами в своих таблицах, были созданы надстройки, представляющие собой календарь в Excel. Пример такой надстройки – календарь samradDatePicker. Скажем спасибо создателям этой надстройки и тем, кто доработал ее и перевел на русский язык! Надстройка упрощает ввод дат в ячейки листа Excel. После установки этой надстройки в контекстное меню (щелчок правой кнопки мыши) добавляется новый пункт «Выбрать из календаря», а при выделении ячеек, содержащих дату, справа от ячейки отображается значок календаря.

Как вставить в ячейку статическую, фиксированную, не обновляемую дату?

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

Как вставить в ячейку текущую автоматически обновляемую дату?

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

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

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

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

Изучение форматов дат и вычислений с ними

В Microsoft Office Excel даты хранятся в виде последовательных номеров. Например, в Microsoft Office Excel для Windows день 1 января 1900 г. имеет номер 1, а 1 января 2008 г. - 39 448, т. к. интервал меду этими датами составляет 39 448 дней. Office Excel хранит значения времени в виде дробной части числа, поскольку они рассматриваются как доли суток. Дробная часть числа меняется от 0 до 0,99999999 (что соответствует времени от 0:00:00 до 23:59:59).

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

Дополнительные сведения см. в указанных ниже разделах справки.

Получение сведений о двух системах дат

В Microsoft Excel для Macintosh и Microsoft Excel для Windows поддерживаются две системы дат: 1900 и 1904. По умолчанию в Microsoft Office Excel для Windows установлена система дат 1900, а в Microsoft Office Excel для Macintosh - 1904.

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

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

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

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

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

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

При вводе даты с двузначным обозначением года в ячейку с текстовым форматом или в качестве текстового аргумента функции, например, при вводе =ГОД("1.1.31") в приложении Excel год будет интерпретироваться следующим образом:

Значения от 00 до 29 интерпретируются как годы с 2000 по 2029. Например, если вводится дата 28.05.19 , она распознается как 28 мая 2019 г.

Значения от 30 до 99 интерпретируются как годы с 1930 по 1999. Например, если вводится дата 28.05.98 , она распознается как 28 мая 1998 г.

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

Windows 10

Панель управления и выберите Панель управления .

В разделе часы, язык и регион нажмите кнопку

Щелкните пункт .

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

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

В поле

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

Windows 8

Поиск Найти ), введите в поле поиска Панель управления Панель управления .

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

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

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

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

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

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

Windows 7

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

Щелкните .

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

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

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

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

Изменение стандартного формата даты для отображения года четырьмя цифрами

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

Windows 10

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

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

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

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

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

В списке Краткий формат

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

Windows 8

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

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

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

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

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

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

Windows 7

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

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

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

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

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

Изменение системы дат в Excel

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

Систему дат можно изменить следующим образом:

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

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

Исправление копировать и вставить проблемы

Чтобы получить дату на четыре года и один день позже, добавьте к ней 1462. Пример:

=Sheet1!$A$1+1462

Чтобы задать дату как четыре года и один день более ранней версии, вычитание 1462 от него. Пример.

=Sheet1!$A$1-1462

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