Excel дата перевод в месяц

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

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

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

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

[цвет]”любой текст”КодФормата”любой текст”

Посмотрите простые примеры использования форматирования. В столбце А – значение без форматирования, в столбце B – с использованием пользовательского формата (применяемый формат в столбце С)

Какие цвета можно применять

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Числовые форматы

Форматы даты

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат “ДД.ММ.ГГГГ” отформатирует дату в привычный нам вид 31.12.2017, а формат “ДД МММ” преобразует дату в вид 31 Дек.

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

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

С использованием дефисов

С использованием дроби

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню – правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells) :

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

По желанию можно дополнительно уточнить количество секунд – вводя их также через двоеточие:

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

27.10.2012 16:45

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

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата – лучше воспользоваться функцией СЕГОДНЯ (TODAY) :

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

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек – вкладка Число – Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так – как число с целой и дробной частью. Целая часть числа (41209) – это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!
• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они – числа! А вот это уже раскрывает перед пользователем массу возможностей.

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

Считается простым вычитанием – из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

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

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис – Надстройки (Tools – Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak) . После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS) .

Функция ГОД в Excel

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

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

ДЕНЬНЕД

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

“ВЫБОР”.

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

В этой ситуации вам поможет функция ВЫБОР и уже пройденная нами функция МЕСЯЦ. Построим формулу. Для этого нам необходимо указать пользовательское имя для всех 12 месяцев в функции и использовать функцию месяца, чтобы получить номер месяца из даты.

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

НОМНЕДЕЛИ

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)

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

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

Поэтому вы не можете выразить свое логическое условие просто как >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.

Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).

Полная формула ЕСЛИ может иметь следующую форму:

Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».

Расширенные формулы ЕСЛИ для будущих и прошлых дат

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

Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:

Если условие не выполнено, то в ячейку запишем пустую строку “”.

А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».

Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:

Перевод разных написаний дат

Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:

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

Получение значения даты

С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:

Умножение текстового значения на единицу

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

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

Синтаксис

=DATE(year, month, day) – английская версия

=ДАТА(год; месяц; день) – русская версия

Аргументы

• Year (Год) – значение года, которое важно отобразить в дате;
• Month (Месяц) – значение месяца, которое важно отобразить в дате;
• Day (День) – значение дня, которое важно отобразить в дате.

Вставка текущей даты и времени.

В Microsoft Excel вы можете сделать это в виде статического или динамического значения.

Как вставить сегодняшнюю дату как статическую отметку.

Для начала давайте определим, что такое отметка времени. Отметка времени фиксирует «статическую точку», которая не изменится с течением времени или при пересчете электронной таблицы. Она навсегда зафиксирует тот момент, когда ее записали.

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

• Ctrl + ; (в английской раскладке) или Ctrl+Shift+4 (в русской раскладке) вставляет сегодняшнюю дату в ячейку.
• Ctrl + Shift + ; (в английской раскладке) или Ctrl+Shift+6 (в русской раскладке) записывает текущее время.
• Чтобы вставить текущую дату и время, нажмите Ctrl + ; затем нажмите клавишу пробела, а затем Ctrl + Shift +;

Скажу прямо, не все бывает гладко с этими быстрыми клавишами. Но по моим наблюдениям, если при загрузке файла у вас на клавиатуре был включен английский, то срабатывают комбинации клавиш на английском – какой бы язык бы потом не переключили для работы. То же самое – с русским.

Как сделать, чтобы дата оставалась актуальной?

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

• =СЕГОДНЯ()- вставляет сегодняшнюю дату.
• =ТДАТА()- использует текущие дату и время.

В отличие от нажатия специальных клавиш, функции ТДАТА и СЕГОДНЯ всегда возвращают актуальные данные.

А если нужно вставить текущее время?

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

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

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

При использовании формул имейте в виду, что:

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

Как поставить неизменную отметку времени автоматически формулами?

Допустим, у вас есть список товаров в столбце A, и, как только один из них будет отправлен заказчику, вы вводите «Да» в колонке «Доставка», то есть в столбце B. Как только «Да» появится там, вы хотите автоматически зафиксировать в колонке С время, когда это произошло. И менять его уже не нужно.

Для этого мы попробуем использовать вложенную функцию ИЛИ с циклическими ссылками во второй ее части:

Где B – это колонка подтверждения доставки, а C2 – это ячейка, в которую вы вводите формулу и где в конечном итоге появится статичная отметка времени.

В приведенной выше формуле первая функция ЕСЛИ проверяет B2 на наличие слова «Да» (или любого другого текста, который вы решите ввести). И если указанный текст присутствует, она запускает вторую функцию ЕСЛИ. В противном случае возвращает пустое значение. Вторая ЕСЛИ – это циклическая формула, которая заставляет функцию ТДАТА() возвращать сегодняшний день и время, только если в C2 еще ничего не записано. А если там уже что-то есть, то ничего не изменится, сохранив таким образом все существующие метки.

О работе с функцией ЕСЛИ читайте более подробно здесь .

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

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

Складывать и вычитать календарные дни

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

Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».

Как выглядит формат ячейки в Excel

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

К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.

Добавить к дате рабочие дни

Функция РАБДЕНЬ позволяет точно посчитать дату через нужное количество рабочих дней. Эта функция состоит из трех элементов:

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

Например, директор дал вам поручение, которое необходимо выполнить за 25 рабочих дней. Допустим, сегодня вторник, 5 ноября 2019 года. Эту дату вносим в ячейку A1. Функция =РАБДЕНЬ(A1;25) определит крайний день, когда вы должны его выполнить, — 10 декабря 2019 года. При этом не забудьте поставить в ячейке с результатом формат «Дата».

Помните, что функция РАБДЕНЬ автоматически убирает из подсчетов только субботы и воскресенья. О праздниках Excel не знает. Их нужно заносить в функцию вручную. Чтобы вы не запутались, мы подготовили файл, в который уже внесли все праздники 2020 года. Ищите его в электронной версии статьи.

Как прибавить (вычесть) несколько недель к дате

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

Прибавляем N недель к дате в Excel:

= A2 + N недель * 7

Например, чтобы прибавить 3 недели к дате в ячейке А2, используйте следующую формулу:

= А2 — N недель * 7

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

Как прибавить (отнять) годы к дате в Excel

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

На листе Excel, формулы могут выглядеть следующим образом:

Прибавляем 5 лет к дате, указанной в ячейке A2:

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

Как отличить обычные даты Excel от «текстовых дат»

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

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

Даты

Текстовые значения

· Выровнено по правому краю.

· Указан формат даты в поле «Числовой формат» на вкладке «Главная » — «Число» .

· По левому краю по умолчанию.

· Общий формат отображается в поле «Число» на вкладке «Главная» — «Число».

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

Их можно легко распознать, немного расширив столбцы, выделив один из них, вы­брав команду Формат ► Ячейки ► Выравнивание (Format ► Cells ► Alignment) и для па­раметра По горизонтали (Horizontal) выбрав значение Общий (General) (это вид ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите на таблицу. Если какие-либо значения не выровнены по правому краю, значит Excel не считает их датами.

Как конвертировать текст в дату в Excel

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

Математические операции для преобразования текста в дату

Помимо использования функций Excel, о которых мы говорили чуть выше, вы можете выполнить простую математическую операцию, чтобы заставить программу выполнить реорганизацию строки в дату. Обязательное условие: операция не должна изменять ее значение (порядковый номер дня). Звучит немного сложно? Следующие примеры помогут разобраться!

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

• Сложение: =A1 + 0
• Умножение: =A1 * 1
• Деление: =A1 / 1
• Двойное отрицание: =–A1

Как вы можете убедиться, математические операции могут помочь с датами (строки 3,4.,5,7), временем (строки 2 и 6), а также числами, отформатированными как текст (строка 8).

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

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

Если запись содержит какой-либо разделитель, отличный от косой черты (/) или тире (-), функции Excel не смогут распознать их как даты и вернут ошибку #ЗНАЧ!. Чаще всего такие «неправильные» разделители – это пробел и запятая.

Чтобы это исправить, вы можете запустить инструмент поиска и замены, чтобы заменить этот неподходящий разделитель, к примеру, косой чертой (/):

• Выберите все ячейки, которые вы хотите превратить в даты.
• Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить».
• Введите свой пользовательский разделитель (запятую, к примеру) в поле Найти и косую черту в Заменить.
• Нажмите Заменить все.

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

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

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

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

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

Исправление записей с двузначными годами.

Современные версии Microsoft Excel достаточно умны, чтобы обнаружить некоторые очевидные ошибки в ваших данных, или, точнее, сказать, что Эксель считает ошибкой. Когда это произойдет, вы увидите индикатор ошибки (маленький зеленый треугольник) в верхнем левом углу клетки, и, когда вы выделите её, появится восклицательный знак.
При нажатии на восклицательный знак отобразятся несколько параметров, относящихся к вашим данным. В случае двухзначного года программа спросит, хотите ли вы преобразовать его в 19XX или 20XX.

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

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

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

1. ВРЕМЯ;
2. ДАТА;
3. ГОД;
4. МЕСЯЦ;
5. ДЕНЬ;
6. ЧАС;
7. МИНУТЫ;
8. СЕКУНДЫ.
9. ВРЕМЗНАЧ;
10. ДАТАЗНАЧ;
11. ДАТАМЕС;
12. РАБДЕНЬ;
13. ЧИСТРАБДНИ;
14. ДЕНЬНЕД;
15. НОМНЕДЕЛИ;
16. ТДАТА;
17. СЕГОДНЯ.

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

Возвращает значение времени, составленное из отдельных частей, заданных числами.
Синтаксис: =ВРЕМЯ(часы; минуты; секунды), где

• Часы – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 24.
• Минуты – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 60.
• Секунды – аналогично минутам.

Функция ВРЕМЯ не может возвращать значение от 24 и выше, т.е. если для аргумента «Часы» задать число 25, то результатом будет 1 час.

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

Функция ДАТА

Возвращает значение даты, составленное из отдельных частей, заданных числами.
Синтаксис: =ДАТА(год; месяц; день), где

• Год – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1900 до 9999.
• Месяц – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 12.
• День – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 31.

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

Пример использования:
Обратите внимание на пример таблицы. Последние 2 строки возвращают одинаковый результат. Т.к. минимальная дата, которая может быть представлена в Excel, имеет 1900 год, то ко всем числам, которые переданы аргументу «Год» и меньше минимального года, то к ним автоматически прибавляется 1900.

Функция ГОД

Возвращает число, представляющее год указанной даты.
Синтаксис: =ГОД(дата), где «дата» является обязательным аргументом и принимает числовое значение даты либо ссылку на такое значение.
Пример использования:
=ГОД(«08.10.2013») – возвращаемый результат 2013.
=ГОД(41555) – так же возвращаемый результат 2013, т.к. 41555 является числовым представление даты 08 октября 2013.

Функция МЕСЯЦ

Возвращает число, представляющее месяц указанной даты.
Синтаксис: =МЕСЯЦ(дата), где «дата» является обязательным аргументом и принимает числовое значение даты либо ссылку на такое значение.

Пример использования:
=МЕСЯЦ(«01.01.2001») – возвращаемый результат 1.
=МЕСЯЦ(36892) – так же возвращаемый результат 1, т.к. 36892 является числовым представление даты 01.01.2001.

Функция ДЕНЬ

Возвращает число, представляющее день указанной даты.
Синтаксис: =ДЕНЬ(дата), где «дата» является обязательным аргументом и принимает числовое значение даты либо ссылку на такое значение.

Пример использования:
=ДЕНЬ(«09.05.1945») – возвращаемый результат 9.
=ДЕНЬ(16566) – так же возвращаемый результат 9, т.к. 16566 является числовым представление даты 09.05.1945.

Функция ЧАС

Возвращает число от 0 до 23, представляющее час указанного времени или даты.
Синтаксис: =ЧАС(время), где «время» является обязательным аргументом и принимает числовое значение времени, даты либо ссылку на такое значение.

Пример использования:
=ЧАС(«14:34:50») – функция вернет значение 14.
=ЧАС(0,0241898148) – так же возвращает значение 14, так как 0,0241898148 является числовым представлением времени 14 часов 34 минуты 50 секунд.

Функция МИНУТЫ

Возвращает число от 0 до 59, представляющее минуты указанного времени или даты.
Синтаксис: =МИНУТЫ(время), где «время» является обязательным аргументом и принимает числовое значение времени, даты либо ссылку на такое значение.

Пример использования:
=МИНУТЫ(«22:45:00 «) – функция вернет значение 45.
=МИНУТЫ(0,428472222222222) – возвращает значение 17, так как 0,428472222222222 является числовым представлением времени 10:17:00.

Функция СЕКУНДЫ

Возвращает число от 0 до 59, представляющее секунды указанного времени или даты.
Синтаксис: =СЕКУНДЫ(время), где «время» является обязательным аргументом и принимает числовое значение времени, даты либо ссылку на такое значение.

Пример использования:
=СЕКУНДЫ(«07:23:32») – функция вернет значение 32.
=СЕКУНДЫ(0,999988425925926) – возвращает значение 59, так как 0,999988425925926 является числовым представлением времени 23:59:59.

Функция ВРЕМЗНАЧ

Преобразует время, заданное текстовой строкой, в его числовое значение. Распознаются только те форматы записи времени, которое доступны в меню формата ячеек.
Синтаксис: =ВРЕМЗНАЧ(текст), где «текст» — обязательный аргумент, принимающий строку времени либо ссылку на такую строку.

Пример использования:
На изображении приведены несколько вариантов использования функции ВРЕМЗНАЧ. Обратите внимание на первый столбец, в нем указываются все строки, которые были заданы для аргумента рассматриваемой функции. Все значения явно указаны строкой, кроме последней, поэтому функция ее не преобразует и возвращается ошибку. В четвертой строке указана дата и время, но функцией воспринимается только временное значение. Остальная часть игнорируется.

Функция ДАТАЗНАЧ

Преобразует дату, заданную текстовой строкой, в ее числовое значение. Распознаются только те форматы записи даты, которое доступны в меню формата ячеек.
Синтаксис: =ДАТАЗНАЧ(текст), где «текст» — обязательный аргумент, принимающий строку даты либо ссылку на такую строку.

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

Функция ДАТАМЕС

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

Синтаксис: =ДАТАМЕС(начальная_дата; число_месяцев), где

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

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

Пример использования:
=ДАТАМЕС(«01.01.2001»; 2) – функция возвращает результат 01.03.2001.
=ДАТАМЕС(«01.01.2001»; -2) – результат 01.11.2000.
=ДАТАМЕС(«01.01.2001 10:00:00»; 2) – функцией урезается время и возвращается результат 01.03.2001.

Функция КОНМЕСЯЦА

Возвращает последнюю дату месяца, который наступит либо наступил через указанное количество месяцев. Функция похожа на ДАТАМЕС, но возвращает не указанный день, а последний день высчитанного месяца.
Синтаксис: =КОНМЕСЯЦА(начальная_дата; число_месяцев), где

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

Если начальная дата указана вместе со временем, то оно усекается.

Пример использования:
=КОНМЕСЯЦА(«01.01.2001»; 0) – функция возвращает результат 31.01.2001.
=КОНМЕСЯЦА(«01.01.2001»; -2) – результат 30.11.2000.
=КОНМЕСЯЦА(«01.01.2001 10:00:00»; 2)– функцией урезается время и возвращается результат 31.03.2001.

Функция РАБДЕНЬ

Высчитывает и возвращает дату, которая наступит или наступила через указанное количество рабочих дней. Нерабочими днями считается суббота, воскресенье и праздничные дни.
Синтаксис: =РАБДЕНЬ(начальная_дата; число_дней; [праздники]), где

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

Пример использования:
=РАБДЕНЬ(«11.06.2013»;1) – возвращает результат 12.06.2013.
=РАБДЕНЬ(«11.06.2013»;4) – результат 17.06.2013.
=РАБДЕНЬ(«11.06.2013»;1; «12.06.2013») – функция вернет результат 13.06.2013.
=РАБДЕНЬ(«11.06.2013»;1; ) – результатом будет 14.06.2013.

Функция ЧИСТРАБДНИ

Возвращает количество рабочих дней между указанными датами (включительно). Нерабочими днями считается суббота, воскресенье и праздничные дни.
Синтаксис: =ЧИСТРАБДНИ(начальная_дата; конечная_дата; [праздники]), где

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

Пример использования:
=ЧИСТРАБДНИ(«11.06.2013″;»16.06.2013») – результат функции 4.
=ЧИСТРАБДНИ(«11.06.2013″;»16.06.2013″;»14.06.2013») – возвращаемый результат 3.

Функция ДЕНЬНЕД

Возвращает порядковый номер дня недели для указанной даты. Также можно указать тип нумерации дней недели.

Синтаксис: =ДЕНЬНЕД(дата; [тип]), где

• дата – обязательный аргумент, принимающий дату либо ссылку на дату для которой необходимо определить день недели.
• тип – необязательный аргумент. Целое число от 1 до 3, которое указывает формат подсчета:
  • 1 – неделя начинается с воскресенья. Является значением по умолчанию;
  • 2 – неделя начинается с понедельника;
  • 3 – неделя начинается с понедельника, а отсчет начинается с нуля.

  Пример использования:
  =ДЕНЬНЕД(«07.10.2013») – результат функции 2, т.к. аргумент тип не указан, а дата является понедельником.
  =ДЕНЬНЕД(«07.10.2013», 2) – результат выполнения функции 1.
  =ДЕНЬНЕД(«07.10.2013», 3) – результат 0.

  Функция НОМНЕДЕЛИ

  Возвращает порядковый номер недели в году.

  Синтаксис: =НОМНЕДЕЛИ(дата; [тип]) , где

  • дата – обязательный аргумент, принимающий дату либо ссылку на дату для которой необходимо определить день недели.
  • тип – необязательный аргумент. Целое число от 1 до 2, которое указывает формат недели:
    • 1 – неделя начинается с воскресенья. Является значением по умолчанию;
    • 2 – неделя начинается с понедельника;

    Пример использования:
    =НОМНЕДЕЛИ(«07.10.2013», 1) – возвращает результат 41.
    =НОМНЕДЕЛИ(«07.10.2013», 2) – возвращает 40.

    Функция ТДАТА

    Возвращает текущее (системное) дату и время.

    Синтаксис: =ТДАТА(). Функция не имеет аргументов.

    Функция СЕГОДНЯ

    Возвращает текущую дату без времени.

    Синтаксис: =СЕГОДНЯ(). Функция не имеет аргументов.

    • < Назад
    • Вперёд >
    • Тип данных дата и время в Excel
    • Логические функции Excel
    • Условия сравнения чисел и строк в Excel
    • Текстовые функции Excel
    • Массивы Excel
    • Математические функции Excel
    • Функции ссылок и массивов Excel
    • Статистические функций Excel
    • Функции ВПР2 ГПР2
    • Функция ПЕРТЕКСТ
    • Критерий Манна-Уитни
    • Подключение MySQL в Excel
    • Подключение Excel к SQL Server

    Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

    Комментарии

    Владимир, добрый день!

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

    Чтобы просто отключить автоматический пересчет нужно на вкладке ФОРМУЛЫ ленты Excel в параметрах вычислений (иконка в виде калькулятора) выбрать пункт ВРУЧНУЮ. Но это не решит проблему фиксации времени изменения ячейки, т.к. при пересчете отобразится текущее время.

    Как в excel перевести дату в месяц

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

    Пусть в ячейке В6 содердится дата 05.09.2016

    

    С помощью формулы =ТЕКСТ(B6;»ММММ») можно вывести полное название месяца с заглавной буквы в именительном падеже, Сентябрь. Чтобы вывести название со строчной буквы используйте функцию СТРОЧН() .

    Примечание: Результат предыдущей формулы — текстовое значение. Если нужно, чтобы в ячейке была дата, то используйте Формат ячеек (нажмите сочетание клавиш CTRL+1). О форматах даты подробнее можно прочитать в статье Пользовательский формат ДАТЫ и ВРЕМЕНИ в MS EXCEL

    Формула =ТЕКСТ(B6;»МММ») выведет сокращенное название месяца (3 буквы).

    Особый формат =ТЕКСТ(B6;»[$-FC19] ММММ») выведет полное название месяца с учетом склонения, т.е. сентября. Этот формат удобен для вывода фраз, например, Сегодня 1 сентября, но может работать не на всех компьютерах.

    Если месяц задан числом от 1 до 12, то полное название можно вывести с помощью формулы =ТЕКСТ(ДАТА(;B14;1);»ММММ») . Название со склонением можно вывести с помощью формулы =ВЫБОР(B14;»января»;»февраля»;»марта»;»апреля»;»мая»;»июня»;»июля»;»августа»;»сентября»;»октября»;»ноября»;»декабря») .

    Если требуется решить обратную задачу, то есть, имея название месяца, получить его номер, то используйте формулу =МЕСЯЦ(ДАТАЗНАЧ(1&B21&2011))

    Название месяца должно быть в именительном падеже и без опечаток.

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

    МЕСЯЦ (функция МЕСЯЦ)

    В этой статье описаны синтаксис формулы и использование функции МЕСЯЦ в Microsoft Excel.

    Возвращает месяц для даты, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь).

    Аргументы функции МЕСЯЦ описаны ниже.

    Дата_в_числовом_формате Обязательный аргумент. Дата месяца, который необходимо найти. Дата должна быть введена с использованием функции ДАТА либо как результат других формул или функций. Например, для указания даты 23 мая 2008 года следует воспользоваться выражением ДАТА(2008;5;23). Если даты вводятся как текст, это может привести к возникновению проблем.

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

    Значения, возвращаемые функциями ГОД, МЕСЯЦ и ДЕНЬ, соответствуют григорианскому календарю независимо от формата отображения для указанного значения даты. Например, если для формата отображения заданной даты используется календарь Хиджра, то значения, возвращаемые функциями ГОД, МЕСЯЦ и ДЕНЬ, будут представлять эквивалентную дату по григорианскому календарю.

    Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

    Как в Excel получить месяц из даты (функция ТЕКСТ и МЕСЯЦ)

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

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

    Способ 1. Получить месяц из даты с помощью функции МЕСЯЦ в Excel

    
    Протягиваем формулу и получаем месяц из даты в виде цифры. 5 — это месяц май, 8 — это август и так далее.

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

    Способ 2. Получить месяц из даты с помощью функции ТЕКСТ в Excel

    Синтаксис будет следующий

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

    

    Так например, чтобы получить месяц в формате «Январь, Февраль, Март», необходимо ввести «ММММ».
    Формул будет выглядеть следующим образом.

    Примеры функций для работы с датами: ГОД, МЕСЯЦ и ДЕНЬ в Excel

    В таблицах Excel предусмотрена возможность работы с различными видами текстовой и числовой информации. Доступна и обработка дат. При этом может возникнуть потребность вычленения из общего значения конкретного числа, например, года. Для этого существует отдельные функции: ГОД, МЕСЯЦ, ДЕНЬ и ДЕНЬНЕД.

    Примеры использования функций для обработки даты в Excel

    Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.

    Функция ГОД используется аналогично смежным:

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

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

    =ГОД(адрес ячейки с датой в числовом формате)

    Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.

    Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.

    Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.

    Введем исходные данные в Excel.

    

    Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:

    =ГОД (адрес ячейки, из даты которой нужно вычленить значение года)

    

    В результате мы извлекаем года из каждой даты.

    Аналогичный пример работы функции МЕСЯЦ в Excel:

    

    Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:

    

    Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:

    

    Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):

    

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

    Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:

    

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

    Примеры практического применения функций для работы с датами

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

    Допустим у нас имеется простой отчет по продажам:

    

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

    

    Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:

    

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

    

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

    Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:

    

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

    Как в Эксель написать после числа месяц текстом в правильной форме?

    В Экселе функция ТЕКСТ позволяет название месяца либо в сокращенной форме, либо в именительном падеже, а как написать текстом название месяца правильно?

    Если написать формулу: =ТЕКСТ(«01.04.2019» ; «Д ММММ»)

    то в результате будет написано: «1 Апрель»

    Как в Excel написать словами название месяца в правильной форме (например «1 апреля»)?

    Как в Экселе вывести после числа название месяца текстом в правильном падеже?

    Какой формулой в Microsoft Excel можно получить по дате название месяца прописью?

    В принципе можно составить громоздкую формулу, что-то типа

    Только тут легко запутаться в регистрах и в скобках. Даже редактор БВ не может правильно прожевать этот набор слов.

    Поэтому существует специальный вариант подобной формулы (в ячейке A1 — дата):

    Обратите внимание на середину формулы, именно такая запись возвращает название месяца в нужном склонении. Остальное — дело техники.

    Предлагаю такой вариант.

    Имеется дата 30.10.2019 в ячейке А1. Требуется сделать: « 30 » октября 2019 г.

    Полностью вся формула для данного значения будет иметь вид:

    Если нужно записать конкретно один месяц из определённой даты, тогда формула будет такой:

    Обратите внимание, месяца здесь написаны вручную и не являются постоянными значениями. Так что вместо них можно написать всё что вашей душе угодно. Суть этой формулы в том, что функция МЕСЯЦ(А1) возвращает номер месяца числом: от 1 до 12. Что является «номером_индекса» для функции ВЫБОР. Сами же значения (или можно задать действия) индексов прописываются следом, по порядку. Таким образом, если номер_индекса равен 1 (что соответствует январю), то функция ВЫБОР возвращает значение1 («января»); если он равен 2, возвращается значение2 («февраля») и так далее.

    Формула ТЕКСТ(ДЕНЬ(A1);»00″) добавляет 0 перед днём, который пишется одним числом (преобразует формат написания числа с 1 цифры до двух), таким образом цифры от 1 до 9 будут отображаться как 01, 02 . 09.