Excel дату в числовой код

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

​Смотрите также​ и выберите инструмент:​​ применению функции​​ вот это уже​ можно воспользоваться сочетанием​ функцию​

​Чтобы ввести дату в​ ячейке написана одним​ пробелы).​ часов вечера.​ желаемое — «Сегодня​ копирования, смотрите в​ символы​Вернитесь в списке​​Число​​В этом уроке мы​ и времени в​Весь курс: урок 1​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​ЧИСТРАБДНИ​ раскрывает перед пользователем​ клавиш​ЧАС​ Excel, используйте символы​ числом (например, так​В строке «Число​Если встроенных форматов не​ 02.10.10». Обратите внимание,​

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

​ статье «Копирование в​;##​Числовые форматы​.​ научились настраивать формат​ настройках операционной системы.​ |​Выберите ниже: «Использовать формулу​(NETWORKDAYS)​ массу возможностей.​Ctrl + Ж​(HOUR):​ «/» или «-«.​

​ — 20160112). нам​ знаков» написали цифру​ достаточно, то можно​ что ММ (Месяцы)​ Excel».​, вероятно, ширина ячейки​и выберите пункт​В списке​ отображения дат и​Работая с ячейками в​​урок 2​​ для определения форматируемых​описанной выше.​

​Считается простым вычитанием -​(или​=ЧАС(A1)​ Чтобы ввести время,​ нужно придать дате​ 1, п.ч. стоит​ настроить пользовательский формат​ записаны ПРОПИСНЫМИ буквами,​Если по какой-то​ недостаточна для отображения​форматы​категории​ времени в Microsoft​ Microsoft Excel, которые​| урок 3​ ячеек».​

​Вас не в понедельник​ из конечной даты​CTRL+SHIFT+4​=HOUR(A1)​ используйте «:» (двоеточие).​

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

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

​ Excel, а также​ содержат дату или​ | урок 4​Чтобы найти дату в​ родили? Нет? Уверены?​ вычитаем начальную и​если у вас​Примечание:​ Вы можете ввести​ точками год, месяц,​ бы было написано​ формат Даты и​

​ от минут, которые​ даты, то, возможно,​ щелкните правую границу​​Тип​​дату​​ разобрали несколько полезных​​ время, Вы имеете​​ | урок 5​​ тексте Excel для​ Можно легко проверить​

​ переводим результат в​​ другой системный язык​​Используйте функции​ дату и время​

​ день (например, так​ слово «год», то​ Времени).​ записываются строчными (см.​​ в ячейках стоит​​ столбца, содержащего ячейки​​вы увидите код​​.​ вариантов их ввода​ возможность применять к​Думаю, из прошлого урока​ поля ввода введите​​ при помощи функции​​Общий (General)​ по умолчанию).​МИНУТЫ​

​ в одной ячейке.​ — 2016.01.12). Как​ в строке написали​СОВЕТ:​ файл примера).​ не тот формат.​ с символами ;##.​ формата формат даты,​В группе​ на рабочий лист.​ ним различное форматирование.​ Вам уже известно,​

1. ​ формулу: =ЕТЕКСТ(A2) и​ДЕНЬНЕД​числовой формат, чтобы​
2. ​Если скопировать ячейку с​​(MINUTE) и​​Примечание:​ это сделать, читайте​​ бы цифру «3»,​​Если необходимо работать​
3. ​Приведем другие форматы:​​Напоминаем, что в​​ Размер столбца изменится​​ который вы выбрали​​Тип​ В следующем уроке​ Например, Вы можете​ что даты и​​ нажмите на кнопку​​(WEEKDAY)​​ показать разницу в​​ датой (протянуть за​
4. ​СЕКУНДЫ​На рисунках данной​

​ в статье «Преобразовать​ п.ч. нужно убрать​

​ с датами до​»дддд» (день недели);​ Excel есть два​ в соответствии с​ на предыдущем шаге.​выберите формат даты.​ мы поговорим о​ отображать в ячейке​ время в Excel​ «Формат», чтобы задать​из категории​ днях:​ правый нижний угол​(SECOND), чтобы вернуть​ статьи представлены даты​ число в дату​ три символа (буквы).​ 01.01.1900 — прочитайте​»ддд» (сокращенный день​

​ формата – формат​

office-guru.ru

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

​ длиной чисел. Вы​​ Не удается изменить​ Предварительный просмотр вашего​ 7-ми функциях Excel,​ только день недели,​ хранятся в виде​ желаемый цвет заливки​Дата и время​Здесь ситуация чуть сложнее.​ ячейки), удерживая​ минуты и секунды​ в формате США​ Excel».​В строке «Новый​ статью Вычисление с​ недели);​ ячейки и формат​ можете также перетащить​ формат даты, поэтому​ формата вместе с​ которые позволяют извлекать​ или только месяц​

​ порядковых чисел, началом​ для ячеек. Например,​.​​ Необходимо не учитывать​​правую​ соответственно.​ и ряда других​Преобразовать дату в текст​ текст» ничего не​ датами до 01.01.1900.​»д.М.гг»; ​​ значений. Подробнее об​​ правую границу столбца​ не обращайте влияя.​ первой датой в​ нужные параметры из​ и год, или​ отсчета которых принято​ зеленый. И нажмите​Первый аргумент этой функции​ субботы с воскресеньями​кнопку мыши, то​Чтобы прибавить часы, минуты​​ стран, где сперва​​ Excel.​​ пишем, нажимаем один​​Здесь рассмотрим, как​»дд.ММ.гг Ч:мм»;​ этих форматах, чем​

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

​ до необходимой ширины.​

1. ​ Внесенные вами изменения​ данных будет доступен​

2. ​ значений дат и​

   ​ же только время.​ считать некое​ ОК на всех​

3. ​ — ячейка с​​ и праздники. Для​​ можно выбрать -​​ и/или секунды, используйте​​ идут месяцы, а​

4. ​Чтобы​​ раз на пробел,​​преобразовать в​​»ч:мм:сс AM/PM»;​​ они отличаются, как​

   

5. ​Чтобы быстро ввести текущую​​ будут применяются только​​ в поле​ времени. На этом​Получить доступ ко всем​0 января 1900 года​ открытых окнах.​ датой, второй -​​ такого расчета лучше​​ как именно копировать​

   ​ функцию​​ затем дни. Тип​преобразовать дату в текст​ п.ч. ничего не​Excel текст в дату​»ДД МММ ГГГГ»;​ настроить нужный формат,​ дату, выберите любую​ к пользовательский формат,​

6. ​Образец​ урок закончен. Всего​ возможным форматам Вы​. К счастью, в​Как видно на рисунке​​ тип отсчета дней​

​ воспользоваться функцией​​ выделенную дату:​ВРЕМЯ​ формата зависит от​ Excel​ должно стоять после​и​СОВЕТ:​ т.д., читайте в​ пустую ячейку на​ который вы создаете.​.​ Вам доброго и​ можете на вкладке​ ячейках мы видим​ все даты в​ недели (самый удобный​ЧИСТРАБДНИ​

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

​Если Вам часто приходится​​(TIME):​​ ваших региональных настроек​(установить текстовый формат,​ цифр.​дату в текст Excel​Подробнее о пользовательских​ статье «Преобразовать дату​ листе, нажмите сочетание​

1. ​Внесите нужные изменения в​Примечание:​

2. ​ успехов в изучении​

   ​Число​ не эти числа,​ формате текст выделились​

3. ​ — 2).  ​​(NETWORKDAYS)​​ вводить различные даты​​=ВРЕМЯ(ЧАС(A1)+2;МИНУТЫ(A1)+10;СЕКУНДЫ(A1)+70)​​ Windows. Здесь вы​

4. ​ а не записать​​В этом диалоговом​​. Если мы скопировали​​ форматах дат и​​ в текст Excel»​ клавиш CTRL+; (точка​ поле​​  Форматы даты, начинающиеся​​ Excel.​диалогового окна​ а привычные для​
   

5. ​ цветом:​​Поскольку время в Excel,​​из категории​​ в ячейки листа,​​=TIME(HOUR(A1)+2,MINUTE(A1)+10,SECOND(A1)+70)​​ можете получить дополнительную​​ прописью), воспользуемся функцией​ окне мы написали​ или получили в​ времени читайте в​ здесь.​ с запятой), а​Тип​ со звездочки (*),​Автор: Антон Андронов​Формат ячеек​ нас даты и​

6. ​В условиях форматирования мы​ как было сказано​​Дата и время​​ то гораздо удобнее​Примечание:​ информацию о форматах​

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

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

• ​ используйте функцию​преобразует формат значения​ в который будем​ сможем отсортировать данные​В EXCEL дате соответствует​

• ​ прибавлять дни к​ будет обновляться до​Для отображения​ на панели управления.​​ вас актуальными справочными​​ дат:​ этом уроке Вы​ аргумент – ссылка​ его часть, то​ конечную даты и​ ячейке всегда была​ 70-60 = 10​ГОД​ ячейки в текстовый​ преобразовывать это значение.​ по дате. В​ целое положительное число,​

• ​ дате, вычитать, т.д.,​ текущей даты при​Используйте код​ Форматы без звездочки​ материалами на вашем​А в категории​ узнаете, как вводить​ на проверяемую ячейку.​

• ​ с временем также​ ячейки с датами​ актуальная сегодняшняя дата​ секунд.​(YEAR):​ формат (число будет​ Нажимаем «ОК».​​ ячейках есть формат​​ а времени суток​ смотрите в статье​

support.office.com

Преобразовать число в дату Excel.

​ каждом открытии листа​​Месяцев в виде чисел​ останутся без изменений.​ ​ языке. Эта страница​​Время​​ даты и время​​ Арес ссылки в​ возможны любые математические​ выходных (государственных праздников,​ — лучше воспользоваться​Урок подготовлен для Вас​=ГОД(A1)​ написано цифрами, не​
​Формула получится такая.​ ячейки, а есть​
​ — дробная часть​ «Работа с датами​ или перерасчете формулы,​
​ от 1 до​Если вы хотите применить​ переведена автоматически, поэтому​– встроенные форматы​ в Excel, чтобы​
​ аргументе функции ЕТЕКСТ​ операции, как и​
​ больничных дней, отпусков,​
​ функцией​ командой сайта office-guru.ru​
​=YEAR(A1)​ прописью).​
​ =ЗАМЕНИТЬ(A26;11;1;»»)Копируем формулу по​ формат значений.​ числа.​ в Excel» и​
​ введите в пустую​
​ 12​ формат даты согласно​
​ ее текст может​ времени:​ получать требуемое форматирование.​ должен быть относительным,​ с датой -​
​ отгулов и т.д.):​СЕГОДНЯ​Источник: http://www.excel-easy.com/functions/date-time-functions.html​Примечание:​У нас такая​ столбцу. Получилось так.​Бывает, стоит в​Датам от 01.01.1900 до​ в конце этой​ ячейку​
​м​ как другой язык​ содержать неточности и​Для того чтобы применить​Даты и время в​
​ так как будет​ сложение, вычитание и​Примечание​(TODAY)​Перевела: Ольга Гелих​Используйте функции​ таблица. В столбце​Осталось преобразовать этот текст​ ячейке формат «Дата»,​ 31.12.9999 в EXCEL​ же статьи, смотрите​=СЕГОДНЯ()​
​Месяцев в виде чисел​ отображает даты, выберите​ грамматические ошибки. Для​ форматирование к ячейке,​ Excel можно вводить​ проверятся каждая ячейка​ т.д.​: Эта функция появилась​:​Автор: Антон Андронов​МЕСЯЦ​ С написаны даты​ в дату.​
​ а сортировка по​ сопоставлены целые положительные​ перечень статей в​и нажмите клавишу​ от 01 до​ нужный язык в​ нас важно, чтобы​

excel-office.ru

Отображение в MS EXCEL формата ДАТЫ в ТЕКСТовой строке

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

​ 12​язык (местоположение).​ эта статья была​ формат в разделе​ числа или доли​ текущая проверяемая ячейка​ Если при сложении​ функций Excel начиная​

​ датой и установить​​ быстро — смотрим​​ДЕНЬ​ Нам нужно эти​​ Excel.​​ п.ч. даты написаны​ до 2958466. Например,​ по этой теме».​​Рассмотрим,​​мм​Совет:​ вам полезна. Просим​Тип​ суток, но как​ содержит текст (а​

​ нескольких временных интервалов​ с 2007 версии.​ для нее​ видео. Подробности и​(DAY), чтобы получить​ даты перевести в​В новом столбце​ текстом. Подробнее про​ дате 25.01.1900 соответствует​В Excel можно​как преобразовать число в​Месяцев в виде «янв»,​

​ У вас есть номера​

​ вас уделить пару​
​и нажать​ Вы сами понимаете,​
​ не дату) –​
​ сумма получилась больше​
​ В более древних​
​Общий формат​

​ нюансы — в​
​ месяц и день​ текст (формат текст).​ устанавливаем формат ячеек​ формат ячеек и​ число 25. Отсюда​ несколькими способами посчитать​

excel2.ru

Как Excel хранит дату и время

​ дату​ …, «дек»​ отображаются в ячейках,​ секунд и сообщить,​ОК​

​ это не совсем​ это неправильное значение​ 24 часов, то​ версиях сначала необходимо​(правой кнопкой по​ тексте ниже:​ даты.​Выделяем ячейку D26. На​ «Дата», выделяем первую​ значений в Excel,​

​ следует, что одним​ стаж, возраст. Про​Excel​ммм​ как ;? Весьма​ помогла ли она​. Нужное форматирование будет​ удобно. К тому​ Excel. Тогда функция​ Excel обнулит ее​ подключить надстройку​ ячейке​​Если иметь ввиду российские​​Чтобы добавить несколько дней​​ закладке «Главная» в​​ ячейку. На закладке​​ смотрите в статье​​ суткам соответствует число​ эти способы читайте​​и​​Месяцев в виде «январь»,​​ вероятно, что ваш​​ вам, с помощью​​ применено. Если встроенных​​ же при таком​

​ ЕТЕКСТ возвращает значение​ и начнет суммировать​Пакета анализа​Формат ячеек​ региональные настройки, то​ к дате, используйте​ разделе «Библиотека функций»​ «Формулы» в разделе​

Пример

​ «Преобразовать дату в​ 1.​ статью «Как быстро​дату в число.​ …, «декабрь»​ ячейки не недостаточно​ кнопок внизу страницы.​ числовых форматов Вам​ подходе к ячейке​ ИСТИНА и к​ опять с нуля.​

​. Для этого идем​- вкладка​ Excel позволяет вводить​ следующую формулу:​ нажимаем кнопку «Текстовые»,​ «Библиотека функций» нажимаем​

​ текст Excel».​
​Времени суток соответствует дробная​ посчитать стаж в​Например, в ячейке​мммм​ широк для отображения​

excel2.ru

Преобразовать текст в дату Excel.

​ Для удобства также​​ будет недостаточно, то​ ​ придется каждый раз​​ этой ячейке сразу​​ Чтобы этого не​​ в меню​Число​ дату очень разными​Чтобы прибавить годы, месяцы​ выбираем функцию «Текст».​ на кнопку «Время​У нас такая​ часть числа. Так​ Excel».​ Excel дата написана​
​Месяцев в виде первой​ целое число. Попробуйте​ приводим ссылку на​ можно воспользоваться категорией​ применять определенный числовой​ же присваивается новый​ происходило, нужно применить​Сервис — Надстройки​-​ способами — и​ и/или дни, используйте​
​ Появившееся диалоговое окно​ и дата». Нажимаем​
​ таблица.​ как одним суткам​При составлении формул для​ одним числом 20160112.​ буквы месяца​ дважды щелкнув правый​ оригинал (на английском​Все форматы​ формат.​ формат (зеленая заливка).​ к итоговой ячейке​(Tools — Add-Ins)​Общий​ понимает их все:​​ функцию​ ​ заполняем так.​
​ в появившемся списке​В ячейках таблицы стоит​ соответствует число 1,​
​ отображения в ячейке​ Нам нужно разделить​ммммм​ край столбца, содержащего​ языке) .​. Здесь Вы также​Excel предлагает несколько форматов​ Название функции ЕТЕКСТ​ формат​
​и ставим галочку​), то можно увидеть​»Классическая» форма​ДАТА​
​В строке «Значение» указали​ на​ формат «Дата», но​ а 1 час​ фразы содержащей текст​ точками число на​Дней в виде чисел​ ячейки с ;.​При вводе текста в​
​ сможете найти много​ для ввода временных​ следует читать как​37:30:55​ напротив​ интересную картинку:​3.10.2006​(DATE):​ адрес ячейки, дату​функцию «ДАТАЗНАЧ»​
​ написаны даты в​ – это 1/24​ и дату, например,​ год, месяц и​ от 1 до​ Это будет изменение​ ячейке такие как​
​ всего интересного.​ данных. Если Вы​ сокращение от двух​:​Пакет анализа​То есть, с точки​Сокращенная форма​=ДАТА(ГОД(A1)+4;МЕСЯЦ(A1)+2;ДЕНЬ(A1)+9)​
​ в которой нужно​. В появившемся диалоговом​ текстовом формате. В​
​ суток, 1 минута​ «Сегодня 02.10.10», можно​
​ день.​ 31​
​ размера столбца по​ «​Если ни один из​ примените такой формат,​ слов: Если ТЕКСТ​Если ячейки содержат неправильный​(Analisys Toolpak)​ зрения Excel, 27.10.2012​3.10.06​=DATE(YEAR(A1)+4,MONTH(A1)+2,DAY(A1)+9)​​ преобразовать в текст.​​ окне указываем адрес​ конце даты стоит​ – 1/24/60, 1​ получить вот такой​Как преобразовать число в​д​ размеру число. Можно​ ​2/2″​
​ вариантов Вас так​ Excel автоматически преобразует​​ формат значений –​. После этого в​ 15:42 = 41209,65417​С использованием дефисов​
​Примечание:​В строке «Формат»​ выделенной ячейки. Нажимаем​ буква «г» (12.02.​ сек – 1/24/60/60,​ результат: «Сегодня 40453»,​ дату Excel.​Дней в виде чисел​ также перетащить правую​, Excel предполагается, что​ и не устроил,​ введенную дату (или​
​Читаем логические функции, которыми​ это может привести​ Мастере функций в​На самом деле любую​3-10-6​Функция​
​ указали формат, в​ «ОК». Копируем формулу​
​ 2000г). Как убрать​ то, например, число​ т.е. дата будет​У нас такая​ от 01 до​ границу столбца, чтобы​ это является датой​ то можно создать​ время) в порядковое​ можно проверить другие​ к ошибочным вычислениям​ категории​ дату Excel хранит​С использованием дроби​
​ДАТА​ котором нужно записать​
​ по столбцу. Получилось​​ букву «г» и​ 0,5 тождественно 12:00.​​ отражена в числовом​ таблица с датами,​ 31​ упростить любой нужный​​ и форматы согласно​ ​ пользовательский числовой формат.​​ число (долю суток)​ форматы и типы​ в формулах. Например,​Дата и время​ и обрабатывает именно​
​3/10/6​(DATE) принимает три​ число.​ так, смотрите ячейку​ сделать эти даты​ Времени 2:35:20 (​ виде. Решим задачу​
​ написанными одним числом.​дд​ размер.​ даты по умолчанию​ Его создать достаточно​ и применит к​ данных в ячейках​​ вместо типа значения​появится необходимая нам​ так — как​Внешний вид (отображение) даты​
​ аргумента: год, месяц​Получилось так.​ С27.​ в формате «Дата».​
​2​
​ путем предварительного преобразования​Нам нужно сделать так,​Дней в виде «Пн»,​Если в поле​ на панели управления.​ легко, если в​ ячейке формат​
​ таким же способом:​ «Дата», ячейка содержит​ функция​ число с целой​​ в ячейке может​ и день. Excel​Формат даты пишем заглавными​Обратите внимание!​ Просто, установив в​часа​ даты в текст​ чтобы все даты​ …, «Вс»​Тип​ Excel может отформатировать​ качестве образца использовать​Даты​ЕНЕТЕКСТ – если не​

excel-office.ru

Функции даты и времени в Excel

• ​ тип значения «Текст».​
• ​ЧИСТРАБДНИ​
• ​ и дробной частью.​
• ​ быть очень разным​
• ​ знает, что 6+2=8​

​ буквами. Например, ММ​В ячейке С27​ ячейке формат «Дата»,​35​ в нужном нам​ были написаны в​ддд​нет подходящего формата,​

​ его как «​​ встроенные числовые форматы.​(или Времени), установленные​ текст (функция так​ При подготовке больших​(NETWORKDAYS)​ Целая часть числа​ (с годом или​ – это август.​ (большими буквами) -это​ стоит дата в​ ничего не получится.​минут​ формате.​

ГОД, МЕСЯЦ, ДЕНЬ

​ таком формате –​Дней в виде «понедельник»,​​ вы можете создать​​2 февраля»​

​ Чтобы создать пользовательский​
​ по умолчанию.​

​ же позволяет быстро​​ объемов данных следует​​.​​ (41209) — это​​ без, месяц числом​​ Как известно в​ месяц, а мм​ привычном нам формате​

Функция ДАТА

1. ​ Нам поможет​20​По аналогии с примером​

   

2. ​ 26.01.2016.​ …, «воскресенье»​ собственный. Для этого​​. Если вы измените​​ числовой формат, выполните​

   ​На рисунке ниже представлена​
​ найти дату в​

   

​ выполнить проверку всех​​Про то, как это​​ количество дней, прошедших​​ или словом и​ августе 31 день,​ (маленькими буквами) –​ даты, п.ч. мы​функция в​секунд) соответствует число​ из статьи Отображение​В ячейке В1​дддд​ проще всего взять​ параметры даты на​ следующие действия:​

Текущая дата и время

​ таблица вариантов ввода​ тексте Excel);​ типов значений на​​ правильно вычислять, лучше​​ с 1 января​

​ т.д.) и задается​
​ поэтому программа переходит​

​ это минуты, формула​​ перед вводом формулы​​Excel «ЗАМЕНИТЬ».​​ 0,10787 (​ ЧИСЛОвого формата в​ пишем такую формулу.​Лет в виде чисел​ за основу формат,​ панели управления, соответственно​Выделите ячейку, к которой​

​ даты и времени,​
​ЕЧИСЛО – если число​

ЧАС, МИНУТЫ, СЕКУНДЫ

​ соответствие определенному формату.​ почитать тут.​​ 1900 года (взято​​ через контекстное меню​

​ к следующему месяцу​
​ будет считать по-другому.​

​ настроили формат ячеек​​Итак, убираем букву​​2​​ ТЕКСТовой строке, рассмотрим,​​=ДАТА(ЛЕВСИМВ(A1;4);ПСТР(A1;5;2);ПРАВСИМВ(A1;2))​​ от 00 до​ который ближе всего​ изменится формат даты​

Функция ВРЕМЯ

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

​ за точку отсчета)​
​ - правой кнопкой​

​ (23 августа +​​Если нужен другой​ «Дата».​ «г» в конце​*1/24+​ как преобразовать дату​

​Копируем формулу по​ 99​
​ к нужному формату.​
​ по умолчанию в​

​ числовой формат.​

office-guru.ru

Основные принципы работы с датами и временем в Excel

Видео

​ В левой колонке​ неправильный формат чисел​ данными из разных​ системе отсчета даты​ до текущей даты.​

Как вводить даты и время в Excel

​ мыши по ячейке​ 9 дней =​ формат, то пропишем​В ячейке С26​ дат.​35​

​ источников где в​ Excel принимаются за​ А дробная часть​ и далее​ 1 сентября).​ его в формуле.​ тоже стоит дата,​В ячейке В1​*1/24/60+​ Начнем сразу с​Преобразовать дату в число​​Лет в виде чисел​ ​ отформатировать.​​ не нравится стандартный​

​Формат ячеек​ нужно вводить в​ЕОШ – если ошибка;​

​ разный способ фиксировалась​

​ единицу (см.выше), то​ (0,65417), соответственно, доля​Формат ячеек​Чтобы получить текущую дату​

​ Например, так.​

​ но указан порядковый​ вставляем формулу. На​20​ примера.​ Excel.​

​ от 1900 до​

Быстрый ввод дат и времени

​Нажмите сочетание клавиш CTRL+1.​ формат даты, можно​и перейдите на​ ячейку, а в​​ЕОШИБКА – если ошибка;​​ дата. С такой​​ для вычисления даты,​​ от суток (1сутки​(Format Cells)​ и время, используйте​

​В Excel можно​ номер даты. В​ закладке «Формулы» в​*1/24/60/60).​​Введем в ячейку​​Чтобы преобразовать дату​ 9999​На компьютере Mac нажмите​ выбрать другой формат​

​ вкладку​ правой — результат​ЕСЛИОШИБКА – если ошибка​ таблицей нельзя выполнять​ отстоящей от заданной​ = 1,0)​

​:​ функцию​ многое закрепить, чтобы​ таком формате Excel​ разделе «Библиотека функций»​​Для отображения времени и​ ​А1​​ в число, в​

Как Excel на самом деле хранит и обрабатывает даты и время

​гггг​ клавиши CONTROL+1 или​ даты в Excel,​​Число​​ преобразования. Стоит отметить,​ (это не логическая​​ различных вычислений. Визуально​​ на, допустим, 20​​Из всех этих фактов​​Время вводится в ячейки​​ТДАТА​​ не смещалось при​ хранит даты. Чтобы​

​ выбираем — «Текстовые».​ даты в EXCEL​дату 02.10.10. В​

​ соседней ячейке пишем​При изменении формат, который​ COMMAND+1.​ такие как «​.​ что датам, вводимым​ функция, но ее​ сложно заметить где​ дней, достаточно прибавить​ следуют два чисто​ с использованием двоеточия.​(NOW):​ перемещении, после фильтра,​ преобразовать дату в​ Нажимаем из списка​ существуют одноименные форматы​ ячейке​

​ такую формулу.​ содержит значения времени​В диалоговом окне​

• ​2 февраля 2012 г.»​В категории​ без указания года,​ легко оптимизировать под​ в неправильном формате​ к дате это​ практических вывода:​
• ​ Например​=ТДАТА()​ при копировании, т.д.​ нужном нам формате,​ на функцию «ЗАМЕНИТЬ».​ (Главная/ Число/ Формат​В1​=—(ТЕКСТ(A1;»ГГГГММДД»))​ и использовании «m»​Формат ячеек​

Количество дней между двумя датами

​или «​Все форматы​ присваивается текущий год,​ данную задачу);​​ введена неправильная дата​​ число.​Во-первых, Excel не умеет​16:45​

Количество рабочих дней между двумя датами

​=NOW()​ — заголовок, строку,​ достаточно установить обычным​ Заполняем диалоговое окно​ ячеек). Например, 14.01.2011​попробуем отразить фразу:​​Копируем формулу по​ ​ непосредственно после кода​​откройте вкладку​​2/2/12″​​в поле​ а именно выставленный​ЕПУСТО – если пусто;​ с точки зрения​Эту операцию осуществляет функция​ работать (без дополнительных​По желанию можно дополнительно​Примечание:​

​ картинку в ячейке,​​ способом формат даты​ так.​ можно отобразить как​ «Сегодня 02.10.10». Для​ столбцу. Получилось так.​ «h» или «hh»​Число​​. Можно также создать​​Тип​ в настройках Вашей​​ЕЛОГИЧ – если логическое​ ​ программы Excel.​​РАБДЕНЬ​ настроек) с датами​​ уточнить количество секунд​ ​Используйте функцию​​ ссылку, ячейку в​ в ячейке. (Формат​В строке «Старый текст»​​ 14-мар-2011 или как​​ этого в​Если нужно убрать из​​ или непосредственно перед​ ​.​​ собственный пользовательский формат​

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

​введите необходимый код​ операционной системы.​ значение;​

Сдвиг даты на заданное количество дней

​Чтобы быстро найти ошибочные​(WORKDAY)​ ранее 1 января​ — вводя их​СЕГОДНЯ​ формуле, т.д. Смотрите​ ячейки -> Дата).​ указываем первую ячейку,​ 2001-март.​B1​

Сдвиг даты на заданное количество рабочих дней

​ таблицы столбец А​​ кодом «ss», Excel​ ​В списке​​ в Microsoft Excel.​ числового формата. В​Это не все возможные​ЕНД – если недоступное​ значения в Excel​. Она позволяет вычислить​ 1900 года. Но​ также через двоеточие:​(TODAY), чтобы получить​ об этих способах​Теперь этот столбец​​ в которой нужно​ ​Дате 14.01.2011 соответствует число​​пишем формулу: =»Cегодня​

Вычисление дня недели

​ с датами, то​ отображает минуты вместо​Категория​Сделайте следующее:​​ нашем случае мы​ ​ варианты, которые поддерживает​​ значение (#Н/Д);​​ и выделить цветом​​ дату, отстоящую вперед​

​ это мы переживем!​16:45:30​ только текущую дату.​ в статье «Как​ можно сортировать по​ убрать букву «г».​

Вычисление временных интервалов

​ 40557. Введем в​ «&A1. В результате​ можно скрыть этот​ месяца.​выберите пункт​Выделите ячейки, которые нужно​ введем:​ Excel. Но даже​ЕНЕЧЁТ – если нечетное​ все ячейки с​ или назад относительно​ ;)​

​И, наконец, никто не​ Чтобы получить только​ закрепить в Excel​ дате. Как это​В строке «Нач_поз»​ ячейку 40557. Прибавим​ получим: «Сегодня 40453».​ столбец.​Чтобы быстро применить формат​Дата​ отформатировать.​ДДДД ДД.ММ.СС чч:мм:cc​​ этих вариантов Вам​​ значение;​

planetaexcel.ru

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

​ неправильным форматом, будем​ начальной даты на​Во-вторых, с датами и​ запрещает указывать дату​ текущее время, используйте​ заголовок, строку, ячейку,​ сделать, смотрите в​ указываем, какой по​ к 40557, например,​ EXCEL подставил вместо​Или, скопировать столбец​ даты по умолчанию,​и выберите нужный​Нажмите сочетание клавиш CTRL+1.​и нажмем​ будет предостаточно.​ЕЧЁТ – если четное​ использовать условное форматирование.​ нужное количество рабочих​ временем в Excel​ и время сразу​ следующую формулу (не​ ссылку, т.д.».​ статье «Сортировка по​ счету символ нужно​

Поиск формата текста вместо даты в Excel

​ 0,75 и применим​ даты «02.10.10» соответствующее​ В и вставить​ щелкните ячейку с​ формат даты в​На компьютере Mac нажмите​ОК​Некоторые варианты отображения дат,​

​ значение;​ Для примера возьмем​ дней (с учетом​

1. ​ возможно выполнять любые​ вместе через пробел,​ забудьте применить к​
2. ​ГОД, МЕСЯЦ, ДЕНЬ​ дате в Excel».​ убрать. У нас​
3. ​ к ячейке формат​ ему число 40453.​ в новый столбец​ датой и нажмите​ поле​ клавиши CONTROL+1 или​.​ представленные в правой​ЕССЫЛКА – если ссылка;​ простую таблицу:​ выходных суббот и​

​ математические операции. Именно​ то есть ​ ячейке формат времени):​Функция ДАТА​

​Преобразовать число в дату​ буква «г» -​ ДД.ММ.ГГ ч:мм;@ получим​Для отображения даты в​ значения ячеек. А​ сочетание клавиш CTRL+SHIFT+#.​Тип​ COMMAND+1.​Пользовательский числовой формат будет​ колонке, могут отличаться.​ЕФОРМУЛА – если формула.​Поиск и выделение цветом​ воскресений и государственных​ потому, что на​27.10.2012 16:45​=ТДАТА()-СЕГОДНЯ()​Текущая дата и время​ в Excel.​ это 11 по​ 14.01.11 18:00, т.к.​ нужном нам формате​ столбцs с датами​Если после применения к​. Вы можете настроить​В диалоговом окне​ применен к ячейке.​

​ Это зависит от​

​При желании можете проверить​ ячеек с неправильным​ праздинков). Использование этой​ самом деле они​Для ввода сегодняшней даты​

• ​=NOW()-TODAY()​ЧАС, МИНУТЫ, СЕКУНДЫ​Бывает, по разным​ счету символ (считаем​ 0,75 соответствует 3/4​
• ​ используем функцию ТЕКСТ():​ и формулами удалить.​ ячейке формата даты​ этот формат на​
• ​Формат ячеек​
• ​Как видите все достаточно​
• ​ региональных настроек и​ все функции в​ форматом отображения значений:​ функции полностью аналогично​ — числа! А​
• ​ в текущую ячейку​
• ​Чтобы вернуть часы, используйте​Функция ВРЕМЯ​
• ​ причинам, дата в​ и точки, и​
• ​ суток, т.е. 6​ =»Cегодня «&ТЕКСТ(A1;»дд.ММ.гг»). Получим​
• ​ Подробнее о способах​ в ней отображаются​
• ​ последнем этапе.​
• ​откройте вкладку​

​ просто!​ формата отображения даты​ действии экспериментальным путем.​

exceltable.com

​Выделите диапазон ячеек A2:A8​

Если в вашей ячейке лежит дата в виде текстовой строки, например, «8 март 2013», то для Excel это, естественно, не дата, а текст. Чтобы преобразовать текстовую дату в полноценную (читай – в числовой код даты, т.к. внутри Excel любая дата представлена именно числовым кодом), можно использовать специальную функцию ДАТАЗНАЧ (DATEVALUE). Она умеет превращать в нормальную дату различные текстовые варианты ее написания:

Как видно из скриншота, проблемы с преобразованием будут только у англоязычных дат (если у вас установлен русский Excel), с падежами месяцев (ибо их нет в английском языке в принципе) и с совсем уж невнятными цифровыми наборами типа «18 3 17». Со всем остальным эта функция замечательно справляется.

Если для ввода дат была использована не точка или косая черта, а запятая (что часто бывает при использовании дополнительной цифровой клавиатуры), то придется сначала заменить запятую на точку с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) и потом уже использовать функцию ДАТАЗНАЧ для преобразования получившегося текста в реальную дату:

Ссылки по теме

• Как Excel на самом деле хранит и обрабатывает даты и время
• Как найти ближайший рабочий день функцией РАБДЕНЬ (WORKDAY)
• Как вычислить разницу между двумя датами в днях, месяцах или годах

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

• 1. Преобразование даты в текст
  1.1 Преобразование даты в текст в формате мм / дд / гггг
  1.2 Преобразование даты в текст в формате mmddyyyy
  1.3 Преобразование даты в текст в других форматах
• 2. Преобразование даты в число
  2.1 Преобразование даты в число в 5-значном формате
  2.2 Преобразование даты в число в формате mmddyyyy
• 3. Преобразование даты в месяц / день / год или другие форматы даты с помощью щелчков мышью.
• 4. Загрузите образец файла.

В этом разделе представлены методы преобразования даты в текст в Excel.

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

В следующих случаях я расскажу вам, как использовать эту формулу.

1.1 Преобразование даты в текст в формате мм / дд / гггг

Например, для преобразования даты в ячейке A3 в мм / дд / гггг используйте формулу
= ТЕКСТ (A3; «мм / дд / гггг»)
нажмите Enter ключ

Если вы хотите преобразовать дату в ячейке A8 в дд / мм / гггг, используйте эту формулу
= ТЕКСТ (A8; «дд / мм / гггг»)
нажмите Enter ключ

1.2 Преобразование даты в текст в формате mmddyyyy или ddmmyyyy

Если вы хотите преобразовать дату в ячейке A13 в текст, но в формате mmddyyyy, введите эту формулу
= ТЕКСТ (A13, «ммддйгг»)
нажмите Enter ключ

Если вы хотите преобразовать дату в ячейке A18 в текст в формате ddmmyyyy, введите эту формулу
= ТЕКСТ (A18, «ддммйгг»)
нажмите Enter ключ

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

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

В этом разделе я предлагаю методы преобразования даты в число в 5-значном формате mmddyyyy.

2.1 Преобразование даты в число в 5-значном формате

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

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

2. в Формат ячеек диалог ， под Число вкладка ， выберите Общие из панели Категория.

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

2.2 Преобразование даты в число в формате mmddyyyy или ddmmyyyy

Если вы хотите преобразовать дату в числовую строку в формате mmddyyyy или ddmmyyyy, вы также можете применить функцию Format Cells.

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

2. в Формат ячеек диалога под Число вкладка, выберите На заказ из Категория панель, затем перейдите в правый раздел, введите ММДДГГГГ в Тип текстовое окно.

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

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

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

Попрощайтесь с мышиной рукой и шейным спондилезом

300 продвинутых инструментов Kutools for Excel решить 80% Задачи Excel за считанные секунды, избавят вас от тысяч щелчков мышью.

Легко справляйтесь с 1500 рабочими сценариями, не нужно тратить время на поиск решений, у вас будет много времени, чтобы наслаждаться жизнью. Ежедневно повышайте производительность на 80% для более чем 110000 высокоэффективных людей, включая вас. Больше не мучиться мучительными формулами и VBA, дайте своему мозгу отдых и радостное рабочее настроение. 30— дневная бесплатная пробная версия со всеми функциями, 30день назад деньги без причины. Лучшее тело создает лучшую жизнь.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

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

2. Нажмите Кутулс > Формат > Применить форматирование даты.

3. в Применить форматирование даты диалоговом окне выберите формат даты, который вы хотите использовать в Форматирование даты панель, тем временем преобразованный результат будет предварительно показан справа предварительный просмотр панель.

4. Нажмите Ok, то выбранные вами даты будут преобразованы в выбранный вами формат даты.

Дополнительные советы по преобразованию даты

Преобразование в Дату в Excel. Бесплатные примеры и статьи.

Функция ЗНАЧЕН() в MS EXCEL

Функция ДАТАЗНАЧ() в MS EXCEL

Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), — преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ(«11.09.2009») возвращает число 40067, соответствующее 11 сентября 2009 года. Но, функция ДАТАЗНАЧ() понимает только определенные форматы записи дат. Например, 2009-сент-11 она не поймет, а 11-сент-2009 — поймет.

Автоматическое преобразование формата ячейки в MS EXCEL при вводе ТЕКСТовых данных (Часть 2)

Продолжаем бороться в MS EXCEL 2007 с автоматическим преобразованием формата ячейки при вводе данных в ячейку. При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты, то EXCEL производит соответствующее преобразование и форматирование. Часто текстовые строки действительно имеют формат дат (2-3-8, т.е. 2 марта 2008), но на самом деле ими не являются (например, это м.б. артикул). В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.

Преобразование в MS EXCEL ТЕКСТовых значений в ДАТУ

Бывает, что при экспорте значений в EXCEL, даты записываются в незнакомом для EXCEL формате, например 20081223 (т.е. 2008г, 23 декабря). Для дальнейшей работы с такими датами выполним преобразование в привычный для EXCEL формат даты.

Является ли в MS EXCEL значение ДАТОЙ

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

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

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

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

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

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

Поскольку функция проверки ошибок в Excel распознает даты в текстовом формате с двузначным номером года, можно воспользоваться средством автозамены и преобразовать их в даты в формате даты. С помощью функции ДАТАЗНАЧ можно преобразовывать в даты большинство типов текстовых дат.

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

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

Примечания: Сначала убедитесь в том, что в Excel включена проверка ошибок. Для этого:

Щелкните Файл > Параметры > Формулы.

В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры ExcelExcel 2007формулы.

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

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

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

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

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

Нажмите появившуюся рядом с выделенной ячейкой кнопку ошибки.

В меню выберите команду Преобразовать XX в 20XX или Преобразовать XX в 19XX. Если вы хотите отключить индикатор ошибки, не преобразуя число, нажмите кнопку пропустить ошибку.

Текстовые даты с двумя цифрами года преобразуются в стандартные даты с четырьмя цифрами года.

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

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

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

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

Краткий формат даты выглядит следующим образом:

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

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

Выполните указанные ниже действия:

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

В пустой ячейке сделайте следующее.

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

Нажмите клавишу ВВОД, и функция ДАТАЗНАЧ возвращает порядковый номер даты, представленной текстовым форматом даты.

Что такое серийный номер Excel?

В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. является порядковым числом 1, а 1 января 2008 — порядковый номер 39448, так как он составляет 39 448 дня после 1 января, 1900.To скопировать формулу преобразования в диапазон смежных ячеек, выделите ячейку, содержащую введенную формулу. , а затем перетащите маркер заполнения по диапазону пустых ячеек, который соответствует размеру диапазона ячеек, содержащих текстовые даты.

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

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

Сочетание клавиш: Кроме того, можно нажать клавиши CTRL + C.

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

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

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

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

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

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

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

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

Функция ДАТАЗНАЧ для преобразования текста в дату в Excel

Функция ДАТАЗНАЧ в Excel предназначена для работы с текстовыми данными в формате ДАТА. Она используется для преобразования текстовых данных в формат Дата и возвращает числовое значение, характеризующее указанную дату.

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

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.

Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

Вид таблицы данных:

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

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

1. Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2). Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
2. Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
3. Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.

Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

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

Обработка значений даты в текстовом формате в Excel

Пример 2. В таблице Excel указаны даты неверного формата (вместо записи вид «13.06.2019» используется 13_06_2019). Такие данные указаны в двух столбцах. В соседнем необходимо вычислить разницу дней между указанными датами.

Вид таблицы данных:

Для расчетов используем следующую формулу:

Для получения текстовой строки, которая может быть преобразована в данные формата Дата с помощью функции ДАТАЗНАЧ, используем функцию ПОДСТАВИТЬ, которая выполняет замену символов «_» на «.». Результат вычитания двух полученных дат – искомое значение.

Растянем формулу вниз по столбцу чтобы рассчитать все значения:

Особенности синтаксиса функции ДАТАЗНАЧ в Excel

Функция ДАТАЗНАЧ имеет следующую синтаксическую запись:

Единственным аргументом (обязателен для заполнения) является дата_как_текст – текстовое представление даты, которое может быть преобразовано к данным формата Дата. В Excel есть несколько допустимых вариантов записи дат: 13-июн-2019, 13.06.2019. Любой из этих вариантов записи может быть использован в качестве аргумента функции ДАТАЗНАЧ.

1. Если текстовые строки, характеризующие даты, хранятся в ячейках Excel, большинство функций выполняют преобразования данных к требуемому типу автоматически. Однако, во избежание возможных ошибок, рекомендуется использовать функцию ДАТАЗНАЧ.
2. Рассматриваемая функция ориентируется на показания часов, встроенных в ПК, на котором используется редактор Excel. Если в качестве текстового представления даты указана неполная дата, например «13.06», данные о годе будут взяты из текущего времени. Например, функция =ДАТАЗНАЧ(“13.06”) вернет значение 43629, которое после установления формата Дата для ячейки будет преобразовано в 13.06.2019.
3. Если в качестве аргумента функции ДАТАЗНАЧ было передано значение, не преобразуемое к формату Дата (например, =ДАТАЗНАЧ(23), =ДАТАЗНАЧ(ИСТИНА), =ДАТАЗНАЧ(“333”)), будет возвращен код ошибки #ЗНАЧ!
4. Для склеивания значений, содержащихся в отдельных ячейках, чтобы «собрать» их в одну строку, характеризующую значение даты, следует использовать символ “&”. Например, в ячейках A1, B1, C1 хранятся значения 10, 3 и 2019 соответственно. Чтобы получить данные формата Дата и записать их в отдельную ячейку, можно использовать следующую функцию — =ДАТАЗНАЧ(A1&».»&B1&».»&C1).

Excel works!

Excel работает за вас

Excel works!

Thanks for Visiting

Как перевести время в число в Excel

Еще раз привет. Как обычно начну статью с того, что скажу — часто встречаюсь с каким-нибудь незнанием со стороны пользователей и сейчас расскажу об этом. Банально, но действительно так и приходят идеи о статье. Сегодня хочется написать отдельный текст про работу со временем в Excel. Как можно быстро обработать временные данные? Как перевести время в число в Excel? Как не запутаться в этом — читаем в тексте!

Время и число в Excel. =СЕГОДНЯ()

Сразу скажу что дата и время в Excel — это в первую очередь числовые данные. И только в определенном формате эти числа выглядят как время или дата.

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

Любое значение дата_время в Excel — это число преобразованное в нужный формат числа при помощи опции Формат ячеек!

Приведем пример. Вам нужно задать сегодняшнюю дату? Пожалуйста! Записываем функцию =СЕГОДНЯ() — без аргумента в скобках. Она возвращает сегодняшнее число. Как правило, сразу в этой ячейке будет дата в правильном формате.

Но что будет, если данные перевести в формат Числовой? Появиться число 42544,00.

Тоже самое будет и со временем в формате Время (см. на первой картинке), значение будет равно, например 21:00. Если сравнить значения чисел, то они будут идентичны.

Соответственно, как складывать и вычитать такие данные? Да как обычные числа! Если вы хотите, чтобы у вас в ячейке отображалось дата_время, просто сложите числа и задайте нужный формат. Как на примере:

Так же быть и с вычитанием данных друг из друга.

Теперь можно перейти к хитростям обработки дата_время.

Отделить дату от времени и наоборот в Excel

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

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

Воспользуйтесь формулой для определения времени из дата_время

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

Как перевести время в число в Excel? Функции ДЕНЬ, ЧАС, МИНУТЫ

Это тоже просто, если знаете хитрость. Очень и очень часто, нам удобнее использоваться формат 3,5 часа, чем 3:30. Т.е. перевод числа во временном формате в число, но с нужным значением. Для этого есть прекрасные функции Excel =ЧАС(), =МИНУТЫ().

Чтобы перевести время 3:30 в просто три с половиной часа, нам нужно воспользоваться формулой.

Как работает: мы находим количество полных часов функцией ЧАС, затем находим количество полных минут и делим их на 60, чтобы получить минут в часе. Готово!

Так же, как верно подсказали мне в комментариях, можно умножить 3:30 (в ячейке A1) на 24 — получится числовое значение времени.

Если необходимо подсчитать количество дней во времени (например 33:30:00) нужно воспользоваться функцией ДЕНЬ.

Важно! Не забудьте перевести ячейку в числовой формат, иначе после выполнения формулы, или в ячейке будет выдаваться 0:00:00
Это будет удобно при работе с большим объемом данных, вычислениями со временем или группировкой данных в сводной таблице

Эффективная работа в MS Office

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

Обратное преобразование даты в число

Когда вы связываете несколько систем друг с другом возможны всякие…нюансы. Вроде таблицы с числами вида

Само собой при выгрузке не имелся ввиду июнь 2034 года или 4 августа 2016 года. А конкретные числа 6.34 или 4.08, которые Excel по ошибке попытался преобразовать в даты. Тот же самый механизм, что и если вы введете в любой ячейке любого листа, например, текстом «12-15» и нажмете на Enter. Вы думаете, что вводите числовой диапазон, а Excel думает, что это незавершенная дата вида 12-15-2016 и «помогает» вам дописать его.

Исправить при вводе это автоматическое преобразование можно добавив в начало формулы одинарный апостроф — ‘ . В этом случае неважно что вы введете в значение ячейки (в том числе формулу) — Excel будет считать, что дальше текст.

Само преобразование может пойти по трем путям (в т.ч. в зависимости от региональных разделителей):
1. Если второе число меньше или равно 12. Excel будет пытаться определить в первом числе день, во втором числе месяц
2. Если второе число больше 12, но меньше или равно 29. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 1900
3. Если второе число больше или равно 30. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 2000

Итак, причины и механизмы возникновения ошибки ясны. Как поправить? Простая махинация вроде —A2 не поможет ведь после «дописки» значения до даты прошло преобразование и то же число 6.34 это теперь 12 571.

Исправление немного сложнее. Во-первых посмотрим по какому пути пошло преобразование. Если год от этой даты совпадает с текущим годом — значит мы имеем дело с первой ситуацией, в ином случае — ситуация 2 или 3.

Запишем формулу с вложенным ЕСЛИ():

Для Ситуации 1 мы должны склеить дни (целое число) и номер месяца (дробная часть числа):

Для Ситуации 2 и 3 мы должны взять номер месяца (целое число) и две последние цифры года (дробная часть числа).

Осталось только подставить эти значения в наш ЕСЛИ() в соответствующие группы формулы:

И вуаля. Можем преобразовать хоть 10 000 строк за раз.

PS К сожалению, в ситуации 1 мы не можем точно знать какое число было преобразовано в дату — с нулем, или без него как первый знак после запятой. То есть 4,08 и 4,8 в данном случае будут преобразованы одинаково.

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

Главная » Функции Excel »

Функции даты и времени

28 Апрель 2011              155552 просмотров

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

• ДАТА(DATE) — возвращает заданную дату в числовом формате Microsoft Excel:
  =ДАТА(2010; 6; 20)
  =DATE(2010, 6, 20)Год(year)(2010) — число, обозначающее год. Должно быть в интервале от 1900 до 2078.
  Месяц(month)(6) — число, обозначающее номер месяца в году. Если месяц больше 12, то это число прибавляется к первому месяцу указанного года. Так, ДАТА(2010;14;4) вернет дату 4 марта 2011 года.
  День(day)(20) — число, определяющее номер дня в месяце. Если день больше числа дней в указанном месяце, то это число будет прибавлено к первому дню указанного месяца. Например, ДАТА(2010;10;44) вернет дату 13 ноября 2010 года.
  Аргументы месяц и день так же могут быть указаны и отрицательными числами.
• ДАТАЗНАЧ(DATEVALUE) — Преобразует дату из текстового формата в числовой(понятный для Excel):
  =ДАТАЗНАЧ(«20.6.2009»)
  =ДАТАЗНАЧ(«20-ИЮН-09»)
  =DATEVALUE(«6/20/2009»)обе функции вернут 39984.Чтобы лучше понимать назначение данной функции советую ознакомиться со статьей: Как Excel воспринимает данные?
  Если в указанной текстом дате не указан год(«20.6»), то ДАТАЗНАЧ применит текущий год на основании системной даты ПК. Любая информация о времени(часы, минуты, секунды) этой функцией игнорируются. Чтобы преобразовать время необходимо воспользоваться функцией ВРЕМЗНАЧ(TIMEVALUE)
• ДНЕЙ360(DAYS360) — Вычисляет количество дней между двумя датами на основе 360-дневного года (двенадцать 30-дневных месяцев). Может использоваться для расчета платежей, если бухгалтерия основана на двенадцати 30-дневных месяцах:
  =ДНЕЙ360(нач_дата; кон_дата; метод)Нач_дата и кон_дата — даты, количество дней между которыми требуется узнать.
  Метод — логическое значение для указания метода расчета при вычислениях. Если не указан, указан ЛОЖЬ(FALSE) или 0, то применяется Американский метод(NASD). Он основан на том, что если начальная дата является 31-м числом месяца, то она считается равной 30-ому числу того же месяца. Если конечная дата является 31-м числом месяца и начальная дата меньше, чем 30-ое число, то конечная дата считается равной 1-ому числу следующего месяца, в противном случае конечная дата считается равной 30-ому числу того же месяца. Если указан ИСТИНА(TRUE) или 1, то применяется Европейский метод. В этом случае начальная и конечная даты, которые приходятся на 31-ое число месяца, считаются равными 30-ому числу того же месяца.
  =ДНЕЙ360(«30.11.13»; «7.12.13»)
  =ДНЕЙ360(«30.11.13»; «7.12.13»; 1)
  =ДНЕЙ360(A1; A2)все формулы вернут 7(при условии, что в A1 записана дата «30.11.13», а в A2 — «07.12.13»).
• ДАТАМЕС(EDATE) — Находит дату, отстоящую на заданное число месяцев вперед или назад от указанной даты:
  =ДАТАМЕС(нач_дата; число_месяцев)
  =ДАТАМЕС(«15.01.16»; 1)
  =ДАТАМЕС(«15.01.16»; -1)
  =EDATE(«01/15/16», 1)нач_дата(«15.01.16») — дата или ссылка на ячейку с датой, от которой необходимо вычислить новую дату
  число_месяцев(1) — целое число, определяющее количество месяцев до или после даты, указанной аргументом нач_дата. Если указано положительное число, то к указанной дате будет прибавлено указанное количество месяцев; если отрицательное — то из указанной даты будет вычтено указанное количество месяцев. Если указано не целое число, то от него отбрасывается дробная часть без применения округления(1,7 будет преобразовано в 1)
  Замечание: для версий Excel ниже 2007 функция может быть недоступна. Если функции нет в списке, то следует установить надстройку Пакет Анализа(если она не установлена), а затем подключить его с помощью команды Сервис —Надстройки
• КОНМЕСЯЦА(EOMONTH) — Определяет дату для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
  =КОНМЕСЯЦА(«01.01.2016»; 0)
  =EOMONTH(«01.01.2016», 0)нач_дата(«01.01.2016») — ссылка на ячейку с датой либо непосредственно дата в числовом или текстовом формате.
  число_месяцев(0) — число месяцев до или после даты, указанной аргументом нач_дата. Если указан 0, то будет возвращена дата последнего дня для месяца даты, указанной аргументом нач_дата. Любое положительное число прибавляет указанное кол-во месяцев от даты в нач_дата, а отрицательное — отнимает.
  Замечание: для версий Excel ниже 2007 функция может быть недоступна. Если функции нет в списке, то следует установить надстройку Пакет Анализа(если она не установлена), а затем подключить его с помощью команды Сервис —Надстройки
• ЧАС(HOUR) — возвращает количество часов для заданного значения времени:
  =ЧАС(«15:30:30»)
  =ЧАС(«10.04.2016 15:30:30»)
  =ЧАС(0,646180555555556)
  =ЧАС(6,646180555555556)
  =ЧАС(«3:30:30 PM»)
  =HOUR(«15:30:30»)все вышеприведенные формулы вернут число 15. В качестве аргумента можно задавать как время в текстовом формате(«15:30:30»), так и его эквивалент в виде дробной части(0,646180555555556). Так же можно указывать ссылку на ячейку, содержащую значение времени. Если вместе со временем указать дату(«10.04.2016 15:30:30»), то дата будет игнорироваться. Если число больше 1, то в расчет берется только дробная часть числа(0,646180555555556 и 6,646180555555556 вернут одинаковое значение)Чтобы лучше понимать работу данной функции советую ознакомиться со статьей: Как Excel воспринимает данные?
• МИНУТЫ(MINUTE) — возвращает количество минут для заданного значения времени:
  =МИНУТЫ(«15:30:30»)
  =МИНУТЫ(«10.04.2016 15:30:30»)
  =МИНУТЫ(0,646180555555556)
  =МИНУТЫ(6,646180555555556)
  =МИНУТЫ(«3:30:30 PM»)
  =MINUTE(«15:30:30»)все вышеприведенные формулы вернут число 30. В качестве аргумента можно задавать как время в текстовом формате(«15:30:30»), так и его эквивалент в виде дробной части(0,646180555555556). Так же можно указывать ссылку на ячейку, содержащую значение времени. Если вместе со временем указать дату(«10.04.2016 15:30:30»), то дата будет игнорироваться. Если число больше 1, то в расчет берется только дробная часть числа(0,646180555555556 и 6,646180555555556 вернут одинаковое значение)Чтобы лучше понимать работу данной функции советую ознакомиться со статьей: Как Excel воспринимает данные?
• СЕКУНДЫ(SECOND) — возвращает количество секунд для заданного значения времени:
  =МИНУТЫ(«15:30:45»)
  =СЕКУНДЫ(«10.04.2016 15:30:45»)
  =СЕКУНДЫ(0,646354166666667)
  =СЕКУНДЫ(6,646354166666667)
  =СЕКУНДЫ(«3:30:45 PM»)
  =SECOND(«15:30:45»)все вышеприведенные формулы вернут число 45. В качестве аргумента можно задавать как время в текстовом формате(«15:30:45»), так и его эквивалент в виде дробной части(0,646354166666667). Так же можно указывать ссылку на ячейку, содержащую значение времени. Если вместе со временем указать дату(«10.04.2016 15:30:45»), то дата будет игнорироваться. Если число больше 1, то в расчет берется только дробная часть числа(0,646354166666667 и 6,646354166666667 вернут одинаковое значение)Чтобы лучше понимать работу данной функции советую ознакомиться со статьей: Как Excel воспринимает данные?
• ДЕНЬ(DAY) — возвращает число, означающее номер дня для указанной даты, заданной в числовом формате или записанную текстом в одном из локальных форматов:
  =ДЕНЬ(«20.4.2009»)
  =ДЕНЬ(«20-ИЮН-09»)
  =ДЕНЬ(A1)
  =DAY(«6/20/2009»)все указанные функции вернут число 20, т.е. день месяца (при условии, что в А1 записана дата 20.06.2009). =ДЕНЬ(СЕГОДНЯ()) вернет номер дня месяца для текущей даты(на основании системной даты).
• МЕСЯЦ(MONTH) — возвращает число, означающее номер месяца для указанной даты, заданной в числовом формате или записанную текстом в одном из локальных форматов:
  =МЕСЯЦ(«20.6.2009»)
  =МЕСЯЦ(«20-ИЮН-09»)
  =МЕСЯЦ(A1)
  =MONTH(«6/20/2009»)все указанные функции вернут число 6, т.е. номер месяца для указанной даты(в А1 записана дата 20.06.2009). =МЕСЯЦ(СЕГОДНЯ()) вернет номер месяца для текущей даты(на основании системной даты).
• ГОД(YEAR) — возвращает число, означающее год для указанной даты, заданной в числовом формате или записанную текстом в одном из локальных форматов:
  =ГОД(«20.6.2009»)
  =ГОД(«20-ИЮН-09»)
  =ГОД(A1)
  =YEAR(«6/20/2009»)все указанные функции вернут 2009, т.е. год для указанной даты(в А1 записана дата 20.06.2009). =ГОД(СЕГОДНЯ()) вернет год для текущей даты(на основании системной даты).
• ЧИСТРАБДНИ(NETWORKDAYS) — возвращает количество рабочих дней между указанными датами. Рабочими днями считаются все дни за исключением выходных и праздничных дней. Праздничные дни указываются отдельно:
  =ЧИСТРАБДНИ(ДАТАЗНАЧ(«1.6.2015»); ДАТАЗНАЧ(«1.4.16»); F1:F30)
  =ЧИСТРАБДНИ(«1.6.2015»; «1.4.16»; F1:F30)
  =ЧИСТРАБДНИ(A1; B1; F1:F30)
  =ЧИСТРАБДНИ(A1; ДАТАЗНАЧ («01.04.16»); F1:F30)
  =NETWORKDAYS(«1.6.2015», «1.4.16», F1:F30)Нач_дата(«1.6.2015») — начальная дата для вычисления рабочих дней. Если начальная дата меньше конечной даты, то функция вернет отрицательное значение.
  Кон_дата(«1.4.16») — конечная дата для вычисления рабочих дней.
  Праздники(F1:F30) — необязательный аргумент, представляющий одну или нескольких дат в числовом формате, которые должны быть исключены из подсчета рабочих дней. Обычно это государственные праздники(новогодние, майские и т.п.). Как правило для этого аргумента отводится отдельный диапазон ячеек(F1:F30), в котором перечисляются праздничные даты. Но так же может быть представлен статичным массивом дат: {«7.1.2016″;»8.1.2016»}
• ТДАТА(NOW) — возвращает текущую дату и время. Не имеет аргументов:
  =ТДАТА()
  =NOW()функция не обновляется ежесекундно и будет вычислена только в случае, если на листе с функцией были произведены какие-либо изменения(вручную или макросами).
• ВРЕМЯ(TIME) — возвращает заданное в 24-часовом формате время в числовом формате Microsoft Excel:
  =ВРЕМЯ(15; 30; 45)
  =TIME(15, 30, 45)часы(hour)(15) — число, обозначающее час. Должно быть в интервале от 0 до 23.
  минуты(minute)(30) — число, обозначающее минуты часа. Если минуты больше 59, то излишек от 59 прибавляется к указанному часу. Так, ВРЕМЯ(15;78;45) вернет время 16:18:45.
  секунды(second)(45) — число, обозначающее секунды от минуты. Если секунды больше 59, то излишек будет прибавлен к минутам. Например, ВРЕМЯ(15;18;65) вернет время 15:19:05.
  Аргументы минуты и секунды так же могут быть указаны и отрицательными числами. В этом случае указанное количество минут будет вычтено из часа, а секунды — из минут: =ВРЕМЯ(15;18;-65) будет равно 15:16:55
• ВРЕМЗНАЧ(TIMEVALUE) — Преобразует время из текстового формата в числовой(понятный для Excel):
  =ВРЕМЗНАЧ(«15:30»)
  =ВРЕМЗНАЧ(«15:30:45»)
  =TIMEVALUE(«15:30:45»)первая функция вернет значение 0,645833333333333, а вторая — 0,646354166666667.Чтобы лучше понимать назначение данной функции и её результаты советую ознакомиться со статьей: Как Excel воспринимает данные?
  Если в указанном текстом времени не указаны секунды, то ВРЕМЗНАЧ приравняет их к нулевым(«15:00» = «15:00:00»). Если не указаны минуты, то функция вернет значение ошибки #ЗНАЧ!(#VALUE!). Любая информация о дате этой функцией игнорируются. Чтобы преобразовать дату необходимо воспользоваться функцией ДАТАЗНАЧ(DATEVALUE)
• СЕГОДНЯ(TODAY) — возвращает текущую дату и не имеет аргументов:
  =СЕГОДНЯ()
  =TODAY()функция не обновляется ежесекундно и будет вычислена только в случае, если на листе с функцией были произведены какие-либо изменения(вручную или макросами).
• ДЕНЬНЕД(WEEKDAY) — возвращает день недели для указанной даты. День недели возвращается как целое число в от 1 до 7. При этом вторым аргументом указывается тип определения начала недели — с Воскресенья или с Понедельника:
  =ДЕНЬНЕД(«02.04.2015»)
  =ДЕНЬНЕД(«2.4.2015»;2)
  =ДЕНЬНЕД(39905;1)
  =ДЕНЬНЕД(A1;2)
  =WEEKDAY(«4/2/2015»)дата_в_числовом_формате(«02.04.2015») — дата, записанная как текст(«02.04.2015» или «02 Апр 2015») или как число, определяющее дату(39905). Текст автоматически преобразуется в дату в числовом формате.Чтобы лучше понимать принципы задания аргументов для данной функции советую ознакомиться со статьей: Как Excel воспринимает данные?
  Тип — целое число, определяющее тип отсчета начала недели:
  1 — неделя начинается с Воскресенья. Это значит, что при задании даты «02.4.2015» будет возвращено число 5, хотя день недели Четверг.
  2 — неделя начинается с Понедельника. Значит при задании даты «02.4.2015» будет возвращено число 4, что наиболее подходящее для России и Европейской части населения.
  В версиях, новее 2003 так же есть и другие варианты, отвечающие за первый день недели(12,13 и т.д.), но в силу их отсутствия их практического применения они не рассматриваются.
• НОМНЕДЕЛИ(WEEKNUM) — возвращает номер рабочей недели года для указанной даты:
  =НОМНЕДЕЛИ(«02.4.2015»)
  =НОМНЕДЕЛИ(«02.4.2015»;1)
  =НОМНЕДЕЛИ(«02.4.2015»;1)
  =НОМНЕДЕЛИ(«02 апр 2015»;1)
  =НОМНЕДЕЛИ(A1;2)
  =WEEKNUM(A1,1)пор_номер_даты(«02.4.2015») — дата, записанная как текст(«02.04.2015» или «02 Апр 2015») или как число, определяющее дату(39905). Текст автоматически преобразуется в дату в числовом формате. Так же может быть ссылкой на ячейку с датой.Чтобы лучше понимать принципы задания аргументов для данной функции советую ознакомиться со статьей: Как Excel воспринимает данные?
  тип_возв(2) — необязательный аргумент, по умолчанию принимающий значение 1. Число, которое определяет, с какого дня начинается неделя. Определение первой недели на основании Системы 1: 1 или не указан — Воскресенье, 2 или 11 — Понедельник, с 12 до 17 — Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье. Определение первой недели на основании Системы 2: 21 — первым днем недели считается Понедельник.
  Система 1 — Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
  Система 2 — Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1.
• РАБДЕНЬ(WORKDAY) — возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от указанной даты. Рабочими днями не считаются выходные дни и дни, определенные как праздничные аргументом праздники. Как правило РАБДЕНЬ используется, чтобы исключить выходные дни или праздники при вычислении дат платежей, ожидаемых дат доставки или количества фактически отработанных дней:
  =РАБДЕНЬ(ДАТАЗНАЧ(«03.01.16»); 5)
  =РАБДЕНЬ(ДАТАЗНАЧ(«03.01.16»); 5; {42376; 42377})
  =РАБДЕНЬ(«03.01.16»; 5; {«7.1.2016″;»8.1.2016»})
  =РАБДЕНЬ(«03.01.16»; 5; F1:F30)
  =РАБДЕНЬ(«1/3/16»; 5; F1:F30)Нач_дата(«1.6.2015») — начальная дата для вычисления рабочих дней. Если нач_дата является недопустимой датой, то функция вернет значение ошибки #ЧИСЛО!(#NUM!).
  число_дней(5) — количество рабочих и не праздничных дней до или после нач_дата. Положительное число будет означать нахождение будущей даты от указанной аргументом нач_дата; отрицательное число — прошедшей даты от указанной аргументом нач_дата.
  Праздники(F1:F30) — необязательный аргумент, представляющий одну или нескольких дат в числовом формате, которые должны быть исключены из подсчета рабочих дней. Обычно это государственные праздники(новогодние, майские и т.п.). Как правило для этого аргумента отводится отдельный диапазон ячеек(F1:F30), в котором перечисляются праздничные даты. Но так же может быть представлен статичным массивом дат: {«7.1.2016″;»8.1.2016»}
• ДОЛЯГОДА(YEARFRAC) — Возвращает долю года, которую составляет количество дней между двумя указанными датами (начальной и конечной). Предназначена для определения доли общегодовых гонораров или обязательств, приходящихся на указанный период:
  =ДОЛЯГОДА(«01.01.16″;»30.06.16»;0)
  =ДОЛЯГОДА(«01.01.16″;»01.07.16»;3)
  =ДОЛЯГОДА(A1;B1;4)
  =YEARFRAC(A1,B1)нач_дата — дата в текстовом формате, непосредственно дата или ссылка на ячейку с начальной датой.
  кон_дата — дата в текстовом формате, непосредственно дата или ссылка на ячейку с конечной датой.
  Базис — тип используемого способа вычисления дня:
  • 0 или не указан — US (NASD) 30/360
  • 1- Фактический/фактический
  • 2 — Фактический/360
  • 3 — Фактический/365
  • 4 — Европейский 30/360

  Замечание: для версий Excel ниже 2007 функция может быть недоступна. Если функции нет в списке, то следует установить надстройку Пакет Анализа(если она не установлена), а затем подключить его с помощью команды Сервис —Надстройки

• РАЗНДАТ(DATEDIFF) – Вычисляет интервал в виде полного количества лет, месяцев или дней между двумя датами. Самое интересное в этой функции то, что она отсутствует в справке и её нельзя найти через Мастер вставки функций. Но эту функцию можно записать в ячейку напрямую.
  Эта функция способна правильно рассчитать количество полных лет для сотрудника от дня рождения до текущей даты. Без этой функции сделать это очень сложно.
  Синтаксис функции и аргументы:
  =РАЗНДАТ(Нач_дата; Кон_дата; Интервал)
  =РАЗНДАТ(ДАТАЗНАЧ(«1.6.2015»); ДАТАЗНАЧ(«1.4.16»); «y»)
  =РАЗНДАТ(«1.6.2015»; «1.4.16»; «ym»)
  =РАЗНДАТ(A1; B1; «yd»)
  =РАЗНДАТ(A1; ДАТАЗНАЧ («01.04.16»); «m»)
  =DATEDIFF(«1.6.2015», «1.4.16», «ym»)
  Нач_дата – начальная дата для расчета интервала.
  Кон_дата – конечная дата для расчета интервала. Должна быть всегда больше начальной даты.
  Интервал — определяет, как и в каких единицах будет рассчитываться интервал между начальной и конечной датами. Допускается указание следующих значений:
  • «y» — разница в полных годах
  • «m» — в полных месяцах
  • «d» — в полных днях
  • «yd» — разница в днях с начала года без учета лет
  • «md» — разница в днях без учета месяцев и лет
  • «ym» — разница в полных месяцах без учета лет

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

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

На картинке пример объединения даты с текстом в одной ячейке. Видите как выглядит наша дата? Это формат порядкового номера Excel.

Именно в таких ситуациях вам может понадобиться привести дату в «читаемый» формат.

Переводим дату в текстовый формат

Когда нам нужно привести дату в определенный формат (к примеру, 16-08-2016), мы можем вызвать функцию ТЕКСТ.

Итак, как она работает.

Синтаксис:

=ТЕКСТ(значение; формат_текста)

Она принимает два аргумента:

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

• Формат_текста — формат, в котором нужно показать нашу дату. Формат должен быть указан в двойных кавычках.

Какие есть форматы и разделители?

Формат дат состоит из 4 составляющих:

• день

• месяц

• год

• разделитель

Ниже приведены форматы, которые можно использовать для каждой части:

Формат дня:

• Д — день будет отображаться без нуля, т.е. 1 будет отображаться как 1, а 12 как 12.
• ДД — день будет отображаться с нулем, т.е. 1 будет 01, а 12 как 12.
• ДДД — будет отображаться день недели(сокращенно), т.е. «Пятница» будет «Пят».
• ДДДД — будет отображаться день недели(полностью), т.е. «Пятница» будет «Пятница».

Формат месяца:

• М — месяц будет отображаться без нуля, т.е. 1 будет отображаться как 1, а 12 как 12.
• ММ — месяц будет отображаться с нулем, т.е. 1 будет 01, а 12 как 12.
• МММ — месяц будет отображаться с сокращенным названием, т.е. «Сентябрь» будет «Сен».

М — месяц будет отображаться без нуля, т.е. 1 будет отображаться как 1, а 12 как 12.
ММ — месяц будет отображаться с нулем, т.е. 1 будет 01, а 12 как 12.
МММ — месяц будет отображаться с сокращенным названием, т.е. «Сентябрь» будет «Сен».
ММММ — месяц будет отображаться с полным названием, т.е. «Сентябрь» будет «Сентябрь».

Формат года:

• ГГ — год будет отображаться как 2 цифры, т.е. 2019 будет 19.
• ГГГГ — год будет отображаться полностью, т.е. 2019 будет 2019.

Разделитель:

• «/» : это просто разделитель между цифрами в дате, т.е. отображаться будет как 01/01/2019.
• «-»: такой же разделитель между цифрами, т.е. отображаться будет как 01-01-2019.
• Пробелы и запятые: тут немного посложней, вы можете комбинировать и создавать свои форматы отображения, например, 01 января, 2019 года.

Итак, разберем примеры.

Как привести указанную дату в текстовый формат

Пример:

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

Формула:

="Дата"&A2&"="&B2  

Теперь, нам нужно получить дату в «привычном» для нас виде.

Формула будет:

="Дата "&A2&" = "&ТЕКСТ(B2;"ДД.ММ.ГГГГ")

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

Какие еще есть форматы и как они выглядят на «выходе»:

Как привести текущую дату в текстовый формат

Для того чтобы получить текущую дату мы можем использовать функцию СЕГОДНЯ.

Формула:

="Сегодня "&ТЕКСТ(СЕГОДНЯ();"ДД/ММ/ГГГГ")

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

Переводим дату в текстовый формат с помощью функции «Текст по столбцам»

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

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

Пошаговая инструкция:

• Выделите ячейки, которые необходимо преобразовать в текст;

• Перейдите в раздел «Данные» -> «Текст по столбцам»;

• В открывшемся окне:

• Выберите опцию «с разделителями»;

• Снимите все галочки и нажмите «Далее»;

• Выберите формат «текстовый» укажите куда поместить наши, уже текстовые, значения и нажмите «Готово».

Функция сразу же переводит даты в текстовый формат.

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

Переводим дату в текстовый формат с помощью копирования и вставки

Пошаговая инструкция:

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

• Откройте обычный блокнот и вставьте наши данные туда, как только вы это сделаете, данные сразу же потеряют формат, т.е. станут обычным текстом (потому что в блокноте нет никаких форматов данных, кроме текста);

• Выделите ячейки, куда вы будете вставлять даты и поменяйте их формат на «Текстовый»;

• А теперь, заново скопируйте даты из блокнота, а затем вставьте их в Excel.

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

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

На картинке пример объединения даты с текстом в одной ячейке. Видите как выглядит наша дата? Это формат порядкового номера Excel.

Преобразование строки даты в дату

= ЛЕВСИМВ (дата; 10) + ПСТР (дата; 12;8)

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

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

В показанном примере мы используем приведенные ниже формулы.

Для извлечения даты формула в C5:

= ДАТАЗНАЧ (ЛЕВСИМВ(B5;10))

Чтобы извлечь дату, формула в D5:

= ВРЕМЗНАЧ (ПСТР (B5;12;8))

Чтобы собрать дату-время, формула в E5:

= C5 + D5

Чтобы получить дату, мы извлекаем первые 10 символов значения с помощью ЛЕВСИМВ:

ЛЕВСИМВ(B5;10) // возвращает «2015-03-01»

Результатом является текст, поэтому, чтобы заставить Excel интерпретироваться как дата, мы помещаем ЛЕВСИМВ в ДАТАЗНАЧ, который преобразует текст в правильное значение даты Excel.

Чтобы получить время, мы извлекаем 8 символов из середины значения с ПСТР:

ПСТР (B5;12;8) // возвращает «12:28:45»

Опять же, результатом является текст. Чтобы заставить Excel интерпретироваться как время, мы помещаем ПСТР в ВРЕМЗНАЧ, который преобразует текст в правильное значение времени Excel.

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

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

= ЛЕВСИМВ(дата; 10) + ПСТР(дата; 12;8)

Обратите внимание, что в этом случае значения ДАТАЗНАЧ и ВРЕМЯЗНАЧ не нужны, поскольку математическая операция (+) заставляет Excel автоматически принудительно передавать текстовые значения в числа.

Преобразовать дату в Юлианский формат

= ГОД (дата) и ТЕКСТ (дата-ДАТА (ГОД (дата); 1;0); «000»)

Если вам нужно преобразовать дату в формат даты в Юлиане в Excel, вы можете сделать это, построив формулу, в которой используются функции ТЕКСТ, ГОД и ДАТА.

«Формат даты в Юлиане» относится к формату, в котором значение года для даты комбинируется с «порядковым днем для этого года» (т. Е. 14-й день, 100-й день и т. д.) для формирования штампа даты.

Есть несколько вариантов. Дата в этом формате может включать в себя 4-значный год (гггг) или год с двумя цифрами (гг), а номер дня может быть заполнен нулями или может быть не дополнен тремя цифрами. Например, на дату 21 января 2017 года вы можете увидеть:

1721 // ГГД

201721 // ГГГГ

2017021 // ГГГГДДД

Для двухзначного года + число дня без дополнения используйте:

= ТЕКСТ (B5; «гг») & B5-ДАТА(ГОД (B5); 1;0)

Для двузначного года + число дня, дополненное нулями до 3-х мест:

= ТЕКСТ (B5; «гг») & ТЕКСТ (B5-ДАТА (ГОД (B5); 1;0); «000»)

Для четырехзначного года + число дня, дополненное нулями до 3-х мест:

= ГОД(B5) & ТЕКСТ(B5-ДАТА(ГОД(B5); 1;0); «000»)

Эта формула строит окончательный результат в 2 частях, объединенных конъюнкцией с оператором амперсанда (&).

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

ТЕКСТ (B5; «гг»)

Чтобы извлечь полный год, используйте функцию ГОД:

ГОД (B5)

С правой стороны амперсанда нам нужно определить день года. Мы делаем это, вычитая последний день предыдущего года с того дня, с которым мы работаем. Поскольку даты — это просто серийные номера, это даст нам «n» день года.

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

B5-ДАТА(ГОД (B5); 1;0)

Дает нам последний день предыдущего года, который на примере 31 декабря 2015 года.

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

ТЕКСТ (B5-ДАТА (ГОД (B5); 1;0); «000»)

Если вам нужно преобразовать юлианскую дату назад к обычной дате, вы можете использовать формулу, которая анализирует юлианскую дату и пробегает ее через функцию даты с месяцем 1 и днем, равным «n-му» дню. Например, это создаст дату с Юлианской датой ггггддд, например, 1999143.

= ДАТА(ЛЕВСИМВ(A1;4); 1; ПРАВСИМВ(A1;3)) // для ггггддд

Если у вас есть только номер дня (например, 100, 153 и т. д.), вы можете жестко закодировать год и вставить следующий день:

= ДАТА (2016;1; A1)

Где A1 содержит номер дня. Это работает, потому что функция ДАТА умеет настраивать значения вне диапазона.

Преобразование даты в месяц и год

= ТЕКСТ(дата; «ггггмм»)

Чтобы преобразовать нормальную дату Excel в формат ггггмм (например, 9/1/2017> 201709), вы можете использовать функцию ТЕКСТ.

В показанном примере формула в C5:

= ТЕКСТ (B5; «ггггмм»)

Функция TEКСT применяет заданный числовой формат к числовому значению и возвращает результат в виде текста.

В этом случае предоставляется формат числа «ггггмм», который присоединяется к 4-значному году с 2-значным значением месяца.

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

Преобразование даты в текст

= ТЕКСТ (дата; формат)

Если вам нужно преобразовать даты в текст (т. е. дату в преобразование строк), вы можете использовать функцию ТЕКСТ. Функция ТЕКСТ может использовать такие шаблоны, как «дд / мм / гггг», «гггг-мм-дд» и т. д., чтобы преобразовать действительную дату в текстовое значение.

Даты и время в Excel хранятся в виде серийных номеров и преобразуются в удобочитаемые значения «на лету» с использованием числовых форматов. Когда вы вводите дату в Excel, вы можете применить числовой формат, чтобы отобразить эту дату по своему усмотрению. Аналогичным образом, функция ТЕКСТ позволяет преобразовать дату или время в текст в предпочтительном формате. Например, если дата 9 января 2000 года введена в ячейку A1, вы можете использовать TEКСТ, чтобы преобразовать эту дату в следующие текстовые строки следующим образом:

= ТЕКСТ(A1; «ммм») // «Янв»

= TEКСТ(A1; «дд/мм/гггг») // «09/01/2012»

= ТЕКСТ(A1; «дд-ммм-гг») // «09-Янв-12»

Вы можете использовать TEКСТ для преобразования дат или любого числового значения в фиксированном формате. Вы можете просмотреть доступные форматы, перейдя в меню «Формат ячеек» (Win: Ctrl + 1, Mac: Cmd + 1) и выбрав различные категории в списке слева.

Преобразование даты текста дд/мм/гг в мм/дд/гг

= ДАТА(ПРАВСИМВ(A1;2) + 2000; ПСТР(A1;4;2); ЛЕВСИМВ(A1;2))

Чтобы преобразовать даты в текстовом формате дд/мм /гг в истинную дату в формате мм/дд/гг, вы можете использовать формулу, основанную на функции ДАТА. В показанном примере формула в C5:

= ДАТА(ПРАВСИМВ(B5;2) + 2000; ПСТР(B5;4;2); ЛЕВСИМВ (B5;2))

Который преобразует текстовое значение в B5 «29/02/16» в правильную дату Excel.

Ядром этой формулы является функция ДАТА, которая используется для сборки правильного значения даты Excel. Функция ДАТА требует действительных значений года, месяца и дня, поэтому они анализируются из исходной текстовой строки следующим образом:

Значение года извлекается с помощью функции ПРАВСИМВ:

ПРАВСИМВ(B5;2) +2000

ПРАВСИМВ получает по крайней мере 2 символа от исходного значения. Число 2000 добавлено к результату, чтобы создать действительный год. Это число переходит в ДАТА в качестве аргумента год.

Значение месяца извлекается с помощью:

ПСТР(B5;4;2)

ПСТР извлекает символы 4-5. Результат переходит в ДАТА в качестве аргумента месяц.

Значение дня извлекается с помощью:

ЛЕВСИМВ(B5;2)

ЛЕВСИМВ захватывает последние 2 символа исходного текстового значения, которое переходит в ДАТА в качестве аргумента дня.

Три значения, извлеченные выше, входят в ДАТУ следующим образом:

= ДАТА (2016; «02»; «29»)

Хотя месяц и день предоставляются в виде текста, функция ДАТА автоматически преобразуется в числа и возвращает действительную дату.

Примечание: значение 2016 года автоматически было преобразовано в число при добавлении 2000.

Если исходное текстовое значение содержит дополнительные начальные или конечные символы пробела, вы можете добавить функцию СЖПРОБЕЛЫ для удаления:

= ДАТА(ПРАВСИМВ (СЖПРОБЕЛЫ (A1); 2) + 2000; ПСТР(СЖПРОБЕЛЫ (A1); 4;2); ЛЕВСИМВ(СЖПРОБЕЛЫ (A1); 2))

Преобразование текста в дату

=ДАТА (ЛЕВСИМВ(текст; 4); ПСРТ(текст; 5;2); ПРАВСИМВ(текст; 2))

Чтобы преобразовать текст в непринятом формате даты в правильную дату Excel, вы можете проанализировать текст и собрать правильную дату с формулой, основанной на нескольких функциях: ДАТА, ЛЕВСИМВ, ПСРТ и ПРАВСИМВ.

В показанном примере формула в C6:

= ДАТА(ЛЕВСИМВ(B6;4); ПСРТ(B6;5;2); ПРАВСИМВ(B6;2))

Эта формула отдельно извлекает значения года, месяца и дня и использует функцию ДАТА, чтобы собрать их в дату 24 октября 2000 года.

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

текст (19610412) Дата представления (Апрель 12, 1961)

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

Функция ДАТА принимает три аргумента: год, месяц и день. ЛЕВСИМВ извлекает самые левые 4 символа и поставляет это в ДАТА в качестве года. Функция ПСРТ извлекает символы 5-6 и поставляет это в ДАТА в качестве месяца, а функция ПРАВСИМВ извлекает самые правые 2 символа и поставляет их в ДАТА в качестве дня. Конечным результатом является правильная дата Excel, которая может быть отформатирована любым способом.

В строке 8 (непризнанный) формат даты дд.мм.гггг и формула в C8:

= ДАТА(ПРАВСИМВ(B8;4); ПСРТ(B8;4;2); ЛЕВСИМВ(B8;2))

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

Чтобы преобразовать даты, добавив нуль, попробуйте Специальную вставку:

• Добавить ноль в неиспользуемую ячейку и скопировать в буфер обмена
• Выберите проблемные даты
• Специальная вставка> Значения> Добавить

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

= A1 + 0

Где A1 содержит непризнанную дату.

Другой способ заставить Excel распознавать даты — использовать текст в столбцах:

Выберите столбец дат, затем попробуйте Дата> Текст в столбах>Исправлено> Конец

Это иногда может исправить все сразу.