Приклад функції РОЗНДАТ для розрахунку часу між датами Excel
Функція РОЗНДАТ дозволяє з'ясувати, скільки повних днів, місяців чи років минуло між двома вказаними датами. Таким чином, можна легко порахувати в Excel: вік від дати народження, трудовий стаж, терміни вагітності та ін. Приклади розрахунків точних термінів, віку та тривалості до закінчення періодів часу за допомогою функції РОЗНДАТ. Як правильно вважати різницю між двома датами за 365 і 366 днів на рік?
Приклади використання функції РОЗНДАТ
Приклад 1. Потрібно обчислити, знаючи дату народження працівника, скільки повних років, місяців та днів йому виповнилося на момент сьогоднішньої дати.
Використовуючи як аргументи саму дату народження та функцію СЬОГОДНІ(), а також значення "Y", "M" і "D" для одиниці, отримаємо наступний набір формул:
Таким чином ми отримали точний вік у роках, місяцях та днях від дати народження.
Як точно порахувати скільки пройшло часу: років, місяців та днів?
Приклад 2. У документі записано дати народження працівників. Потрібно з'ясувати, скільки років, місяців і днів виповнилося кожному з них на сьогоднішній день.
Використовуючи вже знайому формулу = РОЗНДАТ(B2;СЬОГОДНІ();"Y") для осередку С2 отримаємо кількість повних років віку кожного з працівників. Протягнемо цю формулу на весь стовпець нижче.
Модифікувавши формулу, використану вище, замінюємо значення одиниці з "Y" на "YM". Підсумковий вид формули: =РОЗНДАТ(B2;СЬОГОДНІ();"YM"). Таким чином, ми отримаємо на виході кількість повних місяців віку, не враховуючи повні роки. Протягнемо формулу на весь стовпець нижче.
Наприкінці нам знадобляться і дні, які не увійшли на місяць.Знову замінюємо значення одиниці з "YM" на "YD" і отримаємо формулу наступного виду, в якій на виході буде число днів: = РОЗНДАТ(B2;СЬОГОДНІ();"YD").
Таке рішення можна елегантно покращити, якщо використовувати функцію СПЕЦПИТИ() і викликати віконце аргументів, навівши функцію в наступний вид:
Як порахувати терміни закінчення дії договору в Excel
Приклад 3. У документі записані дати укладання договорів працівників компанії. За умовчанням договір укладається 1 рік. Потрібно з'ясувати, скільки днів залишилося до закінчення трудового договору в кожного зі співробітників, щоб підготувати нові екземпляри для продовження договорів.
За допомогою функції РОЗНДАТ визначимо, скільки днів минуло з укладення трудового договору. У новому стовпці для комірки С2 використовуємо формулу: = РОЗНДАТ(B2;СЬОГОДНІ();"d") і простягнемо її на весь стовпець нижче.
Підрахуємо кількість днів, що залишилися до закінчення трудового договору кожного зі співробітників. Для цього в новому стовпці віднімаємо від кількості днів на рік значення зі стовпця С, використовуючи формулу:
Спочатку ми визначаємо кількість днів у році за датою (365 або 366) за формулою ЯКЩО(ОСТАТ(РОК(B2);4);365;366), а потім віднімаємо, щоб дізнатися скільки залишилося -C2.
Для зручності можна додати ще одну колонку, в якій за допомогою умов зазначити зауваження для продовження договору, якщо залишилося менше 10 днів до його закінчення. Для комірки E2 використовуємо формулу:
У цій формулі використовується функція ЯКЩО з кількома умовами. Якщо до кінця терміну більше 10 діб тоді формула повертає значення «Діє», а якщо менше 10 тоді одна з умов:
- Якщо менше 10 діб, але більше 0 – значення «Закінчується».
- Якщо менше 0, тоді «Прострочено».
Особливості використання функції РОЗНДАТ
- - Поч_дата: дата, від якої буде вестися розрахунок;
- - Кон_дата : дата, до якої вестиметься розрахунок;
- - Одиниця : аргумент, що вказує на величину, в якій проводитиметься розрахунок.
Примітка: аргументи поч_дата і кон_дата можуть приймати як числове значення, так і бути результатом будь-якого обчислення, що виробляється поза осередками (наприклад, функції ДАТА(), функції СЬОГОДНІ()).
Примітка: аргумент одиниця може приймати одне із шести значень:
- - "Y": у результаті підрахунків буде виведено кількість повних років між початковими аргументами;
- - "M": в результаті підрахунків буде виведено кількість повних місяців між початковими аргументами;
- - "D": у результаті підрахунків буде виведено кількість повних днів між початковими аргументами;
- — "MD": у результаті підрахунків буде виведено кількість днів між датами, не враховуючи місяці, що минули між датами. Наприклад, при обчисленні різниці між 1 вересня 2018 року та 21 листопада 2018 року (що становить 2 місяці та 20 днів) зі значенням “MD” буде повернено значення 20, т.к. минулі 2 повні місяці не будуть враховані;
- — "YM": у результаті підрахунків буде виведено кількість місяців між датами, не враховуючи роки, що минули між датами. Наприклад, при обчисленні різниці між 1 вересня 2010 року та 1 листопада 2018 року (що становить 8 років та 2 місяці) зі значенням “YM” буде повернено значення 2, т.к. минулі 8 років не будуть враховані;
- - "YD": в результаті підрахунків буде виведено кількість діб між датами, не враховуючи роки, що минули між датами.Наприклад, при обчисленні різниці між 1 вересня 2010 року та 2 вересня 2018 року (що становить 8 років та 1 день) зі значенням “YD” буде повернено значення 1, т.к. минулі 8 років не будуть враховані.
- Створити таблицю
- Форматування
- Функції Excel
- Формули та діапазони
- Фільтр та сортування
- Діаграми та графіки
- Зведені таблиці
- Друк документів
- Бази даних та XML
- Можливості Excel
- Налаштування параметри
- Уроки Excel
- Макроси VBA
- Завантажити приклади
Обчислення різниці двох дат
Попередження: Функція РОЗНДАТ пропонується Excel для підтримки старих книг Lotus 1-2-3. У деяких ситуаціях вона може давати неправильні результати. Для отримання додаткових відомостей див. у розділі Відомі проблеми статті про функцію DATEDIF.
Використовуйте функцію РОЗНДАТ, якщо потрібно визначити різницю двох дат. Спочатку помістіть дату початку в одну комірку, а дату закінчення - в іншу. Потім введіть формулу, наприклад, одну з наступних.
Примітка: Якщо значення поч_дата більше значення кон_дата, виникне помилка #КІЛЬКІСТЬ!
Різниця днями
У цьому прикладі дата початку знаходиться в клітинці D9, а дата закінчення - в клітинці E9. Формула знаходиться в осередку F9. Параметр "д" повертає кількість повних днів між двома датами.
Різниця у тижнях
У цьому прикладі дата початку знаходиться в клітинці D13, а дата закінчення - в клітинці E13. Параметр "д" повертає кількість днів. Але зверніть увагу на /7 наприкінці. Це ділить кількість днів на 7, тому що у тижні міститься 7 днів. Зверніть увагу, що цей результат також має бути представлений у числовому форматі. Натисніть клавіші CTRL+1. Потім натисніть Числовий >
Кількість десяткових знаків: 2.
Різниця у місяцях
У цьому прикладі дата початку знаходиться в клітинці D5, а дата закінчення - в клітинці E5. У формулі "м" повертає кількість повних місяців між двома днями.
Різниця у роках
У цьому прикладі дата початку знаходиться в клітинці D2, а дата закінчення - в клітинці E2. Параметр "г" повертає кількість повних років між двома днями
Розрахунок віку в накопичених роках, місяцях та днях
Ви також можете визначити вік або час роботи іншої людини. Результат може мати такий вигляд: "2 роки, 4 місяці, 5 днів".
1. Використовуйте функцію РОЗНДАТ, щоб знайти загальну кількість років.
У цьому прикладі дата початку знаходиться в клітинці D17, а дата закінчення - в клітинці E17. У формулі параметр "г" повертає кількість повних років між двома днями
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.
Завантаження прикладів
Ви можете завантажити зразок книги з усіма прикладами цієї статті. Ви можете скористатися ними чи створити власні формули.
Інші обчислення дати та часу
Як показано вище, функція РОЗНДАТ обчислює різницю між датою початку та датою закінчення. Однак замість введення певних дат у формулі можна використовувати функцію СЬОГОДНІ(). При використанні функції СЬОГОДНІ() Excel як дата використовує поточну дату комп'ютера. Майте на увазі, що ця змінна буде змінюватися при повторному відкритті файлу в майбутньому.
Зверніть увагу, цю статтю було написано 6 жовтня 2016 р.
Використовуйте функцію ЧИСТРАБДНІ.МЕЖД, якщо потрібно обчислити кількість робочих днів між двома датами. Ви також можете виключити вихідні та свята.
Перш ніж розпочати. Вирішіть, чи потрібно унеможливити дати свят. При виключенні введіть список свят в окремій області або на окремому аркуші. Помістіть кожну дату свята у власний осередок. Потім виділіть ці комірки та натисніть Формули >
Задати ім'я. Назвіть діапазон Мої Свята та натисніть ОК. Потім створіть формулу за допомогою наведених нижче дій.
1. Введіть дату початку та закінчення.
У цьому прикладі дата початку знаходиться в клітинці D53, а дата закінчення - в клітинці E53.
2. В іншому осередку введіть формулу такого виду.
Введіть формулу, як у прикладі вище. Цифра 1 у формулі встановлює суботи та неділі як вихідні та виключає їх із загальної кількості.
3. За потреби змініть цифру 1.
Якщо субота та неділя не є вихідними днями, змініть 1 на інше числове значення зі списку IntelliSense. Наприклад, значення 2 встановлює неділі та понеділки як вихідні дні.
4. Введіть ім'я діапазону свят.
Якщо ви створили ім'я діапазону свят у розділі "Перш ніж почати" вище, введіть його в кінці наступним чином.
Ви можете обчислити витрачений час, віднімаючи один час з іншого. Спочатку помістіть час початку в одну комірку, а час закінчення в іншу. та пробіл перед AM або PM. Нижче розповідається, як це зробити.
1. Введіть час початку та закінчення.
У цьому прикладі час початку знаходиться в клітинці D80, а час закінчення - в клітинці E80. та пробіл перед AM або PM.
2. Встановіть формат "ч: мм AM/PM".
Виберіть обидві дати та натисніть клавіші CTRL+1 (або
+1 на комп'ютері Mac). (Всі формати) >
ч:мм AM/PMякщо він ще не встановлений.
3. Відніміть два часи.
В іншому осередку відніміть осередок часу початку з осередку часу закінчення.
4. Встановіть формат "ч:мм".
Натисніть клавіші CTRL+1 (або
+1 на Mac). (Всі формати) >
ч:мм, щоб результат не містив AM та PM.
Щоб визначити час між двома датами з часом, можна просто відняти одне значення з іншого. Однак необхідно застосувати форматування до кожної комірки, щоб Excel повертав потрібний результат.
1. Введіть дві дати з часом.
В одному осередку введіть повну дату та час початку.А в іншому осередку введіть повну дату та час закінчення. Кожен осередок повинен містити місяць, день, рік, годину, хвилини, і пропуск перед AM або PM.
2. Встановіть формат "14.03.12 1:30".
Виберіть обидві комірки та натисніть клавіші CTRL+1 (або
+1 на комп'ютері Mac). Потім виберіть Дата >
14.03.12 1:30 PM. Це не дата, яку ви встановили, а просто приклад того, як виглядатиме формат. Зауважте, що у версіях до Excel 2016 цей формат може використовувати інший приклад дати, наприклад 14.03.01 1:30 PM.
3. Відніміть два значення.
В іншому осередку відніміть комірку дати та часу початку з дати та часу закінчення. Швидше за все, результат виглядатиме як число з десятковим знаком. Ви виправити це на наступному кроці.
4. Встановіть формат "[ч]:мм".
Натисніть клавіші CTRL+1 (або
+1 на Mac). Виберіть пункт (Всі формати). У полі Тип введіть [ч]:мм.
Як у Excel порахувати різницю між датами у роках?
Іноді на аркуші Excel нам потрібно вирахувати різницю між двома датами для отримання тривалості. Тут у цьому посібнику вводяться формули для обчислення різниці між двома датами в роках, місяцях, тижнях і днях.
Якщо ви хочете дотримуватися цього посібника, завантажте приклад електронної таблиці.
Загальна формула:
Розрахувати різницю в днях
Розрахувати різницю у тижнях
Розрахуйте різницю у місяцях
Розрахуйте різницю у роках
аргументи
| Start_datetime: The starting date in the date range Start date мусить бути коротким, ніж кінець дати, або формула буде відновити error result #NUM!. |
| End_datetime: Значення часу в межах часу, що ви хочете визначити термін часу. end date must be greater than the start date, otherwise the formula will return the error value #NUM!. |
| D: Return difference in days. |
| D/7:Return difference in weeks 7 днів на week, до відновлених days перш за все divides 7 to get the number of weeks.Як формула відновлення значення в форматі часу, вам потрібно відобразити результат для загального або номера, щоб отримати число weeks. |
| M: Return difference in months. |
| Y: Return difference in years. |
Як працює ця формула
Припустимо, вам потрібно розрахувати тривалість між датами в осередках B4 і B6, використовуйте наведену нижче формулу
Розрахувати різницю в днях
Розрахувати різницю у тижнях
Розрахуйте різницю у місяцях
Розрахуйте різницю у роках
Press Enter ключ для отримання результату.
Порада: формула для розрахунку різниці в тижнях дасть результат у форматі дати, просто відформатуйте осередок результату як загальний чи числовий, як вам потрібно.
пояснення
РОЗНДАТ (функція РОЗНДАТ): ця функція використовується для обчислення різниці між двома датами у днях, тижнях, місяцях, роках, днях без урахування років або місяці без урахування років.
Відносні формули
- Розрахувати кількість днів, що залишилися у місяці
У цьому посібнику представлено форму для швидкого розрахунку кількості днів, що залишилися в місяці в Excel.
- Обчислити Обчислити дні Годинник Хвилини Секунди між двома датами
Іноді нам може знадобитися отримати дні, години, хвилини та секунди між двома датами, у цьому посібнику представлена формула для вирішення цього завдання.
- Розрахунок днів, що залишилися між двома датами
Тут у цьому посібнику представлена формула для швидкого розрахунку днів, що залишилися, між двома датами.
- Розрахунок часу в мережі з перервою у розкладі
Він надає формулу, яка використовує функцію MOD для розрахунку мережного часу з перервами Excel.
Відносні функції
- Функція ЯКЩО в Excel
Тест на зазначені умови, потім повертає відповідні значення
- Функція Excel VALUE
Перетворіть текст на число.
- Функція МІСЯЦЬ в Excel
МІСЯЦЬ використовується для отримання місяця у вигляді цілого числа (від 1 до 12) від дати.
- Функція ДЕНЬ в Excel
Функція ДЕНЬ отримує день у вигляді числа (від 1 до 31) від дати
- Функція РІК в Excel
Функція РІК повертає рік на основі заданої дати у форматі 4-значного серійного номера.