Дати часто є важливою складовою аналізу даних. Часто виникає запитання на кшталт: коли придбано продукт, скільки часу займе виконання завдання в рамках проекту або який середній дохід за фінансовий квартал? Правильне введення дат має велике значення для забезпечення точності результатів. Але форматування дат для легкого розуміння не менш важливе для забезпечення правильної інтерпретації цих результатів.
Важливо
Оскільки правила, які регулюють інтерпретацію дат будь-якою програмою обчислення, є складними, потрібно бути якомога точнішим щодо дат, коли вводите їх. Це забезпечить найвищий рівень точності обчислень дат.
Відомості про обчислення дат і формати
У програмі Excel дати зберігаються як послідовні номери, які називаються порядковими значеннями. Наприклад, в Excel для Windows 1 січня 1900 р. має порядковий номер 1, а 1 січня 2008 р. – 39 448, оскільки ця дата настала через 39 448 днів після 1 січня 1900 р.
Час у програмі Excel зберігається як десяткове дроби, тому що час вважається частиною доби. Десяткове значення – це значення в діапазоні від 0 (нуля) до 0,99999999, яке відповідає часу від 0:00:00 (12:00:00) до 23:59:59 (11:59:59 PM).
Оскільки дати й час – це значення, їх можна додавати, віднімати та включати в інші обчислення. Можна відобразити дату як порядковий номер, а час як десятковий дріб, змінивши формат клітинки, яка містить дату або час, на Загальний.
Відомості про дві системи дат
Excel для Mac і Excel для Windows підтримують системи дат 1900 і 1904. Стандартна система дат в Excel для Windows – 1900. Стандартна система дат в Excel для Mac – 1904.
Спочатку програма Excel для Windows базувалася на системі дат 1900, оскільки вона забезпечувала кращу сумісність з іншими програмами для роботи з електронними таблицями, розробленими для роботи в MS-DOS і Microsoft Windows, і тому стала системою дат за замовчуванням. Спочатку програма Excel для Mac базувалася на системі дат 1904, оскільки вона забезпечувала кращу сумісність із ранніми комп'ютерами Macintosh, які не підтримували дати до 2 січня 1904 року, і тому стала системою дат за замовчуванням.
У таблиці нижче наведено першу й останню дати для кожної системи дат, а також порядковий номер, пов'язаний із кожною датою.
| Система дат | Перше побачення | Остання дата |
|---|---|---|
| 1900 | 1 січня 1900 р. (серійне значення 1) |
31 грудня 9999 р. (серійне значення 2958465) |
| 1904 | 2 січня 1904 р. (серійне значення 1) |
31 грудня 9999 р. (серійне значення 2957003) |
Оскільки обидві системи дат використовують різні початкові дні, одну й ту ж дату представлено різними порядковими значеннями в кожній системі дат. Наприклад, дата 5 липня 2007 року може мати два різні порядкові значення, залежно від використовуваної системи дат.
| Система дат | Серійний номер від 5 липня 2007 р. |
|---|---|
| 1900 | 37806 |
| 1904 | 39268 |
Різниця між двома системами дат становить 1462 дні. Таким чином, числове значення дати в системі дат 1900 року завжди на 1462 дні більше за порядкове значення тієї ж дати в системі дат 1904 року. І навпаки, числове значення дати в системі дат 1904 року завжди на 1462 дні менше, ніж числове значення тієї ж дати в системі дат 1900 року. 1462 дні дорівнює чотирьом рокам і одному дню (що включає один високосний день).
Змінення способу інтерпретації двозначних років
Важливо
Щоб забезпечити інтерпретацію значень року належним чином, введіть значення років чотирма цифрами (наприклад, 2001, а не 01). Якщо ввести чотиризначні роки, програма Excel не інтерпретує століття.
Якщо ввести дату з двозначним роком у клітинку з текстовим форматом або як текстовий аргумент у функції, наприклад =YEAR("01.01.31"), програма Excel інтерпретує рік так:
- Діапазон від 00 до 29 інтерпретується як роки з 2000 по 2029. Наприклад, якщо ввести дату 28.05.19, Excel інтерпретує це буде 28 травня 2019 р.
- Від 30 до 99 трактується як роки з 1930 по 1999. Наприклад, якщо ввести дату 28.05.98, Excel інтерпретує це буде 28 травня 1998 р.
У Microsoft Windows можна змінити спосіб інтерпретації двозначних років у всіх інстальованих програмах Windows.
Windows 10.
- У полі пошуку на панелі завдань введіть панель керування та виберіть результат Панель керування.
- У розділі "Годинник, мова та країна" виберіть посилання "Змінити формат дати, часу або числових форматів"
- Виберіть пункт "Мова та регіональні стандарти".
- У діалоговому вікні "Регіон" натисніть кнопку "Додаткові настройки".
- Перейдіть на вкладку "Дата ".
- У полі У полі У разі введення двозначного року інтерпретуйте його як рік проміжку , змініть верхню межу століття.
Коли ви змінюєте рік із верхньою межою, автоматично змінюється рік із нижньою межею. - Натисніть кнопку OK.
ОС Windows 8.
- Проведіть пальцем від правого краю екрана, торкніться елемента "Пошук" (якщо ви використовуєте мишу, перемістіть вказівник у правий верхній кут екрана, перемістіть вказівник униз, а потім натисніть кнопку "Пошук"). Введіть Панель керування у полі пошуку, а потім виберіть пункт Панель керування.
- У розділі " Годинник, мова та країна/регіон" виберіть пункт "Змінити формат дати, часу або числового формату".
- У діалоговому вікні "Регіон" натисніть кнопку "Додаткові настройки".
- Перейдіть на вкладку "Дата ".
- У полі У полі У разі введення двозначного року інтерпретуйте його як рік проміжку , змініть верхню межу століття.
Коли ви змінюєте рік із верхньою межою, автоматично змінюється рік із нижньою межею. - Натисніть кнопку OK.
Windows 7.
- Відкрийте меню Пуск і виберіть пункт Панель керування.
- Клацніть елемент "Регіон і мова".
- У діалоговому вікні "Регіон" натисніть кнопку "Додаткові настройки".
- Перейдіть на вкладку "Дата ".
- У полі У полі У разі введення двозначного року інтерпретуйте його як рік проміжку , змініть верхню межу століття.
Коли ви змінюєте рік із верхньою межою, автоматично змінюється рік із нижньою межею. - Натисніть кнопку OK.
Змінення формату дати за промовчанням для відображення чотиризначних років
За замовчуванням дати в книзі форматуються так, щоб відображати роки, виражені двома цифрами. Якщо змінити формат дати за промовчанням на інший, відображення дат, раніше введених у книзі, зміниться на новий формат, якщо дати не було відформатовано за допомогою діалогового вікна " Формат клітинок " (на вкладці " Основне " в групі "Число " клацніть запускач діалогових вікон).
Windows 10.
- У полі пошуку на панелі завдань введіть панель керування та виберіть результат Панель керування.
- У розділі "Годинник, мова та країна" виберіть посилання "Змінити формат дати, часу або числових форматів"
- Виберіть пункт "Мова та регіональні стандарти".
- У діалоговому вікні "Регіон" натисніть кнопку "Додаткові настройки".
- Перейдіть на вкладку "Дата ".
- У списку коротких форматів дати виберіть формат, який використовує чотири цифри року ("рррр").
- Натисніть кнопку OK.
ОС Windows 8.
- Проведіть пальцем від правого краю екрана, торкніться елемента "Пошук" (якщо ви використовуєте мишу, перемістіть вказівник у правий верхній кут екрана, перемістіть вказівник униз, а потім натисніть кнопку "Пошук"). Введіть Панель керування у полі пошуку, а потім виберіть пункт Панель керування.
- У розділі " Годинник, мова та країна/регіон" виберіть пункт "Змінити формат дати, часу або числового формату".
- У діалоговому вікні "Регіон" натисніть кнопку "Додаткові настройки".
- Перейдіть на вкладку "Дата ".
- У списку коротких форматів дати виберіть формат, який використовує чотири цифри року ("рррр").
- Натисніть кнопку OK.
Windows 7.
- Відкрийте меню Пуск і виберіть пункт Панель керування.
- Клацніть елемент "Регіон і мова".
- У діалоговому вікні "Регіон" натисніть кнопку "Додаткові настройки".
- Перейдіть на вкладку "Дата ".
- У списку коротких форматів дати виберіть формат, який використовує чотири цифри року ("рррр").
- Натисніть кнопку OK.
Змінення системи дат в Excel
Система дат змінюється автоматично, коли документ відкривається з іншої платформи. Наприклад, якщо під час роботи у програмі Excel відкривати документ, створений у програмі Excel для Mac, прапорець системи дат 1904 установлюється автоматично.
Ви можете змінити систему дат, виконавши такі дії.
- Виберіть елемент"Додатковіпараметри>файлу>".
- У розділі " Під час обчислення цієї книги " виберіть потрібну книгу, а потім установіть або зніміть прапорець "Використовувати систему дат 1904 ".
Проблема: у мене виникають проблеми з датами в книгах, у яких використовуються різні системи дат
Під час копіювання та вставлення дат або під час створення зовнішніх посилань між книгами на основі двох різних систем дат можуть виникнути проблеми. Дати можуть з'явитися на чотири роки і на один день раніше або пізніше очікуваної дати. Ці проблеми можуть виникнути незалежно від того, чи використовується Excel для Windows, Excel для Mac або обидві програми.
Наприклад, якщо скопіювати дату 5 липня 2007 року з книги, у якій використовується система дат 1900, і вставити дату в книгу, у якій використовується система дат 1904, дата відображатиметься як 6 липня 2011 року, тобто на 1462 дні пізніше. Крім того, якщо скопіювати дату 5 липня 2007 р. з книги, яка використовує систему дат 1904, і вставити її в книгу, яка використовує систему дат 1900, дата відображатиметься як 4 липня 2003 р., тобто на 1462 дні раніше. Докладні відомості див. в статті "Системи дат в Excel".
Виправлення неполадок копіювання та вставлення
У пусту клітинку введіть значення 1462.
Виділіть клітинку, а потім на вкладці "Основне " в групі "Буфер обміну " натисніть кнопку "Копіювати".
Виберіть усі клітинки, які містять неправильні дати.
Порада.
Щоб скасувати виділення клітинок, клацніть будь-яку клітинку аркуша. Докладні відомості див. в статті "Виділення клітинок, діапазонів, рядків або стовпців на аркуші".
На вкладці "Основне " в групі "Буфер обміну " натисніть кнопку "Вставити", а потім виберіть пункт "Спеціальне вставлення".
У діалоговому вікні " Спеціальне вставлення " в розділі "Вставити" виберіть пункт "Значення", а потім у розділі "Операція" виконайте одну з таких дій:
- Щоб настроїти дату як чотири роки і один день пізніше, натисніть кнопку "Додати".
- Щоб настроїти дату на чотири роки і на один день раніше, натисніть кнопку Відняти.
Виправлення неполадки із зовнішнім посиланням
Якщо використовується зовнішнє посилання на дату з іншої книги з іншою системою дат, його можна змінити, виконавши одну з таких дій:
- Щоб дата відображалася через чотири роки і один день пізніше, додайте до неї число 1462. Наприклад:
=[Book2]Sheet1!$A$1+1462
- Щоб дата мала вигляд на чотири роки і на один день раніше, відніміть від неї 1 462. Наприклад:
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.
Додаткові відомості
Створення дати в потрібному форматі
Відображення чисел як дат або часу