На робочому аркуші Excel створено план завдань. В одній колонці вказано термін виконання для кожного завдання виконання. Щоб виділити кольором прострочені терміни поставлених завдань, нам потрібна точна різниця між датами в Excel. Для цього будемо використовувати умовне форматування із формулою РОЗНДАТ.

Як порахувати різницю між датами в Excel

Потрібно виділити кольором назви завдань, у яких терміни закінчуються вже через 7 днів. Приклад таблиці плану задач:

Фінальний ефект виділення закінчення термінів через 7 днів:

Виділено зеленим кольором усі завдання, які мають бути виконані через 7 днів. Якщо змінити значення в осередку D2 тоді буде виділено інші завдання.

Корисна порада! У осередку D2 можна використовувати функцію для отримання поточної дати: =СЬОГОДНІ().

Формула різниця дат в Excel

Формула повертає різницю дат між сьогоднішньою та встановленою планом у днях. Для вирішення цієї задачі використовується функція РОЗНДАТ в Excel: де знайти цю формулу?

Цю функцію ви не знайдете у майстрі функцій і навіть на панелі «ФОРМУЛИ». Її завжди потрібно вводити вручну. Першим аргументом функції завжди має бути нова дата, а другим завжди – старша дата. Третій аргумент функції визначає одиницю виміру кількості, що повертає функція =РОЗНДАТ(). У разі це символ " d " – днів. Це означає, що функція повертає кількість днів. Далі слідує оператор<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.

За необхідності можна до цього діапазону додати нове правило умовного форматування, яке попереджатиме нас про закінчення термінів за 2 тижні. Для цього потрібно додати нове правило умовного форматування для діапазону A2:A7 і лише трохи змінити формулу в новому правилі:

Колір форматування для другого правила можна встановити жовтий. До того самого діапазону повинні бути застосовані 2 правила умовного форматування. Щоб перевірити, виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Управління правилами». Так як у нас спочатку виконується верхнє правило слід змінити їх порядок у вікні: «Диспетчер правил умовного форматування». Інакше всі виділені завдання матимуть жовте заливання осередків. Просто виділіть перше правило і натисніть кнопку вниз (CTRL+стрілка вниз), як показано на малюнку:

В результаті план попереджає нас спочатку за два тижні, а потім за тиждень до закінчення термінів виконання завдань:

Корисна порада! Якщо одному і тому ж діапазону присвоєно багато правил – дотримуйтеся ієрархію пріоритетів порядку їх виконання диспетчері управління правилами. Чим вище правило, тим вище його пріоритет виконання по відношенню до інших, розташованих нижче під ним.

Для виконання певних завдань у Excel потрібно визначати, скільки днів минуло між деякими датами. На щастя, програма має інструменти, які здатні вирішити це питання. Давайте з'ясуємо, якими способами можна порахувати різницю дат в Екселі.

Перш ніж почати працювати з датами, потрібно відформатувати комірки під цей формат. У більшості випадків, при введенні комплекту символів, схожого на дату, осередок сам переформатується. Але краще все-таки зробити це вручну, щоби підстрахувати себе від несподіванок.

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

Спосіб 1: просте обчислення

Найпростіше обчислити різницю днів між датами за допомогою звичайної формули.

Спосіб 2: функція РОЗНДАТ

Для обчислення різниці в датах можна також застосовувати спеціальну функцію РОЗНДАТ. Проблема в тому, що в списку Майстра функцій її немає, тому доведеться запроваджувати формулу вручну. Її синтаксис виглядає так:

РОЗНДАТ(початкова_дата;кінцева_дата;одиниця)

«Одиниця»— це формат, у якому у виділений осередок виводитиметься результат. Від того, який символ буде підставлений в даний параметр, залежить, в яких одиницях повертатиметься результат:

• "y" - повні роки;
• "m" - повні місяці;
• "d" - дні;
• "YM" - різниця в місяцях;
• «MD» - різниця в днях (місяці та роки не враховуються);
• "YD" - різниця в днях (роки не враховуються).

Також слід звернути увагу, що, на відміну від способу із застосуванням простої формули, описаного вище, при використанні цієї функції на першому місці має бути початкова дата, а кінцева – на другому. Інакше розрахунки будуть некоректними.

Спосіб 3: обчислення кількостей робочих днів

В Екселі також є можливість провести обчислення робочих днів між двома датами, тобто, крім вихідних і святкових. Для цього використовується функція ЧИСТРАБНІ. На відміну від попереднього оператора, вона є у списку Майстра функцій. Синтаксис цієї функції наступний:

ЧИСТРАБДНІ(поч_дата;кон_дата;[свята])

У цій функції основні аргументи, такі, як і в оператора РОЗНДАТ- Початкова і кінцева дата. Крім того, є необов'язковий аргумент «Свята».

Замість нього слід підставляти дати святкових неробочих днів, якщо такі є за період, що охоплюється. Функція робить розрахунок всіх днів зазначеного діапазону, за винятком субот, неділі, а також тих днів, які додані користувачем в аргумент «Свята».

Після зазначених вище маніпуляцій у попередньо виділеному осередку відобразиться кількість робочих днів за вказаний період.

Як бачимо, Excel надає своїм користувачам досить зручний інструментарій для розрахунку кількості днів між двома датами. При цьому, якщо потрібно розрахувати просто різницю в днях, то оптимальним варіантом буде застосування простої формули віднімання, а не використання функції РОЗНДАТ. А от якщо потрібно, наприклад, підрахувати кількість робочих днів, то тут на допомогу прийде функція ЧИСТРАБДНІ. Тобто, як завжди, користувачеві слід визначитися з інструментом виконання після того, як він поставив конкретне завдання.

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

Додати дні до дати

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

Обчислення кількості днів між датами

Цей метод розрахунку відповість питанням " скільки днів минуло з дати " . Введіть початкову дату та кінцеву дату та натисніть кнопку "Розрахувати". Калькулятор покаже скільки днів між введеними датами. Окремо калькулятор покаже кількість робочих днів.

За допомогою цієї опції можна обчислити скільки днів залишилося до певної події, наприклад, до дня народження або свята. Для цього в полі початкової дати вкажіть сьогоднішнє число, а в полі кінцевої дати – дату події.

Святкові дні

Калькулятор може обчислювати, додавати та віднімати як календарні дні, так і робочі. Офіційними неробочими святковими днями є:

• 1,2,3,4,5,6,8 січня – новорічні канікули
• 7 січня - православне Різдво
• 23 лютого – День захисника Вітчизни
• 8 березня – Міжнародний жіночий день
• 1 травня - Свято Весни та Праці
• 9 травня День Перемоги
• 12 червня – День Росії
• 4 листопада - День народної єдності

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

Так, у 2019 році ситуація...

Перенесення вихідних у 2019 році

Крім офіційних святкових дат, у 2019 році вихідними також є 2,3 та 10 травня за рахунок переносів вихідних із новорічних канікул.

Наш калькулятор під час розрахунку днів враховує як офіційні святкові дати, так і всі переноси.

Для обчислень тривалості часових інтервалів найзручніше використовувати недокументовану функцію РОЗНДАТ( ) , англійська версія DATEDIF().

Функції РОЗНДАТ() немає у довідці EXCEL2007 та в Майстри функцій (SHIFT+ F3 ), але вона працює, хоч і не без огріх.

Синтаксис функції:

РОЗНДАТ(початкова_дата; кінцева_дата; спосіб_вимірювання)

Аргумент початкова датамає бути раніше аргументу кінцева_дата.

Аргумент спосіб_вимірюваннявизначає, як і в яких одиницях вимірюватиметься інтервал між початковою та кінцевою датами. Цей аргумент може приймати такі значення:

Нижче наведено докладний опис усіх 6 значень аргументу спосіб_вимірювання, а також альтернативних формул (функцію РОЗНДАТ() можна замінити іншими формулами (щоправда громіздкими). ​​Це зроблено у файлі прикладу ).

У файлі прикладу значення аргументу початкова датапоміщена в осередку А2 , а значення аргументу кінцева_дата– у комірці В 2 .

1. Різниця днями ("d")

Формула =РОЗНДАТ(A2;B2;"d") поверне просту різницю в днях між двома датами.

Приклад1:початкова дата 25.02.2007, кінцева_дата 26.02.2007
Результат: 1 день).

Цей приклад показує, що з підрахунку стажу необхідно використовувати функцію РОЗНДАТ() з обережністю. Очевидно, якщо співробітник працював 25 і 26 лютого, то відпрацював він 2 дні, а чи не 1. Те ​​саме стосується і розрахунку повних місяців (див. нижче).

Приклад2:початкова дата 01.02.2007, кінцева_дата 01.03.2007
Результат: 28 (днів)

Приклад3:початкова дата 28.02.2008, кінцева_дата 01.03.2008
Результат: 2 (дня), т.к. 2008 рік – високосний

Примітка: Якщо цікавлять тільки робочі дні, то між двома датами можна порахувати за формулою =ЧИСТРАБДНІ(B2;A2)

2. Різниця у повних місяцях ("m")

Формула =РОЗНДАТ(A2;B2;"m") поверне кількість повних місяців між двома датами.

Приклад1:початкова дата 01.02.2007, кінцева_дата 01.03.2007
Результат: 1 місяць)

Приклад2:початкова дата 01.03.2007, кінцева_дата 31.03.2007
Результат: 0

При розрахунку стажу вважається, що співробітник, який відпрацював усі дні місяця, відпрацював 1 повний місяць. Функція РОЗНДАТ() так не рахує!

Приклад3:початкова дата 01.02.2007, кінцева_дата 01.03.2009
Результат: 25 місяців

=12*(РІК(B2)-РІК(A2))-(МІСЯЦЬ(A2)-МІСЯЦЬ(B2))-(ДЕНЬ(B2)<ДЕНЬ(A2))

Увага: У довідці MS EXCEL (див. розділ Обчислення віку) є крива формула для обчислення кількості місяця між двома датами:

=(РІК(ТДАТА())-РІК(A3))*12+МІСЯЦЬ(ТДАТА())-МІСЯЦЬ(A3)

Якщо замість функції ТДАТА() - поточна дата використовувати дату 31.10.1961, а А3 ввести 01.11.1962, то формула поверне 13, хоча фактично минуло 12 місяців і 1 день (листопад і грудень 1961г. + 10 місяців 1962г.). .

3. Різниця у повних роках ("y")

Формула = РОЗНДАТ(A2;B2;"y") поверне кількість повних років між двома датами.

Приклад1:початкова дата 01.02.2007, кінцева_дата 01.03.2009
Результат: 2 роки)

Приклад2:початкова дата 01.04.2007, кінцева_дата 01.03.2009
Результат: 1 рік)

Формула може бути замінена альтернативним виразом:
=ЯКЩО(ДАТА(РІК(B2);МІСЯЦЬ(A2);ДЕНЬ(A2))<=B2;
РІК(B2)-РІК(A2); РІК(B2)-РІК(A2)-1)

4. Різниця у повних місяцях без урахування років ("ym")

Формула =РОЗНДАТ(A2;B2;"ym") поверне кількість повних місяців між двома датами без урахування років (див. приклади нижче).

Приклад1:початкова дата 01.02.2007, кінцева_дата 01.03.2009
Результат: 1 (місяць), т.к. порівнюються кінцева дата 01.03.2009 та модифікована початкова дата 01.02. 2009 (рік початкової дати замінюється роком кінцевої дати, тому що 01.02 менше ніж 01.03)

Приклад2:початкова дата 01.04.2007, кінцева_дата 01.03.2009
Результат: 11 (місяць), т.к. порівнюються кінцева дата 01.03.2009 та модифікована початкова дата 01.04. 2008 (Рік початкової дати замінюється роком кінцевої дати за вирахуванням 1 року, т.к. 01.04 більше ніж 01.03)

Формула може бути замінена альтернативним виразом:
= ОСТАТ (C7; 12)
У осередку С7 повинна бути різниця в повних місяцях (див. п.2).

5. Різниця у днях без урахування місяців та років ("md")

Формула =РОЗНДАТ(A2;B2;"md") поверне кількість днів між двома датами без урахування місяців і років. Використовувати функцію РОЗНДАТ() із цим аргументом не рекомендується (див. приклади нижче).

Приклад1:початкова дата 01.02.2007, кінцева_дата 06.03.2009
Результат1: 5 (днів), т.к. порівнюються кінцева дата 06.03.2009 та модифікована початкова дата 01. 03 .2009 (рік і місяць початкової дати замінюється роком та місяцем кінцевої дати, тому що 01 менше ніж 06)

Приклад2:початкова дата 28.02.2007, кінцева_дата 28.03.2009
Результат2: 0, т.к. порівнюються кінцева дата 28.03.2009 та модифікована початкова дата 28. 03 .2009 (рік та місяць початкової дати замінюється роком та місяцем кінцевої дати)

Приклад3:початкова дата 28.02.2009, кінцева_дата 01.03.2009
Результат3: 4 (дня) - абсолютно незрозумілий та НЕПРАВИЛЬНИЙ результат. Відповідь має бути =1. Крім того, результат обчислення залежить від версії EXCEL.

Версія EXCEL 2007 з SP3:

Результат – 143 дні! Більше, ніж днів на місяці!

Версія EXCEL 2007:

Різниця між 28.02.2009 та 01.03.2009 – 4 дні!

Причому EXCEL 2003 з SP3 формула повертає правильний результат 1 день. Для значень 31.12.2009 та 01.02.2010 результат взагалі негативний (-2 дні)!

Не раджу використати формулу з вищезазначеним значенням аргументу. Формула може бути замінена альтернативним виразом:
=ЯКЩО(ДЕНЬ(A2)>ДЕНЬ(B2);
ДЕНЬ(КОНМІСЯЦЯ(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2);
ДЕНЬ(B2)-ДЕНЬ(A2))

Ця формула лише еквівалетний (у більшості випадків) вираз для РОЗНДАТ() з параметром md. Про коректність цієї формули читайте в розділі "Ще раз про кривизну РОЗНДАТ()" нижче.

6. Різниця у днях без урахування років ("yd")

Формула =РОЗНДАТ(A2;B2;"yd") поверне кількість днів між двома датами без урахування років. Використовувати її не рекомендується через причини, викладені в попередньому пункті.

Результат, що повертається формулою = РОЗНДАТ(A2;B2;"yd") залежить від версії EXCEL.

Формула може бути замінена альтернативним виразом:
=ЯКЩО(ДАТА(РІК(B2);МІСЯЦЬ(A2);ДЕНЬ(A2))>B2;
B2-ДАТА(РІК(B2)-1;МІСЯЦЬ(A2);ДЕНЬ(A2));
B2-ДАТА(РІК(B2);МІСЯЦЬ(A2);ДЕНЬ(A2)))

Ще раз про кривизну РОЗНДАТ()

Знайдемо різницю дат 16.03.2015 та 30.01.15. Функція РОЗНДАТ() з параметрами md та ym підрахує, що різниця становить 1 місяць та 14 днів. Чи це так насправді?

Маючи формулу, еквівалентну РОЗНДАТ() можна зрозуміти хід обчислення. Вочевидь, що у разі кількість повних місяців між датами = 1, тобто. весь лютий. Для обчислення днів, функція знаходить кількість днів попереднього місяця щодо кінцевої дати, тобто. 28 (кінцева дата належить березню, попередній місяць – лютий, а у 2015 р. у лютому було 28 днів). Після цього забирає день початку та додає день кінцевої дати = ДЕНЬ(КОНМІСЯЦЯ(ДАТАМЕС(B6;-1);0))-ДЕНЬ(A6)+ДЕНЬ(B6), тобто. 28-30 +16 = 14. На наш погляд, між датами все ж таки 1 повний місяць і всі дні березня, тобто 16 днів, а не 14! Ця помилка виявляється, коли попереднього місяця щодо кінцевої дати, днів менше, ніж днів початкової дати. Як вийти із цієї ситуації?

Модифікуємо формулу для розрахунку днів різниці без урахування місяців та років:

=ЯКЩО(ДЕНЬ(A18)>ДЕНЬ(B18);ЯКЩО((ДЕНЬ(КОНМІСЯЦЯ(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18))<0;ДЕНЬ(B18);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B18;-1);0))-ДЕНЬ(A18)+ДЕНЬ(B18));ДЕНЬ(B18)-ДЕНЬ(A18))

При застосуванні нової функції необхідно враховувати, що різниця днями буде однаковою для кількох початкових дат (див. малюнок вище, дати 28-31.01.2015). В інших випадках формули еквівалентні. Яку формулу застосовувати? Це вирішувати користувачеві залежно від умови завдання.