Svinkovod.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Способы добавление и вычисление дней из даты

Способы добавление и вычисление дней из даты

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

Уровень мастерства: Начинающий

Как добавить дни к дате?

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

How to Add Days to Existing Dates in Excel

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

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

Метод № 1: Используйте формулу

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

В Excel даты на самом деле являются числами, отформатированными как дата. Если вы измените форматирование числа ячейки, содержащей дату, на Общее или Число, вы увидите число от 1 до 40 000+. Это количество дней, прошедших с 1 января 1900 года, дня, когда календарь начинается в Excel.

Ознакомьтесь с моей статьей и видео о том, как система дат работает в Excel, чтобы узнать больше об этом.

Excel Dates Are Stored as Serial Numbers and Formatted as Dates

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

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

Formula to Add Days to Existing Date Value in Exce

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

Метод № 2: Специальная вставка

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

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

Paste Special to Add or Subtract Days to Each Date Cell

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

  1. Введите количество дней, которое вы хотите добавить или вычесть в пустой ячейке. Для этого примера я ввел 1 в ячейку C2.
  2. Скопируйте ячейку (щелкните правой кнопкой мыши> Копировать или Ctrl + C).
  3. Выберите ячейки, которые содержат даты.
  4. Щелкните правой кнопкой мыши и выберите «Специальная вставка» (сочетание клавиш: Alt, E, S).
  5. Выберите переключатель «Значения» в разделе «Вставка» в меню «Специальная вставка» (сочетание клавиш: V).
  6. Выберите переключатель «Добавить» в разделе «Операция» (сочетание клавиш: D).
  7. Нажмите OK или нажмите Enter.

Скопированное число (1) будет добавлено к значениям в каждой ячейке.

Полный набор клавиш для вставки Special> Add> Values:

Alt, E, S, V, D, Enter (нажмите и отпустите каждую клавишу по порядку)

Как специальная вставка работает?

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

Если ячейка содержит формулу, то формула будет изменена, и +1 будет добавлен в конец каждой формулы. Я объясняю это более подробно в видео выше.

Читайте так же:
Инструкция по прошивке bios

Paste Special Operation Modifies Existing Formulas

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

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

Метод № 3: Макрос VBA

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

Вот несколько простых примеров кода макроса VBA, которые добавляют или вычитают дни к активной (выбранной) ячейке.

Если вы хотите изменить даты в нескольких ячейках, то мы можем использовать цикл For Next Loop для циклического прохождения диапазона и изменения каждой ячейки.

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

Assign Macros to Shapes to Call the Add or Subtract Day to Dates VBA Macro

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

Другие методы для добавления дней к дате?

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

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

Часто пользователи Excel сталкиваются с необходимостью достать из ячейки формата «Дата» или «Время» по отдельности: Год, Месяц, Номер недели, День месяца, Час, Минуту. Такая задача решается с помощью функций.

Допустим в ячейке В2 у нас хранится дата 4 июня 2019 г. Получим из нее Год в другой ячейке D2:

  1. Поставить курсор в ячейку D2.
  2. Кликнуть на значке формулы .
  3. В появившемся окне «Вставка функции» в поле «Категория» выбрать Дата и время, а в поле «Выберите функцию» выделить ГОД. Нажать [OK].

Теперь в ячейке D2 будет храниться значение 2019. При изменении года в дате в ячейке В2 автоматически изменится год в ячейке D2.
Аналогичным образом поступать в случаях, когда из ячейки B2 необходимо вычленить месяц, номер недели с начала года или день месяца. Соответственно в п.3 надо будет выбирать функцию не ГОД, а МЕСЯЦ, НОМНЕДЕЛИ или ДЕНЬ.

Если в ячейке хранится также время или только время, то для выноса часов или минут в отдельную ячейку в п.3 надо выбирать ЧАС или МИНУТЫ.

Вычисление разницы между датами

Для такой задачи существует математическая операция «–» (минус), а также функция РАЗНДАТ (в английской версии — DATEDIF).

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

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

=РАЗНДАТ(Начальная_дата; Конечная_дата; Единица_измерения)

Начальная_дата – это меньшая дата;
Конечная_дата – это бОльшая дата;
Единица_измерения – этот аргумент определяет в каких временных единицах выводить разницу дат:

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

Вычисление разницы во времени

Для такой задачи существует математическая операция «–» (минус), а также функции ЧАС, МИНУТЫ, СЕКУНДЫ, применяемые к разнице значений ячеек.

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

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

Практические примеры

1. Проверка превышения запланированного времени доставки груза

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

Создадим столбец E, в который будем выводить разницу между Разгрузкой и Загрузкой в формате времени в ячейке E2:

В ячейке F2 напишем формулу, в которой будет задействована функция ЕСЛИ:

В формуле мы видим такую логику:

  • Берется фактическая разница между Разгрузкой и Загрузкой;
  • Эта Разница сравнивается с Нормой;
  • Если Разница больше Нормы, то пишется «Опоздание»;
  • Если Разница меньше или равна Норме, то пишется «Норма».

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

то формула в каждой последующей ячейке автоматически будет интеллектуально преобразована в формулу с релевантными строке адресами ячеек. Аналогичным образов поступаем с ячейкой E2 – размножает ее по столбцу E.

2. Красивое представление даты и времени в одной ячейке

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

В ячейке E9 пишем красивую формулу:

=СЦЕПИТЬ(ТЕКСТ(C9-B9;»дд»); » дн. «; ТЕКСТ(C9-B9;»чч»); » час. «; ТЕКСТ(C9-B9;»мм»); » мин.»)

Здесь функция ТЕКСТ переводит дату в число дней, или часов, или минут. Во что преобразовывать, зависит от второго параметра функции – «дд», «чч» и «мм» соответственно. А функция СЦЕПИТЬ склеивает наши числа и тексты в одну строку.
После размножения формулы по столбцу E получаем красивый результат:

Маленькие хитрости

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

Чтобы внеси в ячейку номер дня недели надо воспользоваться функцией ДЕНЬНЕД, для которой есть дополнительный параметр «Тип» – он влияет на начало отсчета номера дня недели. У нас принято начинать отсчет с понедельника, этому типу соответствует значение 2. Таким образом, функция по отображению в какой-то ячейке дня недели из даты 4 июня 2019 г., хранящейся в ячейке B2 будет выглядеть так: =ДЕНЬНЕД(B2;2)
Результатом срабатывания формулы будет 2, т.е., вторник.

Работа с датами

Разработчики Microsoft Excel создали дату для отчета 1.01.1900 год. Даты раннего периода в Excel не будут отображаться как даты, они будут просто цифры.
Быстрый ввод сегодняшней даты производиться нажатием горячих клавиш (Ctrl+Shift+4) или (Ctrl +;) комбинация клавиш зависит от установленного языка Вашей операционной системы. Microsoft
Для изменения отображения формата даты, нужно щелкнуть по дате правой клавишей мыши и выбрать Format Cells, в открывшемся, диалоговом окне в закладке Number надо выбрать Date.

Формат даты

С права появятся варианты отображения даты.

Если же Вы присвоите дате значение General или Number, то Ваша дата превратиться в цифровой код.
Например: 1.01.1900 = 1, 2.01.1900 = 2 – то есть цифра отображает порядковый номер даты + или – день с начала 1.01.1900. Следовательно, дата 12.12.2012 = 41255, что показывает количество дней.

Excel позволяет заполнять даты по различным параметрам:

  • Заполнить
  • Заполнить только форматы
  • Заполнить только значения
  • Заполнить по дням
  • Заполнить по рабочим дням
  • Заполнить по месяцам
  • Заполнить по годам

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

Заполнение дат

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

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

Плюс 100 дней

Щелкаем в ячейке где нужно отобразить результат и пишем обычную формулу сложения (=А2+100), где А2 – это ячейка с начальной датой, а 100 количество дней.

Например: нужно подсчитать количество дней до нового года от 20.05.2012 до 31.12.2012 для этого происходит обычное вычитание.

Сколько осталось дней до нового года

Щелкаем в ячейку где надо получить результат и пишем формулу (=А6-А5), где А6 – это дата нового года, а А5 сегодняшнее число.

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

Рабочие дни до нового года

Щелкаем на ячейку где нужно получить результат и нажимаем кнопку функций FX, в открывшемся диалоговом окне выбираем функции для дат и ищем там функцию NetWorkDays (только рабочие дни) после чего указываем диапазон ячеек, в которых искать результат.

Трюк №38. Трюки с возможностями даты и времени в Excel

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

По умолчанию в Excel используется система дат 1900. Это означает, что числовое значение, лежащее в основе даты 1 января 1900 года, равно 1, у 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями, позволяющими использовать даты в вычислениях. Формат времени очень похож, но Excel обрабатывает время как десятичные доли, где 1 — это 24:00, или 00:00. Числовое значение 18:00 (в русской версии) равно 0,75, так как это три четверти от 24 часов.

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

При помощи функции СУММ (SUM) или просто знака плюс время можно складывать. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне А1 :А5, если эти ячейки содержат допустимые значения времени. Однако если Excel не дано никаких специальных указаний, он не складывает время за пределами 24 часов. Это происходит потому, что, когда значение времени превышает 24 часа (настоящее значение равно 1), оно переходит в новый день и отсчет начинается заново. Чтобы заставить Excel не переходить в новый день после каждых 24 часов, можно использовать формат ячеек 37:30:55 или пользовательский формат [ч]:MM:cc([h]:mm:ss).

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

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

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5 .

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24 .

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1) , в русской версии Excel =ЦЕЛОЕ(А1) .

Чтобы получить только время: =A1-INT(A1) , в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1) , в русской версии Excel =OCTAT(A1;1) . И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;»d») , где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),»d») , в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);»d») .

При работе со временем может также понадобиться учитывать начальное и конечное время. Например, начальное время — это 8:50 p.m. в ячейке А1, а конечное время — 9:50 a.m. в ячейке А2. Если вы вычтете начальное время из конечного ( =А2-А1 ), получите в ответе ######, так как Excel по умолчанию не работает с отрицательными значениями времени. Подробнее о том, как работать с отрицательными значениями времени, — в разделе «Трюк № 74. Отображение отрицательных значений времени».

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2) , в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1) , в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1) .

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3) , в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3) .

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD) , в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)) .

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak. Выберите команду Файл → Надстройки (File → Add-Ins) и установите флажок Пакет анализа (Analysis ToolPak) и, если появится сообщение с вопросом, нужно ли установить эту надстройку, ответьте согласием. Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

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

Чтобы исправить эту ошибку, сначала скопируйте любую пустую ячейку, затем выделите столбец и отформатируйте его, выбрав любой формат даты или времени. Не снимая выделение столбца, выберите команду Главная → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время. Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector