Трюк №74
Іноді потрібно відобразити негативні значення часу, але Excel виводить тільки ######. Є кілька способів вирішити цю проблему.
Якщо ви введете час 12:00:00 (полудень) у будь-яку комірку, а потім відформатуєте її, вибравши формат Загальний (General), то побачите, що її числове значення дорівнює 0.5 (в англійській версії). Подібним чином введіть час 24:00:00 в будь-яку комірку. Подивіться рядок формул і ви побачите 1/1/1900 12:00:00 AM.
Спосіб 1. Зміна системи дат Excel за умовчанням
Перший швидкий і простий спосіб навчити Excel відображати негативні значення часу - вибрати команду пункт меню Файл → Параметри Excel → Обчислення (File → Parametres Excel → Calculation) та встановити прапорець Система дат 1904 (1904 date system). Система дат 1904 року називається також датою і часом Macintosh.
Спробуйте. Виберіть Файл → Параметри Excel → Обчислення (File → Parametres Excel → Calculation) та встановіть прапорець Система дат 1904 (1904 date system). В осередок А2 введіть 5:00:00 AM, в осередок A3 - 6:00:00 AM, в осередок А4 - = А2-А3. Ви отримаєте результат -1:00, але якщо виберете систему дат 1904, як описано раніше.
Якщо зміна системи дат Excel за промовчанням на систему дат 1904 може викликати проблеми в інших обчисленнях часу в робочій книзі, потрібен інший спосіб, який змінює вигляд осередків, що містять негативні значення.
Спосіб 2. Функція тексту (TEXT)
Другий спосіб вимагає використання функції тексту (TEXT). Для початку в осередку А2 введіть 5:00:00 AM, в осередку A3 - 6:00:00 AM. В осередку А4 введіть таку формулу: =ТЕХТ(МАХ($А$2:$А$3)-MIN($А$2:$А$3);"-Н::ММ") , в українській версії Excel=ТЕКСТ(МАКС($А$2:$А$3)-МІН($А$2:$А$3);"-Ч::ММ") .
Використовуючи цю вкладену формулу, ви віднімете A3 з А2, щоб отримати позитивне значення часу. Потім відформатуйте комірку за допомогою функції TEXT (TEXT), щоб вона могла відображати негативні значення часу. Комбінація функцій МАКС (МАХ) і МІН (MIN) гарантує, що раніше час завжди буде відніматися з пізнішого.
Спосіб 3. Користувальницький формат
Ще один спосіб відображення негативних значень часу без переходу до системи дат 1904 і з гарантованим поверненням числового значення - це використання формату користувача -ч:мм (-h:mm) в діалоговому вікні Головна → Формат → Формат комірок (Home → Format → Format Cells ).
Цей спосіб працює лише в тому випадку, якщо ви завжди хочете відображати лише негативні значення часу. Він вимагає також, щоб більш ранній час віднімали з пізнішого. Це означає, що насправді повертатимуться позитивні значення часу, але перед ними буде виводитися знак мінус.
Так, використовуючи один із цих трьох способів, ви зможете відображати негативні значення часу. Пам'ятайте, що свій підступ укладений у кожному способі, тому застосовуйте їх, не забуваючи про можливі підводні камені.