Вычитание даты и времени из журналов консоли администратора Google

#google-sheets

Вопрос:

Администратор Google экспортирует следующие метки даты и времени, которые не распознаются листами. Как я могу заставить листы распознавать их как формат даты? Итак, я могу завершить вычитание по ним, например, «3 февраля 2020 года, 2:00:29 вечера по Гринвичу» минус «2 февраля 2020 года, 1:00:09 вечера по Гринвичу».

1. Здравствуйте, не могли бы вы уточнить, нужна ли вам эта разница в днях, минутах и т.д.?

2. Спасибо за вопрос, разница в годах, месяцах, днях, часах, минутах, секундах была бы отличной. Вопрос, по сути, в том, как заставить листы распознавать формат.

Ответ №1:

Обходной путь

Я буду использовать эти даты в качестве примера 3 февраля 2020 года, 2:00:29 вечера по Гринвичу и 2 февраля 2020 года, 3:00:09 вечера по Гринвичу

На данный момент нет функции листа, которая обрабатывает часовой пояс, поэтому вот мой подход:

Сначала разделите дни вот так:

 =TEXT(INDEX(SPLIT(A2, ","),1 ,1), "mmm d, ") amp; INDEX(SPLIT(A2, ","),1 ,2)

А также часы работы:

 =SUBSTITUTE(INDEX(SPLIT(A5, ","), 1, 3), "GMT", "")

Затем просто рассчитайте разницу в часах:

Разница в датах в часах

 =DATEVALUE(TEXT(INDEX(SPLIT(A2, ","),1 ,1), "mmm d, ") amp; INDEX(SPLIT(A2, ","),1 ,2)) - 
 DATEVALUE(TEXT(INDEX(SPLIT(B2, ","),1 ,1), "mmm d, ") amp; INDEX(SPLIT(B2, ","),1 ,2))

Значение ДАТЫ Преобразует предоставленную строку даты в известном формате в значение даты, которое является числом, которое мы можем обработать.

ПРИМЕЧАНИЕ: Функция ДНЕЙ также будет обрабатывать високосные годы, поэтому вы можете использовать ее вместо разницы в значениях ДАТЫ

Разница в часах

 =TIMEVALUE(SUBSTITUTE(INDEX(SPLIT(A2, ","), 1, 3), "GMT", "")) - 
 TIMEVALUE(SUBSTITUTE(INDEX(SPLIT(B2, ","), 1, 3), "GMT", ""))

Значение ВРЕМЕНИ вернет долю 24-часового дня, которая может быть отрицательной, я не буду использовать =ABS функцию, потому что хочу добавить это значение к разнице дат, поэтому, добавив это отрицательное значение, мы сделаем реальную разницу между 2 датами.

Как только у нас будут оба значения, мы просто добавим их, чтобы получить разницу в часах:

 =( DATEVALUE(TEXT(INDEX(SPLIT(A2, ","),1 ,1), "mmm d, ") amp; INDEX(SPLIT(A2, ","),1 ,2)) - 
 DATEVALUE(TEXT(INDEX(SPLIT(B2, ","),1 ,1), "mmm d, ") amp; INDEX(SPLIT(B2, ","),1 ,2)) ) * 24
  
 (TIMEVALUE(SUBSTITUTE(INDEX(SPLIT(A2, ","), 1, 3), "GMT", "")) - 
 TIMEVALUE(SUBSTITUTE(INDEX(SPLIT(B2, ","), 1, 3), "GMT", "")) ) * 24

И мы можем извлекать различные форматы, такие как часы или минуты, имея в виду, что ЗНАЧЕНИЕ ДАТЫ и ВРЕМЕНИ должно быть умножено на 24.