Как конвертировать время и даты в Google Таблицах

Что важно знать в двух строках

• Внутренне время = доля дня (1 = 24 часа). Для перевода времени в минуты/секунды используйте CONVERT.
• Дата хранится как последовательное число от базовой даты; DATEVALUE выдаёт это число, TO_DATE — обратное преобразование.

Быстрое содержание

• Преобразование времени в часы/минуты/секунды
• Преобразование чисел в формат времени
• Преобразование дат в числовое значение и обратно
• Полезные приёмы, шаблоны и чек-листы
• Часто встречающиеся ошибки и как их исправить

Основная идея

Google Таблицы могут выполнять арифметику только над числами. Поэтому перед математикой дату или время нужно представить как число. После вычислений результат можно вернуть в привычный формат отображения.

Конвертация времени в часы, минуты и секунды

В Таблицах время хранится как доля суток. Полные 24 часа = 1. Любая часть суток — дробь от единицы. Значит, чтобы получить минуты или секунды, мы переводим «день» в нужную единицу.

Формат функции CONVERT:

=CONVERT(value, start_unit, end_unit)

CONVERT переводит значение value из единицы start_unit в end_unit и возвращает число. Google поддерживает перечисленные ниже временные единицы:

Пример: у нас есть длительность поездки в ячейке A2 (формат времени). Нужно получить минуты в C2.

Шаги:

1. Выделите ячейку C2.
2. В строке формул введите:

=CONVERT(A2, "day", "min")

3. Нажмите Enter. Формула преобразует долю дня (значение A2) в минуты.

Важно: если результат отображается как время (например 0:15:00), смените формат ячейки на «Число», чтобы увидеть количество минут как число. Если вы хотите получить часы, используйте “hr”; для секунд — “s” или “sec”.

Важно: CONVERT работает с числовыми значениями. Если в A2 текст или некорректный формат, получите ошибку. Преобразуйте текст в время через TIMEVALUE или используйте функцию TIME.

Пример применения: поездка грузовика

Допустим, A2 содержит время поездки в формате 01:23:45 (1 час 23 минуты 45 секунд). Формула выше вернёт общее количество минут (83.75), если A2 действительно хранится как время.

Если вы хотите целые минуты, используйте округление:

=ROUND(CONVERT(A2, "day", "min"), 0)

Если нужна точность до секунд, конвертируйте в секунды:

=CONVERT(A2, "day", "s")

Преобразование часов, минут и секунд в формат времени

Иногда у вас есть количество секунд или минут как число, и нужно получить привычный формат времени (чч:мм:сс). Подход — перевести число в доли дня и применить формат «Длительность» (Duration).

Пример: в A2 находится число секунд, которое нужно показать как время в C2.

1. Выберите целевую ячейку (C2).
2. В меню выберите Файл > Формат > Число > Длительность. (В русскоязычном интерфейсе: Формат → Число → Длительность)
3. В строке формул введите:

=CONVERT(A2, "s", "day")

4. Нажмите Enter. Таблица отобразит значение в виде часов:минут:секунд.

Альтернативный приём без CONVERT: если A2 — количество секунд, разделите на 86400 (число секунд в дне):

=A2/86400

Затем примените формат Длительность. Это часто быстрее и явнее, если вы знаете константу.

Конвертация дат в числовое значение и обратно

Google Таблицы хранят даты как последовательные числа, чтобы их можно было складывать и вычитать. Базовая точка (ноль) — это последняя дата 1899 года. Каждая следующая дата — целое число выше.

Функции:

=DATEVALUE(date)

Возвращает числовое значение даты.

=TO_DATE(datevalue)

Преобразует числовое значение обратно в формат даты.

Пример: перенос сроков проекта. В B2 стоит исходная дата, в D2 — задержка в днях.

1. В C2 получите числовое значение исходной даты:

=DATEVALUE(B2)

2. В E2 сложите старое числовое значение и задержку:

=C2 + D2

3. В F2 преобразуйте новое числовое значение в дату:

=TO_DATE(E2)

4. Скопируйте формулы вниз по столбцам.

Совет: вместо DATEVALUE можно использовать простую арифметику с датами. Например:

=B2 + D2

Если B2 — настоящая дата (а не текст), то к ней можно просто прибавить число дней. DATEVALUE нужен, когда дата представлена текстом.

Ментальные модели и эвристики

• Время = доля дня. Чтобы получить часы, умножьте на 24; минуты — на 1440; секунды — на 86400.
• Дата = целое число. Сложение/вычитание — добавление/удаление дней.
• Если видите странные дроби после операций с датой/временем — проверьте формат ячейки.

Быстрая формула-заметка:

• часы из времени: =A2*24
• минуты из времени: =A22460 или =CONVERT(A2, “day”, “min”)
• секунды из времени: =A2*86400 или =CONVERT(A2, “day”, “s”)

Когда это не сработает или ошибки

• Входной текст не распознан как дата/время → DATEVALUE/CONVERT вернёт ошибку.
• Зоны времени: Таблицы не учитывают смещение временных зон при арифметике дат, если вы используете локальную дату-время из внешнего источника. Проверьте источник данных.
• Отрицательные время/дата: при получении отрицательных значений формат «Длительность» может показывать ############ или некорректный формат. Используйте условную логику или отображайте вручную.
• Большие числа в CONVERT могут привести к потерям точности при округлении. Применяйте ROUND при необходимости.

Альтернативные подходы

• Функции HOUR(), MINUTE(), SECOND(). Они возвращают отдельные компоненты времени, но не дают суммарного количества минут/секунд.

=HOUR(A2)  // целое количество часов в временной метке
=MINUTE(A2)
=SECOND(A2)

• TIMEVALUE(text) — преобразует строку (например “13:45:30”) в внутреннее числовое значение времени.
• Прямое деление: для перевода секунд в дни используйте /86400.

Практическая шпаргалка (cheat sheet)

Ролевые чек-листы (кто и что должен проверять)

• Аналитик

  • Проверить формат ячеек (Дата/Время/Число).
  • Подготовить данные: даты должны быть «живыми» датами, не текстом.
  • Убедиться в единицах (секунды vs минуты) и при необходимости применять CONVERT.

• Разработчик отчётов

  • Автоматизировать преобразования в отдельных колонках.
  • Документировать используемые единицы (комментарий в шапке).

• Менеджер проекта

  • Проверить корректность расчётов сроков после применения DATEVALUE/TO_DATE.
  • Убедиться, что формат отображается для целевой аудитории (локаль, формат даты).

Проверка и приёмка результатов

Критерии приёмки:

• Результаты конвертации совпадают с контрольными примерами (ручной расчёт).
• Формат ячеек настроен так, чтобы конечный пользователь видел дату/время, а не число.
• Обработаны граничные случаи: пустые ячейки, текстовые даты, отрицательные интерваллы.

Отладка распространённых ошибок

• Ошибка #VALUE!: Вход не является допустимым числом/строкой даты. Примените VALUE() или DATEVALUE() к исходным данным.
• Появляется #######: ширина столбца слишком мала либо отрицательное время при формате Длительность. Увеличьте ширину или используйте текстовый формат.
• Неправильная локаль (месяц/день перепутаны): проверьте локальные настройки файла (Файл → Настройки) и формат даты.

Бонус: шаблон рабочего процесса (mini-SOP)

1. Очистите входные данные: уберите лишние пробелы, приведіть даты к единому формату.
2. В отдельном столбце вычислите числовые значения дат/времени (DATEVALUE / CONVERT / A2/86400).
3. Выполните необходимые операции (сложение/вычитание/средние и т.д.).
4. Преобразуйте результат обратно в видимый формат (TO_DATE / формат Длительность).
5. Проверьте на нескольких контрольных примерах.

Частые вопросы

Как получить целые часы из времени?

Используйте =INT(A2*24) или =HOUR(A2) + INT(MINUTE(A2)/60).

Можно ли конвертировать миллисекунды?

Да, сначала переведите миллисекунды в секунды (/1000), затем в дни (/86400) или используйте цепочку делений.

Что делать, если дата хранится как текст в нестандартном формате?

Преобразуйте текст: используйте DATE, LEFT/MID/RIGHT или функцию PARSEDATE в дополнениях. DATEVALUE работает только с распознаваемыми строками.

Краткое резюме

• CONVERT помогает переводить между временными единицами; не забывайте, что в Таблицах время — доля дня.
• DATEVALUE и TO_DATE дают вам удобный способ выполнять арифметику над датами.
• Всегда проверяйте формат ячеек и обрабатывайте граничные случаи (текстовые даты, отрицательные интервалы, локали).

Спасибо за чтение. Применяйте формулы в отдельных столбцах и документируйте единицы — это снизит количество ошибок при совместной работе.