Конвертация времени и дат в Google Sheets

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

В Google Sheets внутренняя модель времени и даты основана на числах. Время представляет собой долю суток (1 сутки = 1), а дата — порядковый номер дня в последовательной шкале. Благодаря этому можно выполнять арифметику: сначала конвертировать в число, проводить вычисления, затем вернуть формат времени или даты для удобного отображения.

Important: понимание, что 1 сутки = 1 и что в сутках 86 400 секунд, упрощает вычисления и позволяет предсказывать поведение функций при форматировании.

Что делает эта статья

Объясняет синтаксис CONVERT, DATEVALUE и TO_DATE.
Приводит практические примеры «шаг за шагом».
Разбирает распространённые ошибки и даёт альтернативы.
Даёт чеклист для разных ролей, тест-кейсы и шпаргалку с формулами.

Содержание

CONVERT: перевод времени между единицами
Пример: перевод продолжительности в минуты
Обратная задача: из секунд в формат времени
DATEVALUE и TO_DATE: даты ↔ числовые значения
Частые ошибки и ограничения
Альтернативные подходы и всё про форматирование
Шпаргалка: готовые формулы
Чек-лист, тесты и критерии приёмки
Краткие определения (глоссарий)
Итог

CONVERT: перевод времени между единицами

Формула:

=CONVERT(value, start_unit, end_unit)

CONVERT переводит значение value из единицы start_unit в единицу end_unit и возвращает число. Для работы с временем важно помнить: Google Sheets считает время как часть дня, поэтому при переводе времени используются единицы, связанные с днём.

Поддерживаемые временные единицы (варианты, которые полезны для времени):

| | Year | | “yr” | |

| | Day | | “day”, “d” | |

| | Hour | | “hr” | |

Примеры единиц: “day” → “min” переводит доли суток в минуты; “s” → “day” переводит секунды в доли суток.

Время в Google Sheets конвертируется в минуты.

Альтернативный способ: иногда удобно использовать простую арифметику без CONVERT (например, умножая время на 24 для получения часов). Оба подхода эквивалентны, но CONVERT делает формулу более наглядной.

Пример: перевод продолжительности в минуты (пошагово)

Сценарий: у вас в колонке A указано время поездки (в формате времени, например 1:30:00), нужно получить продолжительность в минутах.

Шаги:

Выберите ячейку для результата, например C2.
В строке формул введите:

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

Нажмите Enter — вы получите число минут.

Пояснение: значение в A2 — доля дня. CONVERT воспринимает “day” как единицу исходного значения и возвращает количество минут.

Замена единиц: вместо “min” можно указать “hr” или “s” — получим часы или секунды.

Совет: если вы хотите точность с минутами и секундами, используйте форматирование ячейки «Число» с нужным количеством десятичных знаков.

Обратная задача: из секунд в формат времени

Секунды преобразованы в формат времени в Sheets.

Задача: есть количество секунд (например, 125), нужно отобразить это как часы:минуты:секунды.

Подход с CONVERT:

Выберите ячейку C2 для результата.
Формат ячейки: Format → Number → Duration (Формат → Число → Продолжительность).
Введите формулу:

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

Нажмите Enter — число секунд будет показано как время (чч:мм:сс). Причина: формула возвращает долю дня, а формат «Продолжительность» отображает эту долю как часы/минуты/секунды.

Альтернатива: использовать формулу =A2/86400 и затем форматировать результат как Duration; это эквивалентно CONVERT(A2, “s”, “day”).

Даты в Google Sheets: числовые значения и обратное преобразование

Google Sheets хранит даты как последовательные числа: нулю соответствует последний день 1899 года, и каждый последующий день увеличивает счётчик на 1. Например, 1 января 1900 года — это 1. Такое представление позволяет выполнять арифметику с датами.

Функции:

=DATEVALUE(date)

возвращает числовое значение даты (порядковый номер).

=TO_DATE(datevalue)

преобразует числовое значение обратно в отображаемую дату.

Пустая таблица для демонстрации конверсии дат в Sheets.

Пример: у вас есть даты в колонке B (таргетные даты проекта) и количество дней задержки в колонке D. Нужно вычислить новые даты с учётом задержки.

Пошагово:

В C2 преобразуйте дату в число:

=DATEVALUE(B2)

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

=C2+D2

В F2 конвертируйте новый числовой результат в дату:

=TO_DATE(E2)

Протащите формулы вниз с помощью «ручки заполнения».

Результат: в колонке F будут новые целевые даты.

Важно: при копировании формул следите за форматированием ячеек (Date/Number). Если TO_DATE возвращает странный вид — проверьте локальные настройки формата даты.

Частые ошибки и когда методы не работают

Неправильный формат ячейки: если формула возвращает число, но вы ожидаете формат времени/даты, нужно менять формат через Format → Number.
Отрицательные длительности: стандартный формат времени не показывает отрицательные длительности. Для отрицательных промежутков используйте тексты или отдельную логику (IF + ABS + префикс “-“).
Время больше 24 часов: при форматировании как «Time» часы сверх 24 могут сбрасываться; используйте формат «Duration» для суммирования локальных часов (например, 27:30:00).
Локали: разные локали интерпретируют ввод дат по-разному (DD/MM/YYYY vs MM/DD/YYYY). Всегда проверяйте локальные настройки листа и используйте DATE(year,month,day) для явного создания даты.
Leap seconds: секунды высокоточной временной шкалы (вставляемые правительствами) не учитываются в Sheets — это цифровая модель, не астрономическая.

Альтернативные подходы и полезные функции

TIMEVALUE(text) — превращает строку времени в число (доля дня). Пример: =TIMEVALUE(“1:30:00”).
INT/ROUND — для извлечения целых дней: =INT(A2) даст целую часть даты-времени, =A2-INT(A2) даст только время.
TEXT(value, format) — для форматированного вывода, полезно в отчётах: =TEXT(A2, “hh:mm:ss”).
DATE(year,month,day) — создаёт дату явно из компонентов, полезно при разборе строк.

Выбор подхода:

Если нужен чистый перевод единиц — CONVERT или деление на 86400.
Если исходные данные — строки — сначала примените TIMEVALUE/DATEVALUE.
Для отображения длительностей >24 часов — используйте формат Duration.

Шпаргалка: готовые формулы (cheat sheet)

Секунды → время (чч:мм:сс):
```
=CONVERT(A2, "s", "day")
```
и форматировать как Duration.
Время → минуты:
```
=CONVERT(A2, "day", "min")
```
Время → часы:
```
=CONVERT(A2, "day", "hr")
```
Дата → числовое значение:
```
=DATEVALUE(B2)
```
Числовое значение → дата:
```
=TO_DATE(E2)
```
Добавить дни к дате (без промежуточной DATEVALUE):
```
=B2 + D2
```
(работает, если B2 — дата, D2 — количество дней)
Создать дату из компонентов:
```
=DATE(2025, 12, 31)
```
Извлечь только время из datetime:
```
=A2 - INT(A2)
```

Ролебейз чек-листы (быстрая проверка)

Аналитик:

Убедиться, что исходные колонки имеют правильный формат (Date/Time/Number).
Использовать DATEVALUE/TIMEVALUE для конвертации строк.
Проверить граничные значения: 0, 1, >24ч.

Разработчик/инженер данных:

Автоматизировать форматирование через скрипт или шаблон.
Добавить валидацию входных данных (регулярные выражения для дат).
Логировать ошибки преобразования.

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

Проверить, что финальный отчёт показывает длительности в понятной единице.
Попросить эксперта проверить локальный формат дат/часов перед публикацией.

Тест-кейсы и критерии приёмки

TC1: Ввод 1:30:00 → CONVERT(A2,”day”,”min”) → 90 (минут).
Критерий приёмки: возвращаемое число = 90 ± 0.001.
TC2: Ввод 125 (секунд) → формула =CONVERT(A2,”s”,”day”) + формат Duration → отображается 00:02:05.
Критерий: формат показывает 0ч 2м 5с.
TC3: Дата 2025-12-31 + 5 дней → B2+D2 → 2026-01-05.
Критерий: корректное смещение даты с учётом перехода на новый год.
TC4: Ввод строки “01/02/2025” в локали DD/MM/YYYY → DATEVALUE корректно парсит в числовое значение.
Критерий: при изменении локали в настройках листа результат остаётся корректным либо даёт предсказуемую ошибку.

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

Представляйте дату как целую часть числа, а время — как дробную.
Для переводов между единицами времени всегда сверяйтесь с сутками: 1 день = 24 часа = 1440 минут = 86 400 секунд.
При суммировании длительностей используйте формат Duration, при суммировании дат — Date.

Мини-методология для работы с датами и временем в Sheets

Нормализуйте вход: приведите строки к ISO или используйте DATE/TIME-конструкторы.
Конвертируйте в числа (DATEVALUE/TIMEVALUE/CONVERT) если нужны расчёты.
Выполните арифметику (сложение, вычитание, агрегирование).
Верните формат отображения (TO_DATE или формат Duration/TEXT).
Протестируйте на крайних значениях и с другими локалями.

Безопасность и приватность

Работая с датами/временем, особых рисков безопасности нет, если только данные не содержат персональную информацию. В случае PII — соблюдайте корпоративные правила хранения и обмена данными.

Краткий глоссарий (1 строка каждый)

CONVERT — переводит число из одних единиц измерения в другие.
DATEVALUE — возвращает числовое значение даты.
TO_DATE — отображает числовое значение как дату.
TIMEVALUE — преобразует текстовое время в число (долю дня).
Duration — формат отображения длительности, поддерживает часы >24.

Decision flowchart (простое дерево выбора)

flowchart TD
  A[Есть время или дата?] -->|Время| B{Формат времени}
  A -->|Дата| C{Нужно вычислять?}
  B -->|Строка| D[TIMEVALUE]
  B -->|Явный time| E[CONVERT или A2*24]
  C -->|Да| F[DATEVALUE → арифметика → TO_DATE]
  C -->|Нет| G[Оставить как есть]

Итог

Вы научились: переводить время между секундами, минутами и часами с помощью CONVERT; преобразовывать даты в числовые значения и обратно через DATEVALUE и TO_DATE; и корректно оформлять результаты с учётом локалей и форматов. Используйте приведённую методологию и шпаргалку для надёжных расчётов.

Пример конверсии дат в таблице Google Sheets.