Для виконання певних завдань у Excel потрібно визначати, скільки днів минуло між деякими датами. На щастя, програма має інструменти, які здатні вирішити це питання. Давайте з'ясуємо, якими способами можна порахувати різницю дат в Екселі.
Перш ніж почати працювати з датами, потрібно відформатувати комірки під цей формат. У більшості випадків, при введенні комплекту символів, схожого на дату, осередок сам переформатується. Але краще все-таки зробити це вручну, щоби підстрахувати себе від несподіванок.
Тепер всі дані, які будуть у виділених осередках, програма розпізнаватиме як дату.
Спосіб 1: просте обчислення
Найпростіше обчислити різницю днів між датами за допомогою звичайної формули.
Спосіб 2: функція РОЗНДАТ
Для обчислення різниці в датах можна також застосовувати спеціальну функцію РОЗНДАТ. Проблема в тому, що у списку Майстра функцій її немає, тому доведеться вводити формулу вручну. Її синтаксис виглядає так:
РОЗНДАТ(початкова_дата;кінцева_дата;одиниця)
«Одиниця»— це формат, у якому у виділений осередок виводитиметься результат. Від того, який символ буде підставлений в даний параметр, залежить, в яких одиницях повертатиметься результат:
- "y" - повні роки;
- "m" - повні місяці;
- "d" - дні;
- "YM" - різниця в місяцях;
- «MD» - різниця в днях (місяці та роки не враховуються);
- "YD" - різниця в днях (роки не враховуються).
Також слід звернути увагу, що, на відміну від способу із застосуванням простої формули, описаного вище, при використанні цієї функції на першому місці має бути початкова дата, а кінцева – на другому. Інакше розрахунки будуть некоректними.
Спосіб 3: обчислення кількостей робочих днів
В Екселі також є можливість провести обчислення робочих днів між двома датами, тобто, крім вихідних і святкових. Для цього використовується функція ЧИСТРАБНІ. На відміну від попереднього оператора, вона є у списку Майстра функцій. Синтаксис цієї функції наступний:
ЧИСТРАБДНІ(поч_дата;кон_дата;[свята])
У цій функції основні аргументи, такі, як і в оператора РОЗНДАТ- Початкова і кінцева дата. Крім того, є необов'язковий аргумент «Свята».
Замість нього слід підставляти дати святкових неробочих днів, якщо такі є за період, що охоплюється. Функція робить розрахунок всіх днів зазначеного діапазону, за винятком субот, неділі, а також тих днів, які додані користувачем в аргумент «Свята».
Після зазначених вище маніпуляцій у попередньо виділеному осередку відобразиться кількість робочих днів за вказаний період.
Як бачимо, Excel надає своїм користувачам досить зручний інструментарій для розрахунку кількості днів між двома датами. При цьому, якщо потрібно розрахувати просто різницю в днях, то оптимальним варіантом буде застосування простої формули віднімання, а не використання функції РОЗНДАТ. А от якщо потрібно, наприклад, підрахувати кількість робочих днів, то тут на допомогу прийде функція ЧИСТРАБДНІ. Тобто, як завжди, користувачеві слід визначитися з інструментом виконання після того, як він поставив конкретне завдання.
На робочому аркуші Excel створено план завдань. В одній колонці вказані терміни виконання кожного завдання виконання. Щоб виділити кольором прострочені терміни поставлених завдань, нам потрібна точна різниця між датами в Excel. Для цього використовуватимемо умовне форматування з формулою РОЗНДАТ.
Як порахувати різницю між датами в Excel
Потрібно виділити кольором назви завдань, у яких терміни закінчуються вже через 7 днів. Приклад таблиці плану задач:
Фінальний ефект виділення закінчення термінів через 7 днів:
Виділено зеленим кольоромвсі завдання, які мають бути виконані через 7 днів. Якщо змінити значення в осередку D2 тоді буде виділено інші завдання.
Корисна порада! У осередку D2 можна використовувати функцію для отримання сьогоднішньої дати: =СЬОГОДНІ().
Формула різниця дат в Excel
Формула повертає різницю дат між сьогоднішньою та встановленою планом у днях. Для вирішення цієї задачі використовується функція РОЗНДАТ в Excel: де знайти цю формулу?
Цю функцію ви не знайдете у майстрі функцій і навіть на панелі «ФОРМУЛИ». Її завжди потрібно вводити вручну. Першим аргументом функції завжди має бути нова дата, а другим завжди – старша дата. Третій аргумент функції визначає одиницю виміру кількості, що повертає функція =РОЗНДАТ(). У разі це символ " d " – днів. Це означає, що функція повертає кількість днів. Далі слідує оператор<7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.
За необхідності можна до цього діапазону додати нове правило умовного форматування, яке попереджатиме нас про закінчення термінів за 2 тижні. Для цього потрібно додати нове правило умовного форматування для діапазону A2:A7 і лише трохи змінити формулу в новому правилі:
Колір форматування для другого правила можна встановити жовтий. До того самого діапазону повинні бути застосовані 2 правила умовного форматування. Щоб перевірити, виберіть інструмент: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Управління правилами». Так як у нас спочатку виконується верхнє правило слід змінити їх порядок у вікні: «Диспетчер правил умовного форматування». Інакше всі виділені завдання матимуть жовте заливання осередків. Просто виділіть перше правило і натисніть кнопку вниз (CTRL+стрілка вниз), як показано на малюнку:
В результаті план попереджає нас спочатку за два тижні, а потім за тиждень до закінчення термінів виконання завдань:
Корисна порада! Якщо одному і тому ж діапазону присвоєно багато правил – дотримуйтеся ієрархію пріоритетів порядку їх виконання диспетчері управління правилами. Чим вище правило, тим вище його пріоритет виконання по відношенню до інших, розташованих нижче під ним.
Ось уривок коду:
Запит = Новий запит("ВИБРАТИ | ВИБІР | КОЛИ РОЗНІСТЬДАТ(&Д1, ДОДАТИКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РОЗНІСТЬДАТ(ПОЧАТОКПЕРІОДУ(&Д1, МІСЯЦЬ), ПОЧАТОКПЕРІОД, ПОЧАТОКПЕРІОД Ц), ДЕНЬ)< 0 | ТОГДА ДЕНЬ(КОНЕЦПЕРИОДА(&Д1, МЕСЯЦ)) + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) | КОНЕЦ КАК Дни, | ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ДЕНЬ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, МЕСЯЦ), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ), ДЕНЬ) < 0 | ТОГДА ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА 12 + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | КОНЕЦ - 1 | ИНАЧЕ ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА 12 + РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) | КОНЕЦ | КОНЕЦ КАК Месяцы, | ВЫБОР | КОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), МЕСЯЦ) - РАЗНОСТЬДАТ(НАЧАЛОПЕРИОДА(&Д1, ГОД), НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД), МЕСЯЦ) < 0 | ТОГДА РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД) - 1 | ИНАЧЕ РАЗНОСТЬДАТ(&Д1, ДОБАВИТЬКДАТЕ(&Д2, ДЕНЬ, 1), ГОД) | КОНЕЦ КАК Годы"); Запрос.УстановитьПараметр("Д2",КонечнаяДата); Запрос.УстановитьПараметр("Д1",НачальнаяДата); ТабДМГ=Запрос.Выполнить().Выгрузить(); Стр = ТабДМГ; Сообщить("Разность дат составляет: "+Стр.Дни+" дней "+Стр.Месяцы+" месяцев "+Стр.Годы+" лет ");
Звичайно, можна ще дописати правильність написання днів, місяців та років залежно від числа. Але це вже інша тема.
Тут ми обчислюємо:
* для днів- беремо загальну кількість днів в інтервалі та забираємо вже враховану кількість днів, а це різниця між початками наших місяців із дат. Це число буде враховано у рядку з розрахунком місяця. Але при рачете днів перевіряємо кількість отриманих днів на негативність. Якщо число днів негативне - отже, ми перейшли наступного місяця, але фактично місяць між датами ще не пройшов. Щоб врахувати це, ми додаємо до негативної кількості днів кількість днів на місяці першої дати.
*для місяців- так само беремо загальну кількість місяців і забираємо вже враховану кількість місяців, а це різниця між початками наших ліг. Це число вже теж буде враховано у рядку з розрахунком року. Але під час розрахунку місяця також перевіряємо кількість отриманих місяців на негативність. Якщо кількість місяців негативно - значить ми перейшли у новий рік, але фактично рік між датами ще не минув. Щоб врахувати це, ми додаємо до негативної кількості місяців 12.
* для року- Беремо кількість років між датами. Але якщо кількість місяців було негативним, ми забираємо один рік, т.к. він все ж ще не пройшов.
І я додаю до другої дати один день,т.к. мені потрібно щоб цей останній день був включений до уваги.
Ну ось якось так. Якщо будуть якісь питання – пишіть.
Використовуйте функцію РОЗНДАТ, якщо потрібно обчислити різницю між двома датами. Спочатку введіть дату початку в комірку та дату закінчення в інший. Потім введіть формулу, наприклад, одну з наведених нижче.
Різниця днями
У цьому прикладі Дата початку знаходиться в клітинці D9, а Дата закінчення - E9. Формула буде показано на F9. "D"повертає кількість повних днів між двома датами.
Різниця у тижнях
У цьому прикладі Дата початку знаходиться в клітинці D13, а Дата закінчення - E13. "D" повертає кількість днів. Але зверніть увагу на те, що в кінці є /7 . Це ділить кількість днів на 7, тому що на тиждень є 7 днів. Зауважте, що цей результат також необхідно відформатувати як число. Натисніть клавіші CTRL + 1. Потім натисніть число _гт_ десяткових розрядів: 2.
Різниця у місяцях
У цьому прикладі Дата початку знаходиться в комірці D5, а Дата закінчення - в комірку "вниз". У формулі "м"повертає кількість повних місяців між двома днями.
Різниця у роках
У цьому прикладі Дата початку знаходиться в клітинці D2, а Дата закінчення - E2. "Y"
Обчислення віку у накопичених роках, місяцях та днях
1. Використовуйте РОЗНДАТ для пошуку загального числа років.
У цьому прикладі Дата початку знаходиться в клітинці D17, а Дата закінчення - E17. У формулі "y"повертає кількість повних років між двома днями
2. для пошуку місяців використовуйте РОЗНДАТ ще раз, вказавши "ГМ".
В іншому осередку використовуйте формулу РОЗНДАТ із параметром "ГМ". "ГМ" повертає кількість місяців, що залишилися після останнього повного року.
3. Використовуйте іншу формулу для пошуку днів.
Тепер потрібно знайти кількість днів, що залишилися. Це можна зробити, написавши формулу іншого типу, наведену вище. Ця формула віднімає перший день закінчення місяця (01.05.2016) з вихідної дати закінчення в осередку E17 (06.05.2016). Ось як це робиться: спочатку функція ДАТА створює дату 01.05.2016. Вона створюється за допомогою року в осередку E17 та місяця в осередку E17. 1 означає перший день місяця. Результатом функції ДАТА буде 01.05.2016. Потім ми віднімаємо цю дату з вихідної дати закінчення в осередку E17 (06.05.2016), в результаті чого виходить 5 днів.
4. Необов'язково: Поєднайте три формули в одну.
Ви можете розмістити всі три обчислення в одному осередку, як показано в цьому прикладі. Використання амперсандів, лапок та тексту. Це більш довга формула для введення, але принаймні це все в одній з них. Порада.Натисніть клавіші ALT + ВВЕДЕННЯ, щоб розмістити рядки у формулі. Це спрощує читання. Крім того, якщо ви не бачите формулу, натисніть клавіші CTRL + SHIFT + U.
Завантаження прикладів
Ви можете завантажити приклад книги з усіма прикладами, наведеними у цій статті. Ви можете підписатися на них або створити власні формули.
Інші розрахунки дати та часу
Розрахунок між сьогоднішньою та іншою датою
Як показано вище, функція РОЗНДАТ обчислює різницю між датою початку та кінцевою датою. Однак замість введення певних дат також можна використовувати функцію Today ()у формулі. Під час використання функції TODAY () Excel використовує поточну дату на комп'ютері. Майте на увазі, що при повторному відкритті файлу в майбутньому цей файл зміниться.
Розрахунок робочих днів зі святами чи без них
Використовуйте ЧИСТРАБДНІ. INTL, якщо потрібно визначити кількість робочих днів між двома датами. Крім того, ви можете також виключити вихідні та святкові дні.
Перш ніж розпочати, виконайте наведені нижче дії.Вирішіть, чи потрібно унеможливити дати свят. Якщо це так, введіть список свят в окрему область або на аркуш. Щодня свят поміщається в окремий осередок. Потім виділіть ці осередки, а потім виберіть формули _Гт_ присвоїти ім'я. Назвіть діапазон михолідайста натисніть кнопку ОК. Потім створіть формулу, виконавши наведені нижче дії.
1. Введіть дату початку та дату закінчення.
У цьому прикладі Дата початку знаходиться в клітинці D53, а Дата закінчення - в клітинці E53.
2. в іншому осередку введіть формулу, наприклад:
Введіть формулу, наприклад, наведений вище приклад. 1 у формулі визначає "Субота" та "неділя" як вихідні дні і виключає їх з підсумкового значення.
Примітка. в Excel 2007 немає ЧИСТРАБДНІ. МІЖ. Однак у нього є ЧИСТРАБДНІ. Наведений вище приклад буде виглядати так в Excel 2007: = ЧИСТРАБДНІ (D53, E53). Ви не вкажете 1, тому що ЧИСТРАБДНІ передбачає, що вихідні дні - субота та неділя.
3. у разі потреби змініть значення 1.
Якщо субота та неділя не є вихідними днями, змініть значення 1 на інший у списку IntelliSense. Наприклад, 2 встановлює неділю та понеділок у вихідні дні.
Якщо ви використовуєте Excel 2007, пропустіть цей крок. Функція ЧИСТРАБДНІ в Excel 2007 завжди передбачає, що вихідні - субота і неділя.
4. Введіть ім'я діапазону свят.
Якщо ви створили ім'я діапазону свят у наведеному вище розділі "Початок роботи", введіть його в кінці, як показано нижче. Якщо в тебе немає свят, ви можете залишити кому і Міхолідайс. Якщо ви використовуєте Excel 2007, наведений вище приклад буде виглядати наступним чином: = ЧИСТРАБДНІ (D53, E53, михолідайс).
ПероЯкщо ви не бажаєте посилатися на ім'я діапазону свят, ви також можете ввести діапазон, наприклад D35: E:39. Крім того, ви можете запровадити кожне свято у формулі. Наприклад, якщо святкові дні – 1 січня та 2 із 2016, введіть їх наступним чином: = ЧИСТРАБДНІ. Між (D53, E53, 1, ("1/1/2016", "1/2/2016")). У Excel 2007 воно виглядатиме так: = ЧИСТРАБДНІ (D53, E53, ("1/1/2016", "1/2 . 2016"})
Розрахунок витраченого часу
Щоб обчислити витрачений час, можна відняти один раз від іншого. Спочатку введіть час початку в клітинці і час закінчення в іншій. Переконайтеся, що весь час, включаючи години, хвилини та пробіли, заповніть до полудня або PM. Ось що потрібно для цього зробити:
1. Введіть час початку та закінчення.
У цьому прикладі час початку знаходиться в комірці D80, а час закінчення - E80. Переконайтеся, що вводите годинник, хвилину та пробіли перед символами AM та PM.
2. Встановіть формат ч/PM.
Виберіть обидві дати та натисніть клавіші CTRL + 1 (або + 1 на комп'ютері Mac). Переконайтеся, що вибрано параметр користувальницькі _гт_ год/PMякщо він ще не встановлений.
3. віднімання двох значень.
В іншому осередку віднімете початковий осередок з осередку "час закінчення".
4. Введіть формат год.
Натисніть клавіші CTRL+1 (або +1 на Mac). Виберіть " користувальницькі _гт_", щоб виключити з нього результати "AM" та "PM".
У MS Excel є надзвичайно цікава функція, яку мало хто знає. Так мало, що до цієї функції в екселі навіть не передбачено контекстної підказки при введенні, хоча, як не дивно, в довідці до програми вона є і описана досить непогано. Називається вона РОЗНДАТ()або DATEDIF()та служить для автоматичного розрахунку різниці у днях, місяцях чи роках між двома заданими датами.
Звучить не дуже? Насправді, іноді можливість швидко і точно порахувати скільки часу пройшло з якоїсь події, буває дуже корисною. Скільки місяців минуло з дня вашого народження, скільки часу ви вже просиджуєте штани на цьому місці роботи, або скільки днів ви сидите на дієті — та чи мало вжитків цієї корисної функції? А найголовніше, підрахунок можна автоматизувати і при кожному відкритті книги MS Excel отримувати точні дані саме сьогодні! Звучить цікаво, чи не так?
Функція РОЗНДАТ() приймає три аргументи:
- початкова дата— дата з якою ведеться рахунок
- Кінцева дата- До якої ведеться рахунок
- Одиниця виміру- Дні, місяці, роки.
Записується це так:
= РОЗНДАТ (початкова дата; кінцева дата; одиниця виміру)
Одиниці виміру записуються як:
- «y»- Різниця дат у повних роках
- "m"- Різниця дат у повних місяцях
- "d"- Різниця дат у повних днях
- "yd"- Різниця дат у днях з початку року без урахування років
- "md"- Різниця дат у днях без урахування місяців і років
- "ym"- Різниця дат у повних місяцях без урахування років
Іншими словами, щоб обчислити мій повний вік на поточний момент, я записую функцію як:
=РОЗНДАТ(14.07.1984;22.03.2016;»y»)
Зверніть увагу — останній аргумент завжди поміщений у лапки.
Якщо я хочу отримати точний вік, то запишу ускладнену формулу:
=РАЗНДАТ(F2;G2;»y»)&» рік «&РОЗНДАТ(F2;G2;»ym»)&» місяців»
У якій функція РОЗНДАТ() викликається відразу двічі, з різними значеннями, а слова «рік» і «місяців» просто пристиковуються до результату. Тобто, справжня міць функції проявляється тільки тоді, коли її комбінують з іншими можливостями MS Excel.
Ще один цікавий варіант - додати в функцію лічильник, що щодня зсувається щодо сьогоднішньої дати. Наприклад, якщо я вирішу писати формулу, яка вираховує кількість днів до моєї відпустки в стандартному вигляді, це буде виглядати приблизно так:
І все було б правильно, якби відкривши за тиждень цей лист, я побачив би, що кількість днів до відпустки скоротилася. Однак я побачу те саме число — адже вихідні дати не змінювалися. Відповідно, мені довелося б змінити поточну дату, і тоді функція РОЗНДАТ() зробила все правильно.
Щоб уникнути це прикрої дрібниці, як перший аргумент (сьогоднішнього числа), я підставлю не посилання на значення, що зберігається в комірці, а іншу функцію. Ця функція називається СЬОГОДНІ() та її основне та єдине завдання — повертати сьогоднішню дату.
Раз і проблема вирішена — відтепер, коли б я не відкрив цей лист MS Excel, функція РОЗНДАТ() завжди показуватиме мені точне значення, розраховане з урахуванням сьогоднішньої дати.
