Основи роботи з датами і часом в Excel

Відео

Як завжди, кому треба швидко – дивіться відео. Деталі та нюанси – у тексті нижче:

Як ввести дату і час в Excel

Якщо мати на увазі регіональні налаштування, то Excel дозволяє вводити дату дуже різними способами – і розуміє їх усі:

Зовнішній вигляд (відображення) дати в комірці може бути дуже різним (з роком або без нього, місяцем у вигляді числа чи слова тощо) і налаштовується через контекстне меню – клацніть правою кнопкою миші на комірці, а потім Формат комірки (Формат клітинок):

Час вводиться в клітинки за допомогою двокрапки. Наприклад

16:45

При бажанні ви можете додатково вказати кількість секунд – ввівши їх також через двокрапку:

16:45:30

І, нарешті, ніхто не забороняє вказувати дату і час відразу разом через пробіл, тобто 

27.10.2012 16:45

Швидке введення дат і часу

Щоб ввести сьогоднішню дату в поточну комірку, можна скористатися комбінацією клавіш Ctrl + Ж (Або CTRL+SHIFT+4 якщо у вас інша мова системи за замовчуванням).

Якщо копіювати комірку з датою (перетягнути з нижнього правого кута комірки), утримуючи право кнопкою миші ви можете вибрати спосіб копіювання вибраної дати:

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

Якщо ви хочете, щоб клітинка завжди містила актуальну сьогоднішню дату, краще скористатися функцією СЬОГОДНІ (СЬОГОДНІ):

Як Excel фактично зберігає та обробляє дати та час

Якщо вибрати клітинку з датою і встановити для неї Загальний формат (клацніть правою кнопкою миші на комірці Формат комірки – вкладка Номер - Загальне), можна побачити цікаву картинку:

Тобто, з точки зору Excel, 27.10.2012/15/42 41209,65417:XNUMX pm = XNUMX

Насправді Excel зберігає та обробляє будь-яку дату саме так – як число з цілою та дробовою частинами. Ціла частина числа (41209) — це кількість днів, що минули з 1 січня 1900 року (взято за відлікову точку) до поточної дати. А дробова частина (0,65417), відповідно, частка дня (1 день = 1,0)

З усіх цих фактів випливають два суто практичних висновки:

• По-перше, Excel не може працювати (без додаткових налаштувань) з датами раніше 1 січня 1900 року. Але ми це переживемо! 😉
• По-друге, в Excel можна виконувати будь-які математичні операції з датами і часом. Саме тому, що це насправді цифри! Але це вже відкриває перед користувачем масу можливостей.

Кількість днів між двома датами

Це вважається простим відніманням – ми віднімаємо початкову дату від кінцевої і переводимо результат у Загальне (Загальне) формат числа для відображення різниці в днях:

Кількість робочих днів між двома датами

Тут ситуація трохи складніша. Суботи, неділі та свята не повинні враховуватися. Для такого розрахунку краще скористатися функцією ЧИСТІ РОБІТНИКИ (ДНІ МЕРЕЖІ) з категорії Дата і час проведення. В якості аргументів цієї функції необхідно вказати дати початку і закінчення, а також комірки з датами вихідних (державні свята, лікарняні, відпустки, вихідні тощо):

примітки: Ця функція з’явилася в стандартному наборі функцій Excel з версії 2007 року. У старих версіях спочатку потрібно підключити надбудову Пакет аналізу. Для цього зайдіть в меню Сервіс – Додатки (Інструменти — Надбудови) і поставте прапорець біля пункту Пакет аналізу (Пакет інструментів Analisys). Після цього в Майстрі функцій у категорії Дата і час проведення з'явиться потрібна нам функція ЧИСТІ РОБІТНИКИ (ДНІ МЕРЕЖІ).

Кількість повних років, місяців і днів між датами. Вік у роках. Досвід.

Про те, як його правильно розрахувати, краще прочитати тут.

Зсув дати на вказану кількість днів

Оскільки один день у системі відліку дат Excel береться за одиницю (див. вище), щоб обчислити дату, яка віддалена, скажімо, на 20 днів від заданої, достатньо додати це число до дати.

Зсув дати на вказану кількість робочих днів

Цю операцію виконує функція Робочий день (РОБОЧИЙ ДЕНЬ). Він дозволяє розрахувати дату вперед або назад відносно дати початку на потрібну кількість робочих днів (з урахуванням субот і неділь, а також святкових днів). Використання цієї функції точно таке ж, як використання функції ЧИСТІ РОБІТНИКИ (ДНІ МЕРЕЖІ) описано вище.

Обчислення дня тижня

Ти не в понеділок народився? ні? Зрозуміло? Це легко перевірити за допомогою функції ДЕНЬ (БУДНИЙ ДЕНЬ)з категорії Дата і час проведення.

Перший аргумент цієї функції - комірка з датою, другий - тип підрахунку днів тижня (найзручніше 2).  

Розрахунок часових інтервалів

Оскільки час в Excel, як було сказано вище, є таким же числом, як і дата, але тільки його дробова частина, то з часом також можливі будь-які математичні дії, як і з датою – додавання, віднімання і т.д.

Тут є лише один нюанс. Якщо при додаванні декількох часових інтервалів сума вийшла більше 24 годин, то Excel скине її і знову почне підсумовувати з нуля. Щоб цього не сталося, потрібно застосувати формат до кінцевої клітинки 37:30:55:

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