Оскільки дати та час зберігаються як числа у зворотному кінці в Excel, ви можете легко використовувати прості арифметичні операції та формули над значеннями дати та часу.
Наприклад, ви можете додати два різних значення часу або значення дати або обчислити різницю в часі між двома датами/часами.
У цьому посібнику я покажу вам кілька способів виконання обчислень за допомогою часу в Excel (наприклад, обчислення різниці у часі, додавання або віднімання часу, відображення часу у різних форматах та виконання суми значень часу)
Як Excel обробляє дату та час?
Як я вже згадував, дати та час зберігаються як числа у комірці в Excel. Ціле число являє собою повний день, а десяткова частина числа - частину дня (яку можна перетворити на значення годин, хвилин та секунд)
Наприклад, значення 1 позначає 01 січня 1900 року в Excel, що є початковою точкою, з якої Excel починає розглядати дати.
Отже, 2 означатиме 02 січня 1990 р., 3 означатиме 03 січня 1900 р. Тощо, а 44197 означатиме 01 січня 2021 р.
Примітка. Excel для Windows та Excel для Mac дотримуються різних дат початку. 1 в Excel для Windows означатиме 1 січня 1900 року, а 1 в Excel для Mac означатиме 1 січня 1904 року
Якщо в цих числах є цифри після десяткової коми, Excel вважатиме їх частиною дня, і їх можна перетворити на години, хвилини та секунди.
Наприклад, 44197,5 означатиме 01 січня 2021 року о 12:00:00.
Отже, якщо ви працюєте зі значеннями часу в Excel, ви б по суті працювали з десятковою частиною числа.
Excel дає вам можливість гнучко конвертувати цю десяткову частину у різні формати, наприклад, лише години, лише хвилини, лише секунди або комбінація годин, хвилин та секунд
Тепер, коли ви зрозуміли, як зберігається час в Excel, давайте подивимося на деякі приклади того, як обчислити різницю в часі між двома різними датами чи часами в Excel
Формули для розрахунку різниці в часі між двома часами
У багатьох випадках все, що вам потрібно зробити, це дізнатися загальний час, що пройшов між дворазовими значеннями (наприклад, у випадку табеля обліку робочого часу, який містить час та час виходу).
Вибраний вами метод залежатиме від того, як час згадується у комірці та у якому форматі ви хочете отримати результат.
Давайте розглянемо пару прикладів
Просте віднімання розрахунку різниці в часі в Excel
Оскільки час зберігається як число в Excel, знайдіть різницю між 2 значеннями часу, ви можете легко відняти час початку від часу закінчення.
Час завершення - Час початку
Результатом віднімання також буде десяткове значення, яке представлятиме час, що пройшов між двома значеннями часу.
Нижче наведено приклад, коли я маю час початку та закінчення, і я обчислив різницю в часі простим відніманням.
Існує ймовірність, що ваші результати відображатимуться у форматі часу (замість десяткових чи у значеннях годин/хвилин). У нашому прикладі вище результат у клітинці С2 показує 09:30 замість 9,5.
Це чудово, оскільки Excel намагається скопіювати формат із сусіднього стовпця.
Щоб перетворити це на десятковий, змініть формат клітинок на Загальний (ця опція є на вкладці «Домашня сторінка» у групі «Числа»)
Отримавши результат, ви можете відформатувати його різними способами. Наприклад, ви можете показати значення лише в годинах або лише хвилинах або комбінації годин, хвилин та секунд.
Нижче наведені різні формати, які можна використовувати:
| Формат | Що робить |
| h | Показує лише години, що пройшли між двома датами |
| чх | Відображає години з двозначними цифрами (наприклад, 04 або 12) |
| hh: мм | Показує години та хвилини, що пройшли між двома датами, наприклад 10:20 |
| чч: мм: сс | Показує години, хвилини та секунди, що минули між двома датами, наприклад 10:20:36 |
І якщо вам цікаво, де і як застосувати ці спеціальні формати дати, виконайте наведені нижче дії.
- Виберіть клітинки, до яких потрібно застосувати формат дати
- Утримуючи клавішу Control, натисніть клавішу 1 (або Command + 1, якщо використовується Mac)
- У діалоговому вікні «Форматування клітинок» натисніть вкладку «Номер» (якщо ще не вибрано)
- На лівій панелі натисніть «Спеціальне»
- Введіть будь -який код потрібного формату в поле Тип (у цьому прикладі я використовую hh: mm: ss)
- Натисніть OK
Наведені вище кроки змінять форматування та покажуть вам значення залежно від формату.
Зауважте, що форматування користувацького числа не змінює значення в комірці. Це лише змінює спосіб відображення значення. Отже, я можу вибрати, щоб у клітинці відображалося лише значення години, тоді як воно все ще матиме початкове значення.
Професійна порада: Якщо загальна кількість годин перевищує 24 години, використовуйте замість цього такий формат спеціального числа: [чч]: мм: сс
Обчисліть різницю в годинах, хвилинах або секундах
Коли ви віднімаєте значення часу, Excel повертає десяткове число, яке представляє отриману різницю в часі.
Оскільки кожне ціле число являє собою один день, десяткова частина числа представлятиме таку більшу частину дня, яку можна легко перетворити на години, хвилини або секунди.
Розрахунок різниці в годинах
Припустимо, у вас є набір даних, як показано нижче, і ви хочете обчислити кількість годин між дворазовими значеннями
Нижче наведена формула, яка дасть вам різницю в годинах:
= (B2-A2)*24
Наведена вище формула дасть вам загальну кількість годин, що пройшли між дворазовими значеннями.
Іноді Excel намагається бути корисним і також дасть вам результат у форматі часу (як показано нижче).
Ви можете легко перетворити цей формат у числовий, натиснувши вкладку Домашня сторінка, а в групі Число, вибравши формат Число.
Якщо ви хочете лише повернути загальну кількість годин, що пройшли між двома часами (без десяткової частини), використовуйте формулу нижче:
= INT ((B2-A2)*24)
Примітка: Ця формула працює лише тоді, коли обидва значення часу однакові. Якщо день змінюється (коли одне із значень часу - іншої дати, а друге - іншої дати), ця формула дасть неправильні результати. Подивіться на розділ, де я висвітлю формулу для обчислення різниці в часі, коли дата змінюється пізніше в цьому підручнику.
Розрахунок різниці в хвилинах
Щоб обчислити різницю в хвилинах, потрібно отримане значення помножити на загальну кількість хвилин за день (це 1440 або 24*60).
Припустимо, у вас є набір даних, як показано нижче, і ви хочете обчислити загальну кількість хвилин, що пройшли між початковою та кінцевою датою.
Нижче наведена формула, яка дозволить це зробити:
= (B2-A2)*24*60
Розрахунок різниці в часі в секундах
Щоб обчислити різницю в часі в секундах, потрібно помножити отримане значення на загальну кількість секунд за день (що становить або 24*60*60 або 86400).
Припустимо, у вас є набір даних, як показано нижче, і ви хочете обчислити загальну кількість секунд, що минули між початковою та кінцевою датою.
Нижче наведена формула, яка дозволить це зробити:
= (B2-A2)*24*60*60
Обчислення різниці в часі за допомогою функції TEXT
Ще один простий спосіб швидко отримати різницю в часі, не турбуючись про зміну формату, - це скористатися функцією ТЕКСТ.
Функція TEXT дозволяє вказати формат прямо у формулі.
= ТЕКСТ (Дата завершення - Дата початку, Формат)
Перший аргумент - це обчислення, яке ви хочете зробити, а другий - формат, у якому ви хочете показати результат обчислення.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете обчислити різницю в часі між двома часами.
Ось деякі формули, які дадуть вам результат із різними форматами
Показати лише кількість годин:
= ТЕКСТ (B2-A2, "hh")
Наведена вище формула дасть вам лише результат, який показує кількість годин, що пройшли між дворазовими значеннями. Якщо ваш результат 9 годин і 30 хвилин, він все одно покаже вам лише 9.
Покажіть загальну кількість хвилин
= ТЕКСТ (B2-A2, "[мм]")
Показати загальну кількість секунд
= ТЕКСТ (B2-A2, "[ss]")
Показувати години та хвилини
= ТЕКСТ (B2-A2, "[hh]: мм")
Показувати години, хвилини та секунди
= ТЕКСТ (B2-A2, "hh: mm: ss")
Якщо вам цікаво, у чому різниця між hh та [hh] у форматі (або mm та [mm]), під час використання квадратних дужок це дасть вам загальну кількість годин між двома датами, навіть якщо значення години більше, ніж 24. Отже, якщо відняти два значення дати, де різниця становить більше 24 годин, використання [hh] дасть вам загальну кількість годин, а hh дасть вам лише години, що минули на день дати завершення .
Отримайте різницю в часі в одиницях (години/хвилини) та ігноруйте інші
Якщо ви хочете обчислити різницю в часі між двома значеннями часу лише кількістю годин, хвилин або секунд, то ви можете скористатися виділеними функціями ГОДИНА, ХВИЛИНА чи ДРУГИЙ.
Кожна з цих функцій приймає один єдиний аргумент, який є значенням часу, і повертає вказану одиницю часу.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете обчислити загальну кількість годин, хвилин і секунд, що минули між цими двома часами.
Нижче наведені формули для цього:
Розрахунок годин, що минули між двома часами
= ГОДИНА (B2-A2)
Обчислення хвилин з результату значення часу (без урахування відпрацьованих годин)
= ХВИЛИНА (B2-A2)
Обчислення секунд з результату значення часу (без урахування завершених годин і хвилин)
= ДРУГИЙ (B2-A2)
Кілька речей, які вам потрібно знати, працюючи з цими формулами HOURS, MINUTE і SECOND:
- Різниця між часом завершення та часом початку не може бути негативною (що часто буває при зміні дати). У таких випадках ці формули повертають #NUM! помилка
- Ці формули використовують лише часову частину отриманого значення часу (і ігнорують денну частину). Отже, якщо різниця між часом закінчення та початком становить 2 дні, 10 годин, 32 хвилини та 44 секунди, формула HOUR дасть 10, формула MINUTE дасть 32, а друга формула дасть 44
Розрахувати час, що минув до цього часу (від часу початку)
Якщо ви хочете обчислити загальний час, що минув між початковим та поточним часом, можна скористатися формулою ЗАРАЗ замість часу завершення.
Функція NOW повертає поточну дату та час у комірці, в якій вона використовується. Це одна з тих функцій, яка не приймає жодного вхідного аргументу.
Отже, якщо ви хочете обчислити загальний час, що минув між початковим часом і поточним часом, ви можете скористатися формулою нижче:
= ЗАРАЗ () - час початку
Нижче наведено приклад, коли я маю час початку у стовпці А, а час, що минув досі, у стовпці В.
Якщо різниця у часі між початковою датою та часом та поточним часом становить більше 24 годин, то ви можете відформатувати результат, щоб відобразити день, а також частину часу.
Ви можете зробити це, використовуючи формулу ТЕКСТ нижче:
= ТЕКСТ (ЗАРАЗ ()-A2, "dd hh: ss: mm")
Ви також можете досягти того самого, змінивши користувацьке форматування комірки (як описано раніше в цьому посібнику), щоб вона відображала день, а також частину часу.
Якщо ваш час початку містить лише частину часу, то Excel вважатиме це часом 1 січня 1990 року.
У цьому випадку, якщо ви використовуєте функцію ЗАРАЗ для розрахунку часу, що минув до цього часу, це дасть вам неправильний результат (оскільки отримане значення також матиме загальну кількість днів, що минули з 1 січня 1990 року).
У такому випадку можна скористатися формулою нижче:
= NOW ()- INT (NOW ())- A2
У наведеній вище формулі використовується функція INT для видалення денної частини зі значення, яке повертає функція now, і це потім використовується для обчислення різниці в часі.
Зауважте, що NOW-це мінлива функція, яка оновлюється щоразу, коли змінюється аркуш, але не оновлюється в режимі реального часу
Розрахувати час при зміні дати (обчислити та відобразити від’ємні часи в Excel)
Методи, розглянуті досі, добре працюють, якщо час завершення пізніше часу початку.
Але проблема виникає, коли ваш час завершення нижчий за час початку. Це часто трапляється, коли ви заповнюєте табелі обліку робочого часу, де вводите лише час, а не всю дату та час.
У таких випадках, якщо ви працюєте в одну нічну зміну і дата змінюється, існує ймовірність, що час завершення буде раніше часу початку.
Наприклад, якщо ви починаєте свою роботу о 18:00 ввечері, а завершуєте свою роботу та час очікування о 9:00 ранку.
Якщо ви працюєте лише зі значеннями часу, то віднімання часу початку від часу закінчення дасть вам від’ємне значення 9 годин (9-18).
І Excel не може обробляти негативні значення часу (і в цьому питанні, ні люди, якщо ви не можете подорожувати в часі)
У таких випадках вам потрібен спосіб з'ясувати, що день змінився, і розрахунок слід зробити відповідно.
На щастя, для цього є дійсно просте виправлення.
Припустимо, у вас є набір даних, як показано нижче, де я маю час початку та час завершення.
Як ви помітили, що іноді час початку - ввечері, а час закінчення - вранці (що означає, що це була нічна зміна, і день змінився).
Якщо я використовую наведену нижче формулу для обчислення різниці в часі, вона покаже мені знаки хешування у клітинках, де результат є від’ємним значенням (виділено жовтим кольором на зображенні нижче).
= В2-А2
Ось формула IF, незалежно від того, чи є значення різниці в часі від'ємним чи ні, і якщо воно від'ємне, воно повертає правильний результат
= IF ((B2-A2) <0,1- (A2-B2), (B2-A2))
Хоча це добре працює в більшості випадків, воно все одно не вистачає на випадок, якщо час початку та час закінчення складають більше 24 годин. Наприклад, хтось увійде о 9:00 ранку 1-го дня та вийде о 11:00 2-го дня.
Оскільки це більше 24 годин, неможливо дізнатися, чи вийшла людина через 2 години або через 26 годин.
Хоча найкращим способом вирішення цього питання буде переконатися, що записи включають дату, а також час, але якщо це лише час, з яким ви працюєте, то вищезгадана формула повинна вирішити більшість проблем ( враховуючи, що навряд чи хтось працюватиме більше 24 годин)
Додавання/ віднімання часу в Excel
Поки що ми бачили приклади, коли у нас був час початку та закінчення, і нам потрібно було знайти різницю в часі.
Excel також дозволяє легко додавати або віднімати фіксоване значення часу з існуючого значення дати та часу.
Наприклад, припустимо, у вас є список завдань у черзі, де кожне завдання займає визначений час, і ви хочете знати, коли кожне завдання закінчиться.
У такому випадку ви можете легко додати час, який збиратиметься до виконання кожного завдання, до часу початку, щоб знати, в який час очікується виконання завдання.
Оскільки Excel зберігає значення дати та часу як цифри, ви повинні переконатися, що час, який ви намагаєтесь додати, відповідає формату, якого Excel вже дотримується.
Наприклад, якщо ви додасте 1 до дати в Excel, вона дасть вам наступну дату. Це пояснюється тим, що 1 представляє цілий день в Excel (що дорівнює 24 годинам).
Тому, якщо ви хочете додати 1 годину до існуючого значення часу, ви не можете продовжувати і просто додати 1 до нього. Ви повинні переконатися, що ви перетворили це значення години на десяткову частину, яка представляє одну годину. те ж саме стосується додавання хвилин і секунд.
Використання функції TIME
Функція часу в Excel бере значення годин, хвилини та секунди і перетворює їх на десяткове число, яке представляє цей час.
Наприклад, якщо я хочу додати 4 години до існуючого часу, я можу використати формулу нижче:
= Час початку + час (4,0,0)
Це корисно, якщо ви знаєте, скільки годин, хвилин і секунд ви хочете додати до існуючого часу і просто використовуєте функцію TIME, не турбуючись про правильне перетворення часу в десяткове значення.
Також зверніть увагу, що функція TIME враховуватиме лише цілу частину введеного значення години, хвилини та секунди. Наприклад, якщо я використовую 5,5 годин у функції TIME, це додасть лише п’ять годин і проігнорує десяткову частину.
Також зверніть увагу, що функція TIME може додавати лише значення, менші за 24 години. Якщо значення вашої години більше 24, це дасть вам неправильний результат.
Те саме стосується хвилин та другої частини, де функція враховуватиме лише значення, менші за 60 хвилин і 60 секунд
Так само, як я додав час за допомогою функції TIME, ви також можете відняти час. Просто змініть знак + на мінус у наведених вище формулах
Використання базової арифметики
Коли функція часу проста і зручна у використанні, вона має деякі обмеження (як описано вище).
Якщо ви хочете отримати більше контролю, ви можете скористатися арифметичним методом, який я розгляну тут.
Концепція проста - перетворіть значення часу в десяткове значення, яке представляє частину дня, а потім ви можете додати його до будь -якого значення часу в Excel.
Наприклад, якщо ви хочете додати 24 години до існуючого значення часу, ви можете скористатися формулою нижче:
= Start_time + 24/24
Це просто означає, що я додаю один день до існуючого значення часу.
Тепер, просуваючи ту ж концепцію, скажімо, ви хочете додати 30 годин до значення часу, ви можете скористатися формулою нижче:
= Start_time + 30/24
Наведена вище формула робить те ж саме, де ціла частина (30/24) представляє загальну кількість днів за час, який потрібно додати, а десяткова частина представлятиме години/хвилини/секунди
Так само, якщо у вас є певна кількість хвилин, яку ви хочете додати до значення часу, ви можете скористатися формулою нижче:
= Start_time + (хвилини для додавання)/24*60
І якщо у вас є кількість секунд, яку ви хочете додати, то ви можете використовувати формулу нижче:
= Start_time + (хвилини для додавання)/24*60*60
Хоча цей метод не такий простий, як використання функції часу, я вважаю його набагато кращим, оскільки він працює у всіх ситуаціях і слідує тій же концепції. на відміну від функції часу, вам не потрібно турбуватися про те, чи час, який ви хочете додати, становить менше 24 годин або більше ніж 24 години
Ви можете слідувати тій же концепції, віднімаючи час. Просто змініть знак + на мінус у формулах вище
Як підсумувати час в Excel
Іноді вам може знадобитися швидко скласти всі значення часу в Excel. Додавання кількох значень часу в Excel досить просте (потрібна проста формула SUM)
Але є кілька речей, які вам потрібно знати, коли ви додаєте час у Excel, зокрема формат комірки, який покаже вам результат.
Давайте розглянемо приклад.
Нижче у мене є список завдань разом із часом, який кожне завдання займе у стовпці В, і я хочу швидко додати ці терміни та дізнатися загальний час, який витрачається на виконання всіх цих завдань.
У комірці В9 я використав просту формулу SUM для обчислення загального часу, на який займуть усі ці завдання, і вона дає мені значення як 18:30 (це означає, що на виконання знадобиться 18 годин 20 хвилин) всі ці завдання)
Поки що все добре!
Як підсумувати за 24 години в Excel
Тепер подивіться, що станеться, коли я зміню час виконання завдання 2 для виконання від 1 години до 10 годин.
Результат тепер говорить 03:20, а це означає, що для виконання всіх цих завдань знадобиться 3 години 20 хвилин.
Це неправильно (очевидно)
Проблема тут не в тому, що Excel зіпсував. Проблема тут у тому, що комірка відформатована таким чином, що відображатиме лише часову частину отриманого значення.
Оскільки отримане значення тут становить більше 24 годин, Excel вирішив перетворити 24 -годинну частину на день, видаливши її зі значення, яке відображається користувачеві, і відображатиме лише залишкові години, хвилини та секунди.
На щастя, це легко виправити.
Все, що вам потрібно зробити, це змінити формат комірки, щоб вона показувала години, навіть якщо вона перевищує 24 години.
Нижче наведено деякі формати, які можна використовувати:
| Формат | Очікуваний результат |
| [год]: мм | 28:30 |
| [м]: сс | 1710:00 |
| d “D” hh: мм | 1 Д 04:30 |
| d “D” hh “Min” ss “Sec” | 1 D 04 хв. 00 сек |
| d “День” hh “Хвилина” ss “Секунди” | 1 день 04 хвилина 00 секунди |
Ви можете змінити формат, перейшовши у діалогове вікно форматування клітинок і застосувавши власний формат, або скористайтеся функцією ТЕКСТ та використовуйте будь -який із зазначених вище форматів у самій формулі
Ви можете скористатися формулою ТЕКСТ нижче, щоб показати час, навіть якщо він перевищує 24 години:
= ТЕКСТ (SUM (B2: B7), "[h]: mm: ss")
або формулу нижче, якщо ви хочете перевести години, що перевищують 24 години, на дні:
= ТЕКСТ (SUM (B2: B7), "dd hh: mm: ss")
Результати показують хеш (###) замість дати/часу (причини + виправлення)
У деяких випадках ви можете виявити, що замість того, щоб показувати вам значення часу, Excel відображає символи хеш у клітинці.
Ось деякі можливі причини та способи їх усунення:
Колонка недостатньо широка
Якщо комірка не має достатньо місця для відображення повної дати, вона може відображати символи хешу.
У ньому є легке виправлення - змініть ширину стовпця та зробіть його ширшим.
Негативне значення дати
Значення дати чи часу не може бути від'ємним у Excel. у випадку, якщо ви обчислюєте різницю в часі, і вона виявиться негативною, Excel покаже вам символи хешу.
Спосіб виправлення змінює формулу, щоб дати правильний результат. Наприклад, якщо ви обчислюєте різницю в часі між двома часами та зміною дати, вам потрібно відкоригувати формулу для її врахування.
В інших випадках ви можете скористатися функцією ABS, щоб перетворити від’ємне значення часу в додатне число, щоб воно відображалося правильно. Крім того, ви також можете скористатися формулою IF, щоб перевірити, чи є результат негативним значенням, і повернути щось більш значуще.
У цьому посібнику я розглянув теми про обчислення часу в Excel (де можна обчислити різницю в часі, додати або відняти час, показати час у різних форматах та підсумувати значення часу)
Сподіваюся, цей підручник був вам корисним.
