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

Приклади, які будуть використовуватися тут, відповідають формату дати, прийнятому в русифікованому додатку Excel: дд.мм.рррр або дд / мм / рррр. Наприклад, дата 1.03.1952 або 01/03/1952 означає 1 березня 1952 року. Майте на увазі, що це не 3 січня 1952 року народження, як прийнято в регіональному форматі США.

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

Подання дати як порядкового числа

Для Excel дата - це порядкове число, яке представляє собою кількість днів, що минули з 0 січня 1900 року. Це означає, що порядкове число 1 відповідає даті 1 січня 1900 роки; число 2 - дати 2 січня 1900 року і т.д. Існування такої системи дозволяє використовувати формули при роботі з датами. Наприклад, вона дозволяє створити формулу, яка буде обчислювати кількість днів між двома датами.

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

Щоб уявити числове значення у вигляді дати, до осередку, що містить цей номер, необхідно застосувати формат Дата. Для цього відкрийте діалогове вікно Формат ячеек і клацніть на вкладці Число, а потім оберіть формат.

Примітка

Excel 2000 і більш пізні версії цього додатка підтримують дати в діапазоні від 1 січня 1900 року по 31 грудня 9999 роки (порядкове число цієї дати 2958465). Діапазон дат, доступний в більш ранніх версіях програми, значно менше. Тут використовуються дати з 1 січня 1900 року і до 31 грудня 2078 роки (порядкове число 65380).

Виберіть систему дат 1900 або 1904

Excel підтримує дві системи дат: система дат, починаючи з 1900 або 1904 року. Яку з них віддати перевагу в робочій книзі, ви визначаєте самостійно, в залежності від того, яка дата є підставою для інших дат. В системі дат, що починається з 1900 року, день 1 січня 1900 року має порядкове число 1. У системі дат 1904 року розпочало відліку посилання - 1 січня 1904 року. За замовчуванням для систем Windows додаток Excel використовує систему дат, що починає відлік з 1 січня 1900 року, а для систем Macintosh - дату, починаючи з 1904 року. Проте в Excel для Windows з метою забезпечення сумісності з файлами Macintosh теж підтримується система дат 1904. Систему дат можна змінити, використовуючи діалогове вікно параметрів програми. Щоб відкрити його, виберіть команду Файл → Параметри Excel, а потім клацніть на вкладці Додатково. Залежно від своїх намірів встановіть або зніміть в розділі При перерахунку цієї книги прапорець Використовувати систему дат 1904. Майте на увазі, що система дат не може змінюватися, якщо в комп'ютері використовується версія Excel для Macintosh.

Рекомендуємо використовувати систему дат, встановлену за замовчуванням (починаючи з 1900 року). Будьте особливо обережні, коли застосовуєте дві різні системи дат в пов'язаних робочих книгах. Припустимо, що Книга1 використовує систему дат, починаючи з 1904 року, і містить в осередку А1 дату 15/1/1999, а Кніга2, навпаки, використовує систему дат з 1900 року і містить посилання на клітинку A1 книги Книга 1. У книзі Кніга2 ця дата буде відображатися як 14/1/1995. Таким чином, обидві робочі книги використовують один і той же порядкове число дня (34 713), але в різних книгах воно інтерпретується по-різному.

Існує одна перевага використання системи дат 1904. Ця система дозволяє відображати негативні значення часу. Іншими словами, при використанні системи дат 1900 результат віднімання 4: 00-5: 30, не може бути відображений. При використанні системи дат 1904 результат відображається як негативне час -1: 30 (тобто різниця становить одну годину і 30 хвилин).

введення дати

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

Наприклад, якщо необхідно ввести дату 3 червня 2010 року, то це можна зробити досить просто - 3 Червень, 2010 використовуючи даний формат будь-якої іншої з наявних форматів дати. Excel перетворює введену інформацію і збереже її як числове значення 39 251 - порядкове число, відповідне цієї дати. Після цього Excel застосує формат дати, заданий за замовчуванням, тому вміст комірки може бути відображено в поданні, кілька відмінному від введеного.

Примітка

Залежно від встановлених регіональних параметрів операційної системи, дата, введена в форматі 3 червень, 2010 може інтерпретуватися як текстовий рядок. У такому випадку потрібно ввести дату, як визначено у вікні регіональних параметрів Windows.

Зверніть увагу, що при активізації комірки, що містить дату, в рядку формул відображається її вміст. Воно буде представлено в заданому за замовчуванням форматі, який відповідає скороченому вигляді дати, прийнятому в системі. Майте на увазі, що рядок формул не відображує порядкове число дати. Щоб відобразити порядкове число дати, застосуєте до осередку числовий формат.

Щоб змінити заданий за замовчуванням формат дати, необхідно змінити регіональні параметри операційної системи. Для цього в системному меню Пуск операційної системи Windows 7 виберіть команду Панель керування → Мова і регіональні стандарти і виберіть у відповідних списках, що розкриваються короткий і повний формати дат. В Excel за замовчуванням застосовується короткий формат дати.

Excel має досить широкі можливості перетворення введених дат, проте вони не цілком досконалі. Наприклад, програма не визнає в якості дати наступну введену інформацію:

• 1 червня 2010 року;
• Jun-1 2010 року;
• Jun-1/2010.

Цю інформацію Excel сприйме як текст. Тому перш ніж використовувати дати в формулах, переконайтеся, що Excel підтримує той формат дати, який збираєтеся ввести. В іншому випадку формули, які звертаються до цих дат, будуть працювати некоректно.

Існує ще один недолік. Якщо введена дата знаходиться поза підтримуваного діапазону дат, Excel також інтерпретує її як текст. Якщо порядкове число, що знаходиться за межами доступних для дати числових значень, спробувати привести до формату дати, замість значення буде відображатися ряд знаків "решітки" (#########).

Пошук дати

У робочому аркуші, в якому використовується досить багато дат, може виникнути необхідність знайти певну дату. Для цього скористайтеся діалоговим вікном Знайти і замінити Excel, яке можна відкрити, вибравши команду Головна → Редагувати → Знайти і виділити → Знайти або натиснувши комбінацію клавіш Ctrl + F>. Майте на увазі, що Excel досить вимоглива до точності введення шуканої інформації. Вам доведеться ввести повний чотирьохрозрядний рік дати в полі Знайти діалогового вікна пошуку даних. Формат дати повинен відповідати короткому поданням дати, прийнятому в системі. Це уявлення можна побачити в рядку формул.

Подання часу як порядкового числа

В Excel в якості часу застосовується подрібнена частина порядкового числа, що позначає дату. Іншими словами, Excel вважає добу одиницею, а все величини менше доби - відповідною частиною одиниці. Наприклад, порядкове число дати 18 червня 2010 року дорівнює 40347, а опівдні того ж дня має значення 40347,5.

Порядкове число однієї хвилини еквівалентно приблизно 0,00069444. Формула, наведена нижче, обчислює цей номер наступним чином: 24 години множиться на 60 хвилин, а потім 1 ділиться на отриманий результат. Таким чином, в знаменнику виходить кількість хвилин в один день (1440).

= 1 / (24 * 60)

Тим же способом в Excel визначається порядкове число однієї секунди - приблизно 0,00001157. Для цього 1 ділиться на 24 години, помножені на 60 хвилин і 60 секунд. В цьому випадку знаменник містить кількість секунд в один день (86400).

= 1 / (24 * 60 * 60)

Найменша одиниця виміру часу в додатку Excel - одна тисячна частка секунди (мілісекунда). Порядкове число наведеного нижче значення представляє час 23: 59: 59,999, або без однієї тисячної секунди опівночі.

0,99999999

У таблиці представлені різні значення часу і відповідні їм порядкові числа, за якими Excel розпізнає цей час.

введення часу

Як і у випадку з датами, немає необхідності вводити порядкове число часу. Досить ввести в клітинку час в будь-якому з форматів, підтримуваних в Excel. У таблиці наведено деякі приклади записів часу, підтримуваних в Excel.

Оскільки наведені вище приклади часу не пов'язані ні з яким певним днем, Excel за замовчуванням використовує порядкове число 0, відповідне датою 0 січня 1900 року, яка, за своєю суттю, не є датою.

Примітка

При використанні системи дат, що починається з 1904 року, тимчасові значення без значення дати відраховуються від першого січня 1904 року. Всі наведені нижче приклади ґрунтуються на системі дат, що починається з 1900 року.

Щоб об'єднати дату і час, використовуйте спільний формат дати і часу, який складається з формату дати, пробілу та формату часу. Наприклад, якщо в комірку ввести текст 18 червень, 2010 11:30, додаток Excel обчислить порядкове число +39251,4791666667 і відобразить його в доступному форматі дати і часу.

18.06.2010 11:30:00

У деяких випадках, коли вводиться час, що перевищує 24 години, пов'язана з ним дата відповідно збільшується. Наприклад, якщо ввести в клітинку час 25:00:00, Excel збільшить ту частину, яка відноситься до дня (оскільки час перевищує 24 години), і відобразить введене значення в такий спосіб:

01.01.2000 1:00:00

Те ж саме відбувається, якщо ввести одночасно і дату, і час, що перевищує 24 години. Введена дата при відображенні буде відкоригована. Наприклад, при введенні в клітинку 01.09.2010 25:00:00 на екрані відображається наступна інформація:

02.09.2010 1:00:00

При введенні тільки часу без відповідної дати максимальний час, який можна ввести в клітинку одно 9999: 59: 59, тобто менше 10000 годин. У цьому випадку Excel додає відповідну кількість днів, і на екрані відображається отримана дата. Якщо ж ввести час, що перевищує межу 10000 годин, то воно буде представлено як проста текстова рядок.

Форматування значень дати і часу

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

Для форматування комірки з датою і часом необхідно виділити осередок і клацнути по вкладці Основне на списку числових форматів. Цей список містить два формати дати і один формат часу.

При створенні формул з посиланнями на осередок, вміст якої представляє дату або час, Excel автоматично перетворює результат виконання формул в формат дати або часу. У деяких випадках це може бути дуже до речі, в інших же створює певні незручності і навіть дратує. На жаль, Excel не дозволяє відключити автоматичне форматування комірки. Проте в подібних випадках можна скористатися спеціальною комбінацією клавіш, щоб скасувати форматування всіх чисел осередки і повернутися до формату Загальний, заданому за замовчуванням. Для цього досить виділити осередок і натиснути комбінацію клавіш Ctrl + Shift + ~>.

Іноді можна зіткнутися з тим, що жоден з вбудованих форматів не підходить для потрібного представлення дати і часу. Не впадайте у відчай - Excel надасть вам прекрасну можливість створити власний числовий формат. Виберіть в списку пункт Інші числові формати (див. Малюнок вище) і введіть визначення користувальницького формату в поле Тип.

Наступний користувальницький формат даних особливо корисний для відображення часу:

[Ч]: мм: сс

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

Проблеми, пов'язані з датами

Незважаючи на всю ефективність форматів дат, в Excel все ж іноді виникають проблеми при роботі з датами. Багато з них йдуть в минуле. Excel була розроблена багато років тому, ще раніше, ніж так звана "проблема 2000 року" вперше потрясла світ. Але багато в чому розробники Excel "відштовхувалися" від Lotus 1-2-3 з її обмеженими можливостями управління датами і часом. Тому все найсерйозніші технічні дефекти були ненавмисно перенесені в Excel. Крім того, версії самої Excel немає одноманітно інтерпретують записи дат з роком з двох цифр. І нарешті, то, як Excel розуміє запис дати в осередку, безпосередньо залежить від регіональних параметрів, встановлених в Windows.

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

Помилка високосного року

Високосний рік, який настає через кожні чотири роки, має один додатковий день - 29 лютого. Незважаючи на те, що 1900 рік не був таким, Excel сприймає його як високосний. Іншими словами, якщо ввести в клітинку 29.02.1900, Excel немає відобразить повідомлення про помилку, а інтерпретує це як значення дати, відповідне 60-го дня з початку відліку (порядкове число 60):

29.2.1990

Однак якщо ввести іншу неприпустиму дату 29.02.1901, Excel відобразить повідомлення про помилку. Додаток не перетворює цей запис в дату, а, скоріше, сприйме її як текстовий рядок:

29.02.1901

Виникає питання: як програмний продукт, який щодня використовують мільйони людей, може містити таку очевидну помилку? Відповідь криється в історії його створення. Вихідна версія Lotus 1-2-3, на якій базувалося додаток Excel, містила дефект, через якого 1900 рік розглядався як високосний. Розробники програми Excel знали про цю помилку і пізніше, під час випуску додатка, спеціально перенесли цей дефект, щоб забезпечити його сумісність з робочими листами файлів Lotus.

Але чому ж ця помилка до сих пір існує в більш пізніх версіях Excel? Компанія Microsoft стверджує, що проблеми, які можуть виникнути при виправленні цієї помилки, значно переважують переваги виправлення. Усунення цієї помилки призвело б в безлад сотні тисяч існуючих робочих книг. Крім того, виправлення зазначеної проблеми позначилося б на сумісності самої Excel з іншими програмами, які використовують дату. Саме тому дана помилка тягне за собою набагато менше проблем, ніж її виправлення. До того ж, більшість користувачів не використовують дати, що передують 1 березня 1900 року.

Використання дат до 1900 року

Безумовно, світ почав своє існування не з 1 січня 1900 року. Користувачі Excel, які працюють з історичною інформацією, часто використовують дати до 1 січня 1900 року. Але, на жаль, єдиний спосіб роботи з такими датами в загальноприйнятих форматах - це ввести їх в осередок як текстову інформацію. Наприклад, якщо ввести в текстову осередок наступний рядок, Excel сприйме її без повідомлення про помилку:

4 липень, 1776

Якщо плануєте сортувати старі дати, введені як текст, або хочете позбутися від проблем з форматами, застосовуйте дати в форматі UTC (Universal Time Coordinated - універсальне скоординоване час за Гринвічем). У форматі UTC (стандарт ISO 8601) дата записується так: рррр-мм-дд, наприклад, 1776-07-24. Це стандартний міжнародний формат дати і часу, який використовується в авіаційній та морській навігації. Його основна перевага полягає в тому, що він правильно зчитується, обробляється і сортується практично в будь-якій системі (в тому числі, в Excel) і при будь-яких параметрах регіональних форматів з тієї причини, що в ньому все розряди по старшинству розташовані зліва направо. Для порівняння: в російській форматі дати (наприклад, 24.7.1776) рік (старший розряд) розташований правіше дня (молодшого розряду). На жаль, серед користувачів формат UTC мало популярний (як в США, так і в Росії), тому що для них він незвичний і з цієї причини погано сприймається візуально.

Однако датами, прийнятя у виде тексту, Вже нельзя маніпулюваті як номінальнімі, вімірюванімі в одиниць часу. Ці дати не можна сортувати, фільтрувати і форматувати, змінюючи числовий формат, як це прийнято в Excel. Не можна визначити день тижня, на який припадає задана дата, а також обчислити дату, яка слід сім'ю днями пізніше.

Введення суперечливих дат

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

В Excel 7 роки, що позначаються двома числами в діапазоні між 00 і 29, інтерпретуються як належать до XXI сторіччя, а дати між 30 і 99 - як дати XX століття. Наприклад, якщо ввести в клітинку запис 5.12.28, то Excel інтерпретує її як 5 грудня 2028 року. Але якщо ввести дату 5.12.30, Excel сприйме її як 5 грудня 1930 року. В Excel 2000 або пізнішої версії, що працює під управлінням Windows 98 і вище, в якості граничного року, заданого за замовчуванням, використовується 2029 рік. У той же час ви можете самостійно встановити граничний рік за допомогою вікна регіональних стандартів Windows.

Щоб уникнути будь-якого роду несподіванок при роботі з датами, просто введіть рік повністю, використовуючи всі чотири цифри.

на початок

Корисне