Сложить или вычесть даты в Excel для Mac
Допустим, вам нужно добавить две недели к дате окончания проекта или определить продолжительность отдельной задачи в список задач. Простая формула или функция листа, предназначенная специально для работы с датами, позволит вам добавить к дате нужное количество дней, месяцев и лет или вычесть их из даты.
Добавление и вычитание дней из даты
Допустим, что выплата средств со счета производится 8 февраля 2012 г. Необходимо перевести средства на счет, чтобы они поступили за 15 календарных дней до заданного срока. Кроме того, известно, что платежный цикл счета составляет 30 дней, и необходимо определить, когда следует перевести средства для платежа в марте 2012 г., чтобы они поступили за 15 дней до этой даты. Для этого выполните указанные ниже действия.
- Откройте новый лист в книге.
- В ячейке A1 введите 08.02.2012.
- В ячейке B1 введите =A1-15 и нажмите клавишу RETURN. Эта формула вычитает 15 дней из даты в ячейке A1.
- В ячейке C1 введите =A1+30 и нажмите клавишу RETURN. Эта формула добавляет 30 дней к дате в ячейке A1.
- В ячейке D1 введите =C1-15 и нажмите клавишу RETURN. Эта формула вычитает 15 дней из даты в ячейке C1. В ячейках A1 и C1 даты вычета счетов за февраль и март (08.02.2012 и 09.03.2012). Ячейки B1 и D1 показывают даты (24.01.2012 и 23.02.12), на которые следует перевести средства, чтобы они поступают за 15 календарных дней до срока.
Добавление и вычитание месяцев из даты
Допустим, вам нужно прибавить к дате определенное количество полных месяцев или вычесть их из нее. Быстро сделать это вам поможет функция ДАТАМЕС.
В функции ДАТАМЕС используются два значения (аргумента): начальная дата и количество месяцев, которые нужно добавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента, например =ДАТАМЕС(«15.02.2012»;-5). Эта формула вычитает 5 месяцев из даты 15.02.2012, и ее результатом будет дата 15.09.2011.
Значение начальной даты можно указать с помощью ссылки на ячейку, содержащую дату, или ввести дату в кавычках, например «15.02.2012».
Предположим, нужно добавить 16 месяцев к дате 16 октября 2012 г.
- В ячейке A5 введите 16.10.2012.
- В ячейке B5 введите =ДАТАМЕС(A5;16) и нажмите клавишу RETURN. Функция использует значение из ячейки A5 в качестве начальной даты.
- В ячейке C5 введите =ДАТАМЕС(«16.10.2012»;16) и нажмите клавишу RETURN. В этом случае функция использует введенное значение даты («16.10.2012»). В обеих ячейках B5 и C5 отображается дата 16.02.2014. Почему в результатах отображаются цифры, а не даты? В зависимости от формата ячеек, содержащих формулы, которые вы ввели, результаты Excel отображаться как порядковые номера. В этом случае 16.02.14 может отображаться как 41686. Если результаты отображаются в виде серийных номеров, измените формат с помощью указанных здесь действий.
- Выделите ячейки B5 и C5.
- На вкладке Главная в группе Формат выберите элемент Формат ячеек, а затем — элемент Дата. Числовые значения в ячейках должны отобразиться в виде дат.
Добавление и вычитание лет из даты
Допустим, вам нужно прибавить определенное количество лет к определенным датам или вычесть их из этих дат. Соответствующие действия описаны в приведенной ниже таблице.
Количество прибавляемых или вычитаемых лет
- На новом листе введите дату 09.06.2009 в ячейке A2, а затем — число 3 в ячейке B2.
- В ячейке A3 введите дату 02.09.2009, а в ячейке B3 — число –5.
- В ячейке A4 введите дату 10.12.2010, а в ячейке B4 — число 25.
- В ячейке A6 введите формулу =ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2)) и нажмите клавишу RETURN . Эта формула добавляет значение из ячейки B2 (3 года) к значению в ячейке A2, чтобы получить результат 09.06.2012.
- В ячейке A7 введите формулу =ДАТА(ГОД(A3)+B3;МЕСЯЦ(A3);ДЕНЬ(A3)) и нажмите клавишу RETURN . Эта формула добавляет значение из ячейки B3 (–5 лет) к значению в ячейке A3, чтобы получить результат 02.09.2004.
- В ячейке A8 введите формулу =ДАТА(ГОД(A4)+B4;МЕСЯЦ(A4);ДЕНЬ(A4)) и нажмите клавишу RETURN . Эта формула добавляет значение из ячейки B4 (25 лет) к значению в ячейке A4, чтобы получить результат 10.12.2035. В каждой из трех формул указанное количество лет из столбца B добавляется к значению, полученному из даты в столбце A. Например, в ячейке A6 функция ГОД применяется к дате в ячейке A2 (09.06.2009) и возвращает 2009 в качестве года. Затем формула добавляет 3 (значение из ячейки B2) к значению года, получая в результате 2012. В этой же формуле функция МЕСЯЦ возвращает значение 6, а функция ДЕНЬ — значение 9. Функция ДАТА объединяет эти три значения в дату, которая находится через три года в будущем: 09.06.2012.
Добавление к дате определенного количества дней, месяцев и лет
Допустим, вам нужно добавить определенное сочетание дней, месяцев и лет к заданной дате.
- На новом листе введите 09.06.2012 в ячейке A2.
- В ячейке A4 введите формулу =ДАТА(ГОД(A2)+3;МЕСЯЦ(A2)+1;ДЕНЬ(A2)+5) и нажмите клавишу RETURN . Эта формула добавляет 3 года, 1 месяц и 5 дней к дате 09.06.2012, возвращая результат 14.07.2015.
- В ячейке A5 введите формулу =ДАТА(ГОД(A2)+1;МЕСЯЦ(A2)+7;ДЕНЬ(A2)+5) и нажмите клавишу RETURN . Эта формула добавляет 1 год, 7 месяцев и 5 дней к дате 09.06.2012, возвращая результат 14.01.2014. В каждой формуле указанное количество лет, месяцев и дней прибавляется к дате, содержащейся в ячейке A2. Например, в ячейке A5 функция ГОД применяется к дате из ячейки A2 (09.06.2012) и возвращает 2012 в качестве значения года. Затем формула добавляет 1 к значению года, получая 2013. Функция МЕСЯЦ возвращает значение 6, к которому добавляются 7 месяцев. Поскольку при сложении 6 и 7 получается 13, функция ДАТА добавляет 1 год к значению года, получая в результате 2014. Затем функция ДАТА вычитает 12 из значения месяца, получая значение месяца 1. Функция ДЕНЬ возвращает значение 9, к которому добавляются 5 дней, и в результате получается 14. Наконец, функция ДАТА объединяет эти три значения (2014, 1 и 14) в дату, находящуюся через один год, семь месяцев и пять дней в будущем: 14.01.2014.
Как складывать даты в эксель
Прибавить к дате рабочие дни
Для того, чтобы точно определить дату окончания выполнения работ,
отстоящую от даты заказа или какого либо этапа, на определенное число РАБОЧИХ дней,
необходимо воспользоваться функцией РАБДЕНЬ.Рассчитаем даты выполнения этапов по доставке товара на региональный склад,
затем непосредственно клиенту и сборке товара на месте (например – мебель, габаритные товары для детей и т.п.).
-
В ячейку вводим формулу : =РАБДЕНЬ, выбираем из списка найденную формулу.
После нажатия на fx в строке формул, в открывшемся окне аргументов указываем Начальную дату. У нас это будет дата заказа.
Далее, указываем количество рабочих дней «Число_дней», которое надо прибавить к дате. В нашем случае это будет первый этап – поставка на региональный склад – 15 рабочих дней.
В аргументе «Праздники» указываем (просто выделяем) диапазон с датами праздничных дней. EXCEL учтет эти даты в расчете.
Далее, указываем количество рабочих дней «Число_дней», которое надо прибавить к дате. В нашем случае это будет первый этап – поставка на региональный склад – 15 рабочих дней.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)
Работа с датами в электронной таблице Microsoft Excel
В электронной таблице Microsoft Excel можно работать не только с числами и текстами, но и с датами. Даты можно сравнивать между собой, складывать и вычитать, а также использовать в других вычислениях. Например, можно вычислить число дней между двумя датами, определить, какой день недели приходился на ту или иную дату, и т.п. Эта статья познакомит вас с соответствующими возможностями популярной офисной программы.
Ввод значений даты
Для того чтобы ввести в ячейку дату, следует указать номер дня, номер месяца и две последние цифры года через точку (12.12.87), дефис (12-12-87) или символ “/” (12/12/87). Можно вводить также первые три буквы названия месяца (12-дек-87
и т.п.; для даты в мае месяце необходимо написать слово май). Текущий год можно не указывать — он будет добавлен к введенной дате автоматически. При вводе значений даты происходит их автоматическое распознавание, и общий формат ячейки 1 заменяется на встроенный формат даты. Так, если ввести, например, значение 12-12-87 или 12 дек 87, то в ячейке отобразится 12.12.87 2 , а в строке формул для данной ячейки будет выведено: 12.12.1987. Но если в ячейке указать 22.10.28, то в строке формул вместо ожидаемой даты 22.10.1928 вы увидите другую — 22.10.2028. Дело в том, что, если при вводе даты указаны только две последние цифры года, Microsoft Excel добавит первые две цифры по следующим правилам:— если введенное число лежит в интервале от 00 до 29, то оно интерпретируется как год с 2000-го по 2029-й;
— если введенное число лежит в интервале от 30 до 99, то оно интерпретируется как год с 1930-го по 1999-й.
Таким образом фирма Microsoft в свое время позаботилась о переходе в третье тысячелетие. Поэтому года с 1900-го по 1929-й следует указывать полностью.
По умолчанию значения даты выравниваются в ячейке по правому краю. Если не происходит автоматического распознавания формата даты, то введенные значения интерпретируются как текст, который выравнивается в ячейке по левому краю.
Представление дат в ячейках
Формат представления даты в ячейке, отображаемый после ввода значения, может быть изменен с помощью меню, пункт Формат, подпункт Ячейки, вкладка Число, раздел Числовые форматы, пункт Дата. Так, вместо значения 05.12.87 можно получить 5.12.87; 5 дек 87; 5 Декабрь, 1987 и другие представления одной и той же даты. При этом значение даты, отображаемое в строке формул, не меняется (оно не зависит от формата ее представления в ячейке).
Действия с датами
Как уже отмечалось, даты можно складывать и вычитать, сравнивать между собой. Можно также умножать и делить их на числа! Для того чтобы понять, как это реализуется, необходимо разобраться, как хранятся даты в компьютере. Введем в ячейку A1 дату “1 января 1900 года” (напомним, что для этого следует ввести 1-1-1900, а не 1-1-00). С помощью маркера заполнения распространим (скопируем) введенное значение на ячейки A2:A10 (в них появятся даты, в которых будут значения, соответствующие 2, 3, …, 10 января 1900 года). Скопируем блок ячеек A1:A10 в ячейки B1:B10. Изменим формат представления данных в блоке B1:B10 на Общий (Формат | Ячейки | Число | Общий). Мы увидим, что в этом блоке появятся значения 1, 2, …, 10. Из этого можно сделать важный вывод: дата в Excel — это количество дней, прошедших от 1 января 1900 года. Такая форма внутреннего представления дат и позволяет выполнять над ними различные арифметические операции и операции сравнения.
Основные функции для работы с датами
В Excel имеется ряд функций для работы с датами. Рассмотрим некоторые из них.
I. Функции ДЕНЬ, МЕСЯЦ и ГОД
Эти функции возвращают соответственно номер дня в месяце, номер месяца в году и год для некоторой даты.
Их синтаксис: ДЕНЬ(дата), МЕСЯЦ(дата) и ГОД(дата), — где аргумент дата — адрес ячейки, содержащей дату, либо дата, заданная в общем или числовом формате (12345), либо как текст (например, “15-4-93” или “15-Апр-1993”).
День возвращается как целое число в диапазоне от 1 до 31. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь). Значение года возвращается как целое число в интервале 1900–9999.
1. Если в ячейке А2 указана дата 26.10.49, то ДЕНЬ(А2) равняется 26, МЕСЯЦ(А2) равняется 10, ГОД(А2) равняется 1949.
2. ДЕНЬ(«4-Янв») равняется 4, МЕСЯЦ(«4-Янв») равняется 1.
3. ДЕНЬ(«15-Апр-1993») равняется 15, МЕСЯЦ(«15-Апр-1993») равняется 4, ГОД(«15-Апр-1993») равняется 1993.
4. ДЕНЬ(«11.8.93») равняется 11, МЕСЯЦ(«11.8.93») равняется 8, ГОД(«11.8.93») равняется 1993.
II. Функция ДЕНЬНЕД
Функция возвращает номер дня недели, соответствующий некоторой дате. Ее синтаксис: ДЕНЬНЕД(дата; тип),
— где дата — аргумент, аналогичный используемому в описанных выше функциях;
тип — число, которое определяет вариант возвращаемых значений:
1. Если в ячейке А2 указана дата 26.10.49, то ДЕНЬНЕД(А2) равняется 3 (Среда).
2. ДЕНЬНЕД(«15.2.90») равняется 5 (Четверг).
3. ДЕНЬНЕД(«15.2.90»; 2) равняется 4 (Четверг).
III. Функция СЕГОДНЯ
Функция возвращает дату текущего дня, отслеживаемую компьютером.
Ее синтаксис: СЕГОДНЯ () — без аргументов, но с обязательными скобками.
IV. Функция ДАТА
Функция позволяет “собрать” дату из значений года, номера месяца и номера дня. Ее синтаксис: ДАТА(год; месяц; день), где:
— год — это число от 1900 до 2078;
— месяц — это число, представляющее номер месяца в году;
— день — это число, представляющее номер дня в месяце.
Например, ДАТА(45; 5; 9) есть 9 мая 1945 года.
Задачи для самостоятельной работы
1. В ячейках В2 и В3 получить число 37 135 (указанное число ни в одну из ячеек не вводить):
2. В ячейках А2 и А3 получить число 18 197 (указанное число ни в одну из ячеек не вводить):
3. В ячейке В2 будет записана некоторая дата. Получить в ячейках В3:В5 соответственно номер дня в месяце, номер месяца и год этой даты:
4. Оформите лист для определения года, номера месяца и номера дня рождения:
Искомые значения должны быть получены в ячейках В3:В5.
5. Сотрудники отдела кадров обычно подсчитывают стаж работы на предприятии следующим образом. Выписывается текущая дата в виде 2007, 27 сентября, а под ней — дата начала работы работника на этом предприятии в аналогичном виде. Затем попарно вычитаются значения года, номера месяцев и номера дней в месяце. Например, если работник начал работать на предприятии 19 мая 2003 года, то его стаж работы составляет 4 года 8 месяцев и 6 дней. Оформите лист для расчета стажа работы по описанной методике с использованием данных типа Дата. Принять, что номер месяца и номер текущего дня больше соответствующих значений момента поступления на работу.
6. По дате, указанной в ячейке, определить номер дня недели, на который приходилась эта дата (понедельник — 1, вторник — 2, …, воскресенье — 7).
7. Научный сотрудник забыл точную дату конференции, на которой ему необходимо присутствовать, но помнит, что она должна начаться в четверг в период с 1 по 8 февраля 2008 года. Помогите ему определить точную дату начала конференции.
8. В ячейке В5 будет записана некоторая дата.
В ячейке В3 получить дату дня, который будет через 100 дней после указанной даты:9. В ячейке В2 будет записана некоторая дата.
В ячейке В3 получить дату дня, который был за 200 дней до указанной даты.10. В ячейке В2 получить дату текущего дня, в ячейке В4 — номер дня недели (понедельник — 1, вторник — 2, …, воскресенье — 7), который будет через некоторое число дней после текущего дня (это число будет указано в ячейке В3):
11. В ячейке В2 получить дату текущего дня, в ячейке В4 — номер дня недели (понедельник — 1, вторник — 2, …, воскресенье — 7), который был за некоторое число дней до текущего дня (это число будет указано в ячейке В3):
12. В ячейке В2 и В3 будут указаны даты двух событий. Определить, сколько дней прошло между этими событиями.
13. Определите свой возраст в днях и неделях.
14. Для текущей даты вычислить:
а) порядковый номер дня с начала года;
б) сколько дней осталось до конца года.
15. Определить, сколько дней длится первое полугодие года и сколько — второе.
16. В ячейке В2 указана дата некоторого события, произошедшего в первой половине ХХ века. Необходимо в ячейке В3 получить дату дня, до которого от 1 января 1900 года прошло в 2 раза больше дней, чем от 1 января 1900 года до дня данного события.
17. В ячейке В2 запишите дату вашего рождения, а в ячейке В3 получите дату текущего дня. Определить дату того дня, когда число дней вашей жизни станет в 2 раза больше, чем число прожитых дней до текущего дня. Дату получить в формате вида “12 Апрель, 2017”.
18. Известна дата рождения Пети. Определить дату рождения Коли, если известно, что число дней, прожитых им до текущего дня, в 2 раза меньше, чем число дней, прожитых Петей.
19. В ячейке В2 запишите дату вашего рождения, а в ячейке В3 получите дату текущего дня. Определить номера дней недели (понедельник — 1, вторник — 2, …, воскресенье — 7), которые будут, когда число дней вашей жизни станет в 2, 3, 4 и 5 раз больше, чем число прожитых дней до текущего дня.
20. После того, как в ячейки В2 и В3 будут введены даты двух событий, определить, какое событие произошло раньше.
21. Производственное совещание проходит по вторникам и пятницам. Составьте их расписание на февраль 2008 года в виде:
— где в строке 2 в ячейках, соответствующих вторникам и пятницам, должен быть указан какой-нибудь символ (“С”, “+” или т.п.).
22. На листе представлены сведения о дате рождения учеников класса:
В диапазоне ячеек С3:С27 поставить знак “+” для тех учеников, дата рождения которых:
а) приходится на среду;
б) приходится на 10-е число месяца;
в) приходится на август.
23. В ячейке В2 указана дата некоторого события. В ячейке В3 получить дату дня, который будет через 3 года после этого события.
24. В ячейке В2 указана дата некоторого события. В ячейке В3 получить дату дня, который был за 5 месяцев до этого события.
25. В ячейке В2 указана дата некоторого события. В ячейке В3 получить дату дня, который будет через n лет, m месяцев и k дней после этого события. Значения n, m и k вводятся в отдельные ячейки.
Решения, пожалуйста, присылайте в редакцию (можно решать не все задачи). Читателям, не имеющим доступа в Интернет, предлагаем в письме указать в соответствующих ячейках необходимые формулы. Фамилии всех приславших ответы будут опубликованы, а лучшие ответы мы поощрим.
1 Если в ячейке не установлен какой-либо специальный формат (числовой, процентный, финансовый, формат дат и т.д.), то данные в ней выводятся в так называемом “общем” формате, используемом для отображения как текстовых, так и числовых значений различного типа.
Суммы с датами в Excel
В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.
В примере показано, ячейка H7 содержит формулу:
Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.
Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.
Чтобы соответствовать времени между двумя значениями, нам нужно использовать два критерия. СУММЕСЛИМН требует, чтобы каждому критерию вводился в качестве критерия/пара диапазон:
Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).
Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).
Сумма, если Дата больше, чем
В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.
В примере показано, ячейка H4 содержит формулу:
Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.
Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).
Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).
ДАТА как ссылка на ячейку
Если вы хотите выставить дату на листе, так что он может быть легко изменена, используйте эту формулу:
где A1-ссылка на ячейку, которая содержит действительную дату.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.