Excel

Работа с датами excel: Сложение или вычитание дат — Служба поддержки Майкрософт

Содержание

Работа с датами в Excel. Вычисление и преобразование.

Постановка задачи.

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

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

Получение номера месяца, года и т.д. из заданной даты.

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

=ДЕНЬ(«18.09.2018»)

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

Рисунок 1

Чтобы узнать номер дня недели, применяем функцию ДЕНЬНЕД. Для нее первой указывают обрабатываемую дату, а затем тип значения. Тип определяет порядок и нумерацию дней недели. Если его не указать, то считается, что неделя начинается с воскресенья, и счет дней недели идет с единицы. Если тип равен 3, то неделя начинается с понедельника, но номер дней идет с нуля. То есть в этом случае понедельник имеет номер 0. В наших условиях первый день – понедельник, счет дней начинается с единицы.

Поэтому используем тип, равный 2.

Рисунок 2

Так же можно указать тип числами от 11 до 17. В этом случае счет дней будет идти с 1, а первый день будет от понедельника(11) до воскресенья(17). То есть функция =ДЕНЬНЕД(“23.08.2017”;11) и функция =ДЕНЬНЕД(“23.08.2017”;2) дадут одинаковый результат, равный 3. Желающие могут открыть календарь и убедиться, что это действительно был третий день недели по принятому в бывшем СССР варианту, то есть среда.

Для нахождения номера квартала отдельной функции, к сожалению, нет. Однако можно воспользоваться такой формулой:

=ЦЕЛОЕ((МЕСЯЦ(D10)+2)/3)

D10 – это ячейка с исходной датой. Естественно, никто не мешает указать ее явным образом.

Рисунок 3

В данном случае 16 июля 2020 года действительно относится к третьему кварталу.

Если нужно найти номер недели, используют функцию со схожим названием НОМНЕДЕЛИ. В ней надо указать дату, для которой надо найти номер недели, а затем тип отчета.

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

  • Система 1    Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
  • Система 2    Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1. Эта методика определена в стандарте ISO 8601, который широко используется в Европе для нумерации недель

Используют следующие типы:

 1 или опущен –  Воскресенье, 1 система отчета;

 2 –  Понедельник, 1 система отчета;

11 – Понедельник, 1 система отчета;

12 –  Вторник, 1 система отчета;

13 –  Среда, 1 система отчета;

14 –  Четверг, 1 система отчета;

15 – Пятница, 1 система отчета;

16 – Суббота, 1 система отчета;

17 –  Воскресенье, 1 система отчета;

21 – Понедельник, 2 система отчета.

Примеры использования:

=НОМНЕДЕЛИ(“21.05.2018”;11) – результат будет 21, неделя начинается с понедельника.

=НОМНЕДЕЛИ(“21.05.2018”;17) результат будет 21, неделя начинается с воскресенья.

При необходимости найти номер недели в соответствии с форматом ISO применяют функцию

=НОМНЕДЕЛИ.ISO, для которой достаточно указать только дату.

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

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

Функция РАБДЕНЬ пишется так:

=РАБДЕНЬ(1;2;3)

1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!

2 – количество рабочих дней. Функция найдет дату последнего дня из указанного количества

3 – праздники. Показывают в виде диапазона, прописывают в виде массива в фигурных скобках. Если в рабочем периоде праздников нет, то не указывают. Либо указывают на диапазон с ними.

Для функции РАБДЕНЬ.МЕЖД используют такую запись:

= РАБДЕНЬ.МЕЖД(1;2;3;4)

1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!

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

3 – выходные дни. Excel предлагает на выбор числовые коды для выходных, но можно поступить проще. Надо указать дни недели в виде строки из единиц и нулей. Первый знак будет относиться к первому дню недели (у нас это понедельник, у кого-то воскресенье). Единицами указывается выходные дни. Например, если для стандартных выходных в субботу и воскресенье используется запись – «0000011». Не забываем про кавычки!

4 – праздничные дни

 Разберем такую пример. На выполнение работы по ремонту требуется согласно нормативу 15 рабочих дней. Какого числа работы должны быть закончены в Казахстане, если они начаты 22 апреля 2020 года при условии стандартных выходных или выходных по понедельникам. Праздничные дни Казахстана в мае 2020 года – 1 мая, 7 мая, 9 мая. Пи этом так как 9 мая выпало на субботу, поэтому согласно правилам и постановлению день отдыха перенесен на 8 мая.

Решения задачи следующее

Рисунок 4

Обратите внимание, что в функции РАБДЕНЬ.МЕЖД можно вообще убрать выходные, оставив только праздники и указов для параметра «выходные дни» строку их 7 нулей. В функции РАБДЕНЬ это невозможно, в ней выходные дни используются автоматически. И еще. Результатом работы обеих функций будет значение в числовом виде. Для преобразования их в формат дат воспользуйтесь форматом даты или примените к результату функцию ТЕКСТ, указав сначала полученное значение, а затем в кавычках нужный формат. Если нужно, чтобы дата выглядела как «число.номер месяца.номер года из 4 цифр», то есть вот так – 15.05.2020  -, тогда формат будет таким «ДД.ММ.ГГГГ».

Рисунок 5

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

Рисунок 6

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

Все вместе выглядит так:

Рисунок 7

Обратите внимание, что для этих функций преобразование в формат даты не требуется. Функцию ДАТА применяют и для нахождения даты через заданное количество недель. Надо только это количество умножить на 7, а результат прибавить к значению функции ДЕНЬ.

https://i.postimg.cc/4xxFrFKS/image9.jpg

Рисунок 8

Нахождение количества дней, месяцев, недель и т.д.  между двумя датами.

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

Для нахождения разницы в КАЛЕНДАРНЫХ днях достаточно от одной даты отнять другую. Если обе даты были изначально именно даты, или если обе даты изначально получены с помощью функций, то результат будет иметь числовой формат без знаков после запятой. Если же одно значение было датой, а второй получено с помощью функции, то результат будет получен в формате даты. В этом случае для него надо задать числовой формат. Если полученное значение поделить на 7, то получим разницу в неделях, которую можно потом округлить для нужной точности. В следующем примере получена разница в днях и неделях, причем недели округлены до полных в верхнюю сторону.

Рисунок 9

Если надо найти  количество РАБОЧИХ дней, то используем функции ЧИСТРАБДНИ и ЧИСТРАБДНИ.МЕЖД. Эти функции идентичны функциям РАБДЕНЬ и РАБДЕНЬ.МЕЖД по функционалу, то есть в первой мы применяем стандартные выходные, а во второй выходные указываем сами.

Синтаксис функции ЧИСТРАБДНИ

=ЧИСТРАБДНИ(1;2;3)

1 – начальная дата;

2 – конечная дата;

3 – праздничные дни.

Синтаксис функции ЧИСТРАБДНИ.МЕЖД

=ЧИСТРАБДНИ.МЕЖД(1;2;3;4)

1 – начальная дата;

2 – конечная дата;

3 – выходные дни. Указываются, как и в функции РАБДЕНЬ.МЕЖД;

4– праздничные дни.

Рисунок 10

Обратите внимание на следующую тонкость. Excel справедливо считает, что в первый день вы пришли на работу утром, а вот ушли вечером. Именно поэтому, если указать и начальную и конечную дату одинаково, то он все равно покажет, что один день отработан. Вот почему в приведенном примере мы видим разницу не в 15, а в 16 дней. Если же вы хотите увидеть разницу в рабочих СУТКАХ, тогда надо от результата этих функций отнять единицу.

 Кроме этого, для нахождения разницы между двумя датами применяют недокументированную функцию РАЗНДАТ.

=РАЗНДАТ(1;2;3)

1 – начальная дата

2 – конечная дата

3 – тип расчета, показывает, в каких единицах идет расчет.

Варианты типа:

 – «y» – количество полных лет;

– «m» – количество полных месяцев;

– «d» – количество полных лет;

– «ym» – полных месяцев, без учета лет;

– «yd» – дней, без учета лет;

– «md» – дней, без учета месяцев.

Рисунок 11

Подведем итоги

На нашем уроке мы научились использовать функции и формулы MS Excel для работы с датами. Применяйте их на практике, и вы увидите, как сократится время вашей работы. Желаю всем удачи. Если возникнут вопросы, пишите в комментариях.

Работа с датами в Power Query

Это фрагмент книги Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных.

Предыдущий раздел                   К содержанию                 Следующий раздел 

Начнем с преобразования текста в даты. При загрузке таблицы со значениями даты или даты/времени Power Query выполняет преобразование столбцов с учетом «правильного» формата.

Рис. 1. Некоторые значения дат Excel не распознал; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите

Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в формате архива (внутри файлы Excel с примерами; без макросов)

Загрузите файл C02E05. xlsx с сайта или из приложенного к заметке архива и сохраните его на диске. Файл содержит каталог товаров базы AdventureWorks. В столбце G указана дата выпуска. Представьте, что разные сотрудники вводили даты в пяти форматах:

  • 7/1/2018
  • 2018-07-01
  • 1.2018
  • Jul 1, 2018
  • 1 July, 2018

Откройте копию рабочей книги C02E05.xlsx. В ячейку h3 введите формулу: =G2+1. Измените формат ячейки Н2 на дату. Протяните формулу до H6 (см. рис. 1). Значения в Н2 и Н4 Excel распознал неверно, применив российские региональные настройки, действующие в Windows, к файлу, созданному на ПК с американскими настройками. Значения в Н5 и Н6 Excel не смог представить в формате даты.

Посмотрим, как с задачей справится Power Query. Создайте новую книгу Excel. Пройдите по меню

Данные –> Получить данные –> Из файла –> Из книги. Выберите C02E05.xlsx. Нажмите Импорт. В окне Навигатора выберите Sheet1, нажмите Преобразовать данные. Откроется окно редактора Power Query. Обратите внимание: PQ смог распознать все значения как даты, но не все корректно:

Рис. 2. Даты, распознанные PQ с настройками по умолчанию

Это связано с тем, что региональные настройки на вашем ПК отличаются от региональных настроек в файле C02E05.xlsx. Чтобы настроить импорт пройдите в редакторе PQ по меню Файл –> Параметры и настройки –> Параметры запроса. Перейдите на вкладку Региональные настройки. Установите Языковый стандарт – Английский (США):

Рис. 3. Изменение параметров запроса

Нажмите Ok. Обновите запрос: на вкладке

Главная редактора PQ кликните Обновить предварительный просмотр. Теперь даты распознаны верно. На вкладке Главная нажмите Закрыть и загрузить. Данные появятся на листе Excel.

Работа с датами при использовании двух локальных настроек

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

  • Country: USA, Release Date: 7/23/2018.
  • Country: United Kingdom, Release Date: 23/7/2018.

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

Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из файла –> Из книги. Выберите C02E06.xlsx. Нажмите Импорт. В окне

Навигатора выберите Products, нажмите Преобразовать данные. Откроется окно редактора. Выберите столбец Release Data. Пройдите по меню Преобразование –> Разделить столбец –> По разделителю. В окне Разделить столбец по разделителю в качестве разделителя уже выбрана косая черта /. Щелкните Ok.

Столбец Release Data разделиться на три столбца. Пройдите по меню Добавление столбца –> Настраиваемый столбец. В окне Настраиваемый столбец введите имя нового столбца – Date и формулу:

if [Country] = «US» then #date( [Release Date.3], [Release Date.1], [Release Date.2] ) else #date( [Release Date.3], [Release Date.2], [Release Date.1] )

1

2

3

4

5

6

if [Country] = «US» then #date(

[Release Date.3], [Release Date.1], [Release Date.2]

)

else #date(

[Release Date.3], [Release Date.2], [Release Date.1]

)

Рис. 4. Настраиваемый столбец

Кликните Ok. Измените тип столбца Date на Дата. Удалите столбцы Release Date.1, Release Data.2 и Release Data.3. На вкладке Главная нажмите Закрыть и загрузить. Данные появятся на листе Excel.

Рассмотрим альтернативный вариант. Продублируем текущий запрос для сохранения обеих версий. В окне Excel в области Запросы и подключения кликните правой кнопкой мыши на запрос Products. Выберите Изменить. Откроется окно редактора PQ. В левой части кликните правой кнопкой мыши на запрос Products. Выберите Дублировать. Создастся запрос Products (2). Перейдите на панель Примененные шаги и удалите последние три шага, начиная с Добавлен пользовательский объект

.

Нажмите и удерживайте клавишу Ctrl и выделите три столбца в следующем порядке: Release Date.2, Release Date.1 и Release Date.3. На вкладке Добавление столбца кликните Объединить столбцы. В окне Объединить столбцы в качестве разделителя задайте Пользовательский и введите косую черту (/). В поле Имя нового столбца введите US Date, кликните Ok.

Нажмите и удерживайте клавишу Ctrl и выделите три столбца дат в ином порядке: Release Date. 1, Release Date.2 и Release Date.3. Повторите процедуру, а новый столбец назовите UK Date.

На вкладке Добавление столбца выберите команду Условный столбец. В окне Добавление условного столбца установите:

Рис. 5. Настройка условного столбца

Кликните Ok. Измените формат столбца Date на Дата. Удалите столбцы Release Date.1, Release Date.2, Release Date.3, US Date и UK Date. Загрузите запрос на лист Excel. Даты отображаются в соответствии с локальными настройками Windows.

Выборка элементов даты и времени

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

Рис. 6. Меню Дата

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

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

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

Полное руководство по использованию дат и времени в Excel

Быстрая навигация

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

    Как Excel хранит даты

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

    Excel хранит даты в виде порядкового номера, представляющего количество дней, прошедших с начала даты. 1900 год. Это означает, что 1 января 1900 на самом деле просто 1 . 2 января 19:00 это 2 . К тому времени, когда мы дойдем до нынешнего десятилетия, числа станут довольно большими… 10 сентября 2013 г. хранится как 41527 .

    Важно отметить, что любая дата до 1 января 1900 года не распознается как дата в Excel. В числовой строке нет «отрицательных» порядковых номеров даты.

    Это кажется запутанным, но с ним намного проще складывать, вычитать и считать дни. Неделя с 10 сентября 2013 ( 17 сентября 2013 ), это просто 41527 + 7 дней, или 41534 .

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

    Excel хранит время, используя тот же формат порядковой нумерации, что и даты. День начинается в полночь (12:00 или 0:00 часов). Поскольку каждый час составляет 1/24 дня, он представлен следующим десятичным числом: 0,041666…

    Это означает, что 9:00 ( 09:00 часов ) 10 сентября 2013 г. будет храниться как 41527,375 .

    Если время указано без даты, Excel сохраняет его, как если бы оно произошло 0 января 1900 . Другими словами, 15:00 ( 15:00 часов ) хранится как 0,625 . Это может затруднить математические вычисления для значений времени (у которых нет даты), поскольку вычитание 6 часов ( 6:00 ) из 3:00 ( 03:00 часов ) станет отрицательным и будет считаться ошибка: 0,125 – 0,25 = -0,125 , который отображается как ######### .

    Минуты и секунды в Excel работают так же, как часы…

    Минута равна 1/60 часа, что составляет 1/24 дня, или 1/1440 дня в сумме , что вычисляется как 0,00069444…

    Секунда равна 1/60 минуты, что составляет 1/60 часа, что составляет 1/24 дня, или 1/0 всего дня, что составляет 0.00001157407…

    Работа с датами и временем

    DATE() и TIME()

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

     =  ДАТА  (  год  ,  месяц  ,  день  ) 
     =  ВРЕМЯ  (  часов  2 , 8  ,   секунд  )

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

     =  ДАТА  (2013,9,10)

    Он будет сохранен как 41527 , что означает, что технически он хранит 00:00 10 сентября 2013 г. .

    Для времени 18:00 ( 18:00 часов ) можно ввести как:

     =  ВРЕМЯ  (18,0,0)

    Он будет сохранен как 0,75 , что означает, что он технически хранит 18:00 0 января 19:00 .

    Если мы хотим представить конкретное время и дату, мы можем сложить две функции вместе. Например, 18:00 ( 18:00 часов ) 10 сентября 2013 г. можно ввести как:

     =  ДАТА  (2013,9,10)+  ВРЕМЯ  (18,0013 0)

    Это будет рассчитано как 41527,75 , что означает, что Excel хранит именно ту дату, которую мы хотим…

    Дополнительные функции установки даты и времени

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

    СЕГОДНЯ()

    Функция СЕГОДНЯ всегда возвращает порядковый номер текущей даты. Функция СЕГОДНЯ просто вводится как:

     =  СЕГОДНЯ  ()

    Функция рассчитана до 41541 . Это означает, что технически он хранит 00:00 24 сентября 2013 года .

    NOW()

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

     =  Сейчас  ()

    , 18:30 ( 18:30 ) на сентября 24, 2, функция рассчитывается на 41541.77770833333333333333333333333333333 годы, функция, рассчитанная на 41541.777777708333333333333333333333333333333333333333333333333333333333333. … СЕЙЧАС хранит точное время и дату с точностью до секунды.

    СДАТА() и КОНМЕСЯЦА()

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

     =  EDATE  (  start_date  ,  месяцев  ) 
     =  КОНМЕСЯЦА  (  start_date  ,  месяцев  )

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

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

    Функции ЭДАТА и КОНМЕСЯЦА удаляют значение времени из даты. Например, например, если в ячейке A1 хранится 10 сентября 2013 г. , мы можем получить значение на 2 месяца вперед следующим образом:

     =  EDATE  (  A1  ,2)

    Возвращает 00:00 ( 0:00 часов 90 3 0 13 ) 110012 или 41588 . Эта функция работает, даже если в месяцах разное количество дней (в сентябре и ноябре 30, в октябре 31).

     =  КОНМЕСЯЦА  (  A1  ,2)

    Возврат 00:00 ( 0:00 часов ) 30 ноября 2013 г. или  41588 . Опять же, эта функция работает, несмотря на то, что в месяцах разное количество дней.

    РАБДЕНЬ()

    Иногда может быть полезно считать вперед по рабочим дням (с понедельника по пятницу), а не по всем 7 дням недели… Для этого в Excel предусмотрено РАБДЕНЬ . Синтаксис для РАБДЕНЬ следующий:

     =  РАБДЕНЬ  (  start_date  ,  days  ,  [праздники]  )

    start_date0029 как указано выше.

    Ввод days — это количество рабочих дней впереди (или позади) текущего дня, которое вы хотите переместить.

    Ввод [праздники] является необязательным, но позволяет исключить определенные дни (например, День Благодарения или Рождество), которые в противном случае могли бы приходиться на рабочую неделю. Это порядковые номера дат, представленные в массиве, заключенном в квадратные скобки: { }. Чтобы указать несколько праздников, даты должны быть указаны в ячейках — нельзя поставить несколько ДАТА функций в массиве.

    Например, давайте найдем дату за 6 рабочих дней до 18:00 ( 18:00 часов ) 10 сентября 2013 г. (хранится в ячейке A1). Понедельник, 2 сентября – День труда, поэтому давайте отметим его как праздник:

     =  РАБДЕНЬ  (  A1  ,-6,  ДАТА  (2013,9,2))

    Возврат 00:00  ( 0:00 часов ) 30 августа 2013 г. или 41516 . (Обратите внимание, что функция удаляет временную часть даты.)

    РАБДЕНЬ.МЕЖД() (Excel 2010 и новее)

    Для более новых версий Excel (2010 и новее) существует другая версия РАБДЕНЬ под названием РАБДЕНЬ.МЕЖД . WORKDAY.INTL работает так же, как WORKDAY , но добавляет возможность настройки определения «выходных». Синтаксис для РАБДЕНЬ.МЕЖД следующий:

     =  РАБДЕНЬ.МЕЖД  (  start_date  ,  дней  ,  [выходные]  ,  [праздники]  )

    Вводы start_date , days и [праздники] работают так же, как и обычная функция РАБДЕНЬ .

    Ввод [выходные] имеет следующие параметры:

    Получение дат в Excel

    ДЕНЬ(), МЕСЯЦ() и ГОД()

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

     =  день  ( Serial_number ) 
     =  месяц  ( Serial_number ) 
     =  год  ( Serial_number ). Например, если в ячейке A1 хранится  10 сентября 2013 г.  , мы можем использовать каждую из формул по очереди: 
     
     =  ДЕНЬ  (  A1  ) 
     Возвращает  10  в виде числового значения.
     =  МЕСЯЦ  (  A1  ) 
     Возвращает  9   в виде числового значения.
     =  ГОД  (  A1  ) 
     Возвращает  2013   в виде числового значения.
     Мы могли бы также указать прямой серийный номер для  10 сентября 2013 г.  :
     =  ДЕНЬ  (  41527  ) 
     Возвращает  10  числовое значение.
     Получение времени в Excel 
     ЧАС(), МИНУТА() и СЕКУНДА() 
     Для времени процесс очень похож. В Excel есть функция извлечения часов, минут и секунд из метки времени, удобно названной  HOUR  ,  MINUTE  и  SECOND  . The syntax is identical:
     =  HOUR  (  serial_number  ) 
     =  MINUTE  (  serial_number  ) 
     =  SECOND  (  serial_number  ) 
     The   serial_number   in each can be any time /ссылка на ячейку в формате даты. Например, если  A1  хранит  18:15:30  (  18:15 часов, 30 секунд  ) в  10 сентября 2013  , мы можем использовать каждую из формул по очереди: )

    Возвращает 18  в виде числового значения.

     =  МИНУТА  (  A1  )

    Возвращает 15  в виде числового значения.

     =  СЕКУНД  (  A1  )

    Возвращает 30  в виде числового значения.

    We could have also given the direct serial number for   6:15:30pm  ( 18:15 hours, 30 seconds ) on  September 10, 2013 :

     =  SECOND  (  41527.7607638889  )

    Возвращает 30 в виде числового значения.

    Дополнительные функции получения даты

    WEEKDAY() и WEEKNUM()

    Даты содержат не только информацию о месяце и году. Они также кодируют косвенную информацию… 10 сентября 2013 г. также является вторником . В Excel есть несколько функций для работы с недельным аспектом дат: WEEKDAY и WEEKNUM . Синтаксис выглядит следующим образом:

     =  Weekday  ( Serial_Number ,  [return_type] ) 
     =  Weeknum  ( Serial_Number ,  [return_typ]  2            9002. любая ссылка на ячейку в формате даты. Начиная с  [тип_возврата]  не является обязательным, каждая функция предполагает, что каждая неделя начинается с  воскресенья . Если ячейка  A1  Магазины  10 сентября 2013 г.  (A  вторник ), мы можем использовать каждую из формул в свою очередь: 
     
     =  Weekday  ( A1 ) 
     Возврат  3 , с  вторника ).  — это 3-й день недели, начинающийся  воскресенья .
     =  WEEKNUM  ( A1 ) 
     Возвращает  37 , с  10 сентября 2013 г.   находится на 37-й неделе  2013  , если считать недели с  воскресенья  .
     Параметр  [return_type]  позволяет указать другой порядок недель по умолчанию. Например, вы можете начать неделю с  понедельника  и продолжать до  воскресенья  или с  субботы  до  пятницы  . Однако Excel раздражает и делает разные записи для  WEEKDAY  и  WEEKNUM  . Полный список опций для  WEEKDAY  выглядит следующим образом:
     Варианты 2 и 11 функционально одинаковы — первый предназначен только для обратной совместимости с более ранними версиями Excel.
     Полный список параметров для  WEEKNUM  выглядит следующим образом:
     Подсчет и отслеживание дат 
     Даты можно складывать и вычитать как обычные числа, поскольку они хранятся в виде порядковых номеров. Это позволяет вам считать дни между двумя разными датами. Иногда, однако, вам нужно считать по другой метрике.
     ЧИСТРАБДНИ() 
     Выше мы узнали о  РАБДЕНЬ  , который позволяет перемещаться вперед и назад на заданное количество рабочих дней, игнорируя выходные и праздничные дни. Но что, если вам нужно измерить количество рабочих дней между двумя датами? Для этого Excel предоставляет  ЧИСТРАБДНИ  . Синтаксис формулы следующий:
     =  ЧИСТРАБДНИ  (  start_date  ,  end_date  ,  [праздники]  ) 
      start_date  и  end_date   может быть любой ссылкой на ячейку в формате даты.
     Ввод  [праздники]   необязателен, но позволяет исключить определенные дни (например, День Благодарения или Рождество), которые в противном случае могли бы приходиться на рабочую неделю. Это порядковые номера дат, представленные в массиве, заключенном в квадратные скобки: { }. Чтобы указать несколько праздников, даты должны храниться в ячейках — невозможно поместить несколько функций  ДАТА  в массив.
     Например, если  A1  содержит  18:00  ( 18:00 часов )  10 сентября 2013 г.  и  B1  содержит  9:00  ( 9:00 часов ) на  декабрь 2,  , ). мы можем использовать  ЧИСТРАБДНИ , чтобы найти количество рабочих дней между двумя датами.
     Исключим из праздничных дней День Колумба (14 октября 2013 г.), День ветеранов (11 ноября 2013 г.) и День благодарения (28 ноября 2013 г.). Для этого мы должны хранить эти даты в других ячейках. Ставим их на  C1 ,  C2  и  C3 :
     =  Дата  (2013,10,14) 
     =  Дата  (2013,11,11) 
     =  Дата  (2013,11,11, 
     =  Дата  (2013,11, 11122 
     =  Дата 28) 
     Теперь мы можем объединить их в функцию:
     =  ЧИСТРАБДНИ  (  A1  ,  B1  ,  C1:C3  ) 
    americ 70
     Функция возвращает 
     010002 значение 90.
     ЧИСТРАБДНИ.МЕЖД() (Excel 2010 и новее) 
     Для более новых версий Excel (2010 и новее) существует другая версия  NETWORKDAYS   по телефону  NETWORKDAYS.INTL  .  NETWORKDAYS.INTL   работает так же, как  NETWORKDAYS  , но добавляет возможность настраивать определение «выходных». Синтаксис для  NetworkDays.intl  выглядит следующим образом:
     =  NetworkDays.intl  ( start_date ,  END_DATE ,  [Weekend] ,  [Holidays] ) 2 
     8.dation 88288888888888888128 гг.  и   [праздники]   работает так же, как и обычная функция  РАБДЕНЬ   . 
     
     Входные данные  [выходные]   имеют следующие параметры: 
     
     
     
     YEARFRAC() 
     
     Иногда бывает полезно измерить, сколько времени прошло в годах, но вычитание функции  YEAR  округляет в меньшую сторону только до ближайшего полный год.   YEARFRAC  берет две даты и предоставляет часть года между ними. Синтаксис следующий: 
     
     =  YEARFRAC  (  start_date  ,  end_date  ,  [базис]  ) 
      start_date  и  end_date  могут быть любой ссылкой на ячейку в формате даты.
     Ввод  [базис]  не является обязательным, но позволяет указать «правила» измерения разницы. В большинстве случаев лучше использовать вариант 1, но вот полный список:
     Например, если  A1  содержит  10 сентября 2013 г.  и  B1  содержит  2 декабря 2013 г. , мы можем использовать  год.
     DATEDIF() (недокументированная функция) 
     Функция  YEARFRAC  дает вам разницу между датами в виде доли года, но иногда вам нужно больше контроля. В Excel есть мощная скрытая функция под названием 9.0012 DATEDIF , который может делать гораздо больше. Он может сказать вам количество лет, месяцев или дней между двумя датами. Он также может отслеживать только частичные входные данные, игнорируя годы или месяцы при расчете дней. Синтаксис для  датирует  следующим образом:
     =  датирует  ( start_date ,  END_DATE ,  UNIT ) 
    .
     Блок    ввод просит вас указать строку, которая представляет тип вывода, который вы хотите. Это немного громоздко, так как вы должны заключать ввод в кавычки ("").
    . DATEDIF  (  A1  ,  B1  ,"Y") 
     Возвращает  1  как числовое значение.
     Используя одни и те же входные данные  start_date  и  end_date , вот возможности вывода для  DATEDIF   используя другие параметры  unit :
     "или "МД").
     Преобразование дат и времени из текста 
     ЗНАЧ ДАТ() и ЗНАЧ ВРЕМЕНИ() 
     Все вышеперечисленные функции отлично работают с серийными номерами в формате даты в Excel. К сожалению, даты и время часто импортируются в рабочие листы в виде текста. Большинство разнообразных функций, таких как  МЕСЯЦ  и  ЧАС  достаточно интеллектуальны для преобразования на лету. Иногда бывает полезно создать значение даты с помощью конкатенации. Для этой цели в Excel предусмотрены две функции:  DATEVALUE  и  TIMEVALUE  . Синтаксис каждого из них следующий:
     =  ДАТАЗНАЧ  (  текст_даты  ) 
     =  ЗНАЧ ВРЕМЕНИ  (  текст_времени  ) 
    принять любую текстовую строку, которая выглядит как дата или время.
     Вот как  DateValue  отвечает на различные  DATE_TEXT  Входы:
     Именно так  TimeValue  отвечает на различные  Time_text  Вход:
     Curningting Dates и Time To Text 
    0505.1050510505050505050505050505051050505159 10000     .10505.10505059 10505059  (
    .
     Преобразование данных в дату и время — это прекрасно, но вам также может понадобиться получить их обратно. Иногда нужен особый формат. В других случаях вам нужно искать дату в текстовой строке и сопоставлять ее с помощью строковых инструментов, таких как  НАЙТИ  и  ПОИСК  . Существует одна основная функция для преобразования даты и времени в текстовые строки в Excel, которая называется  TEXT  . Синтаксис для  ТЕКСТ  следующий:
     =  ТЕКСТ  (  значение  ,  формат_текст  ) 
     Значение    может быть любой ссылкой на ячейку в формате даты или времени.
     Ввод format_text имеет большое количество опций, обобщенных здесь:
     Выходные данные можно комбинировать с простыми символами форматирования для создания стандартных форматов даты. Использование даты  5:07:03am  (  05:07 часов, 3 секунды  ) on  10 сентября 2013  , вот примеры возможных результатов:
     Общая проблема Excel иногда ошибочно интерпретирует текстовые поля как даты.
     Пример здесь:
     Будьте внимательны при вводе дат, особенно если вы импортируете данные из других источников, чтобы убедиться, что ваше «13 января» хранится как 1 января 2013 г., а не 13 января 2013 г.!
     Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel. Excel Tactics призван помочь вам освоить его.
     Подпишитесь на информационный бюллетень, чтобы быть в курсе последних статей. Зарегистрируйтесь, и вы получите бесплатное руководство с 10 быстрыми клавишами для экономии времени!
     Другие сообщения из этой серии... 
     Функция ДАТАЗНАЧ — служба поддержки Майкрософт 
     Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Больше...Меньше
     В этой статье описаны синтаксис формулы и использование функции  ДАТАЗНАЧ  в Microsoft Excel.
     Описание 
     Функция  ДАТАЗНАЧ  преобразует дату, хранящуюся в виде текста, в порядковый номер, который Excel распознает как дату. Например, формула  =ДАТАЗНАЧ("1/1/2008")  возвращает 39448, порядковый номер даты 1/1/2008. Помните, однако, что установка системной даты вашего компьютера может привести к тому, что результаты функции  DATEVALUE  будут отличаться от результатов, приведенных в этом примере
    .
     Функция  ДАТАЗНАЧ  полезна в тех случаях, когда рабочий лист содержит даты в текстовом формате, которые вы хотите отфильтровать, отсортировать или отформатировать как даты или использовать в вычислениях дат.
     Чтобы просмотреть порядковый номер даты как дату, необходимо применить к ячейке формат даты. Найдите ссылки на дополнительную информацию об отображении чисел в виде дат в  См. также раздел .
     Синтаксис 
     ДАТАЗНАЧ(дата_текст)
     Синтаксис функции ДАТАЗНАЧ имеет следующие аргументы:
    •  org/ListItem">
        Date_text      Обязательный. Текст, представляющий дату в формате даты Excel, или ссылка на ячейку, содержащую текст, представляющий дату в формате даты Excel. Например, «30.01.2008» или «30 января 2008» — это текстовые строки в кавычках, представляющие даты.
       При использовании системы дат по умолчанию в Microsoft Excel для Windows аргумент   date_text   должен представлять дату между 1 января 1900 г. и 31 декабря 9999 г. Функция  ДАТАЗНАЧ  возвращает #ЗНАЧ! значение ошибки, если значение аргумента   date_text   выходит за пределы этого диапазона.
       Если часть года аргумента   date_text   опущена,  Функция DATEVALUE  использует текущий год со встроенных часов вашего компьютера. Информация о времени в аргументе   date_text   игнорируется.
     Примечания 
    •  org/ListItem">
       Excel хранит даты в виде последовательных порядковых номеров, чтобы их можно было использовать в вычислениях. По умолчанию 1 января 1900 года — это серийный номер 1, а 1 января 2008 года — это серийный номер 39448, потому что это 39.447 дней после 1 января 1900 г.
    •  Большинство функций автоматически преобразуют значения даты в порядковые номера.
     Пример 
     Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.
      Данные 
     11
     3
     2011
      Формула 
      Описание 
      Результат 
     =ДАТАЗНАЧ("22. 08.2011")
     Серийный номер даты, введенной в виде текста.
     40777
     =ДАТАЗНАЧ("22 МАЯ 2011")
     Серийный номер даты, введенной в виде текста.
     40685
     =ДАТАЗНАЧ("23.02.2011")
     Серийный номер даты, введенной в виде текста.
     40597
     =ДАТАЗНАЧ("5 ИЮЛЯ")
     Серийный номер даты, введенный в виде текста с использованием 1900, и предполагается, что встроенные часы компьютера возвращают 2011 год как текущий год.
    Добавить комментарий 
    Ваш адрес email не будет опубликован. Обязательные поля помечены *