Расчет времени в Google Sheets
Теперь, когда мы узнали как ввести дату и время в электронную таблицу, пришло время поговорить о способах подсчета времени в Google Таблицах. Мы подробно обсудим способы нахождения разницы во времени, посмотрим, как суммировать даты и время, и научимся отображать только единицы даты или времени и полностью разделять их.
Как рассчитать разницу во времени в Google Sheets
Когда вы работаете над некоторыми проектами, обычно важно контролировать, сколько времени вы тратите. Это называется прошедшее время. Google Таблицы могут помочь вам рассчитать разницу во времени множеством различных способов.
Пример 1. Вычтите время, чтобы получить продолжительность времени в Google Sheets
Если у вас есть время начала и время окончания, не проблема узнать затраченное время:
= Время окончания – Время начала
Предположим, что время начала находится в столбце A, а время окончания — в столбце B. С помощью простой формулы вычитания в C2 вы найдете, сколько времени заняло то или иное задание:
=В2-А2
Время по умолчанию имеет формат «чч:мм».
Чтобы получить результаты только в виде часов или в виде часов, минут и секунд, вам необходимо применить собственный формат с соответствующими временными кодами: час а также чч:мм:сс. Google даже предлагает специальный числовой формат для таких случаев: Продолжительность:
Кончик. Чтобы применить пользовательский формат времени, перейдите к «Формат» > «Число» > «Дополнительные форматы» > «Пользовательский числовой формат» в меню электронной таблицы.
Пример 2. Расчет продолжительности времени в Google Таблицах с помощью функции ТЕКСТ
Еще один трюк для расчета продолжительности времени в Google Таблицах связан с функцией ТЕКСТ:
=ТЕКСТ(B2-A2;”ч”) – для часов
=ТЕКСТ(B2-A2;”ч:мм”) – для часов и минут
=ТЕКСТ(B2-A2;”ч:мм:сс”) – для часов, минут и секунд
Примечание. Видите, как записи выровнены по левому краю? Потому что функция ТЕКСТ всегда возвращает результаты в текстовом формате. Это означает, что эти значения нельзя использовать для дальнейших расчетов.
Пример 3. Разница во времени в часах, минутах и секундах
Вы можете отслеживать затраченное время и получать результат в одной единице времени, не считаясь с другими единицами. Например, подсчитайте количество только часов, только минут или только секунд.
Примечание. Чтобы обеспечить правильные результаты, ваши ячейки должны быть отформатированы либо как числа, либо автоматически: Формат > Число > Число или же Формат > Число > Автоматически.
Чтобы получить количество затраченных часов, вычтите время начала из времени окончания и умножьте результат на 24 (поскольку в сутках 24 часа):
=(Время окончания – Время начала) * 24
Вы получите разницу во времени в виде десятичной дроби:
Если время начала больше времени окончания, формула вернет отрицательное число, как в C5 в моем примере.
Кончик. Функция INT позволит вам увидеть количество полных потраченных часов, поскольку она округляет числа до ближайшего целого числа:
Чтобы подсчитать минуты, замените время начала на время окончания и умножьте полученное на 1440 (поскольку в одном дне 1440 минут):
=(Время окончания – Время начала) * 1440
Чтобы узнать, сколько секунд прошло между двумя временами, упражнение такое же: подставьте время начала из времени окончания и умножьте результат на 86 400 (количество секунд в сутках):
=(Время окончания – Время начала) * 86400
Кончик. Вы можете избежать умножения во всех этих случаях. Просто сначала вычтите время, а затем примените формат прошедшего времени из Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени. Если вы нажмете стрелку вниз справа от текстового поля, вы сможете выбрать между дополнительными единицами даты и времени:
Пример 4. Функции для получения разницы во времени в электронной таблице Google
Как всегда, Google Sheets предоставляет вам три особенно полезных функции для этой цели.
Примечание. Эти функции работают только в течение 24 часов 60 минут и секунд. Если разница во времени превышает эти пределы, формулы будут возвращать ошибки.
- =HOUR(B2-A2) – чтобы вернуть только часы (без минут и секунд)
- =МИНУТЫ(B2-A2) – вернуть только минуты (без часов и секунд)
- =SECOND(B2-A2) – вернуть только секунды (без часов и минут)
Как добавить и вычесть время в Google Sheets: часы, минуты или секунды
Эти операции также могут быть выполнены двумя методами: один включает в себя базовые математические вычисления, другой — функции. В то время как первый способ всегда работает, второй с функциями работает только тогда, когда вы добавляете или вычитаете единицы менее 24 часов, или 60 минут, или 60 секунд.
Добавить или вычесть часы в Google Sheets
Добавить менее 24 часов:
= Время начала + ВРЕМЯ (N часов, 0, 0)
Вот как формула выглядит на реальных данных:
=A2+ВРЕМЯ(3,0,0)
Добавить более 24 часов:
= Время начала + (N часов / 24)
Чтобы добавить 27 часов ко времени в A2, я использую эту формулу:
=А2+(27/24)
- Чтобы вычесть 24 и более часов, используйте приведенные выше формулы в качестве основы, но измените знак плюс (+) на знак минус (-). Вот что у меня есть:
=A2-TIME(3,0,0) – вычесть 3 часа
=A2-(27/24) – вычесть 27 часов
Добавить или вычесть минуты в Google Sheets
Принцип работы с минутами такой же, как и с часами.
Есть функция ВРЕМЯ, которая складывает и вычитает до 60 минут:
= Время начала + ВРЕМЯ (0, N минут, 0)
Если вы хотите добавить 40 минут, вы можете сделать это следующим образом:
=A2+ВРЕМЯ(0,40,0)
Если вы хотите вычесть 20 минут, используйте следующую формулу:
=A2-ВРЕМЯ(0,40,0)
И есть формула, основанная на простой арифметике для сложения и вычитания за 60 минут:
= Время начала + (N минут / 1440)
Таким образом, вот как вы добавляете 120 минут:
=А2+(120/1440)
Поставьте минус вместо плюса, чтобы вычесть 120 минут:
=А2-(120/1440)
Добавить или вычесть секунды в Google Sheets
Секунды в Google Таблицах рассчитываются так же, как часы и минуты.
Вы можете использовать функцию ВРЕМЯ, чтобы добавить или вычесть до 60 секунд:
= Время начала + ВРЕМЯ (0, 0, N секунд)
Например, добавьте 30 секунд:
=A2+ВРЕМЯ(0,0,30)
Или замените 30 секунд:
=A2-ВРЕМЯ(0,0,30)
Чтобы рассчитать более 60 секунд, используйте простую математику:
= Время начала + (N секунд / 86400)
Добавьте 700 секунд:
=А2+(700/86400)
Или замените 700 секунд:
=А2-(700/86400)
Как суммировать время в Google Sheets
Чтобы найти общее время в вашей таблице в Google Таблицах, вы можете использовать функцию СУММ. Хитрость здесь заключается в том, чтобы выбрать правильный формат для отображения результата.
По умолчанию результат будет отформатирован как Продолжительность – чч:мм:сс
Но чаще всего формата времени или продолжительности по умолчанию будет недостаточно, и вам нужно будет придумать свой собственный.
Ячейки A7:A9 содержат одинаковое значение времени. Они просто отображаются по-разному. И вы действительно можете выполнять с ними вычисления: вычитать, суммировать, преобразовывать в десятичное число и т. д.
Извлечь дату и время из полной записи «дата-время»
Давайте представим, что одна ячейка в Google Sheets содержит и дату, и время. Вы хотите разделить их: извлеките только дату в одну ячейку и только время в другую.
Разделить дату и время, используя числовой формат
Чтобы отобразить дату или время в одной ячейке на экране или распечатать их, просто выберите исходную ячейку, перейдите к Формат > Число и выбрать Свидание или же Время.
Однако, если вы хотите использовать эти значения для будущих вычислений (вычитание, суммирование и т. д.), этого будет недостаточно. Если вы не видите единицу времени в ячейке, это не обязательно означает, что ее нет, и наоборот.
Ну так что ты делаешь?
Разделить дату и время с помощью формул
Google хранит даты и время в виде чисел. Например, он видит дату 24.08.2017 11:40:03 как число 42971,4861458. Целая часть представляет дату, дробная – время. Итак, ваша задача сводится к тому, чтобы отделить целое число от дробного.
- Чтобы извлечь дату (целая часть), используйте функцию ОКРУГЛВНИЗ в ячейке B2:
=ОКРУГЛВНИЗ(A2,0)
Формула округляет значение в меньшую сторону и отбрасывает дробную часть.
Чтобы извлечь время, поместите следующую формулу вычитания в C2:
=А2-В2
Скопируйте результаты в третью строку и примените Свидание формат до B3 и Время форматировать в C3:
Используйте надстройку «Разделить дату и время»
Вы можете быть удивлены, но для этой работы есть одно специальное дополнение. Он действительно маленький и простой, но его вклад в Google Таблицы невозможно переоценить.
Разделить дату и время одновременно разделяет все записи даты и времени во всем столбце. Вы контролируете желаемый результат с помощью всего 4 простых настроек:
Вы говорите надстройке:
- Есть ли строка заголовка.
- Если вы хотите получить блок Date.
- Если вы хотите получить единицу времени.
- И если вы хотите заменить исходный столбец новыми данными.
Это буквально снимает с ваших плеч бремя разделения единиц даты и времени:
Надстройка является частью коллекции Power Tools, поэтому у вас под рукой будет более 30 других полезных надстроек. Установите его из магазина Google Sheets. все проверить.
Это способы не только отображать дату или время, но и разделять их по разным ячейкам. И с этими записями теперь можно выполнять различные вычисления.
Надеюсь, эти примеры помогут вам решить ваши задачи при работе с датами и временем в Google Sheets.
