ISO-8601 String to Date в ячейке Google Таблиц

21

У меня есть строка из отформатированных строк ISO-8601 в столбце моего листа. Как я могу получить листы Google, чтобы рассматривать их как Даты, чтобы я мог выполнять математику (например, разница между двумя ячейками)? Я пробовал только =Date("2015-05-27T01:15:00.000Z") , но без радости. Должен быть простой способ сделать это. Любые советы?

    
задан Bob Kuhar 27.05.2015 в 22:47
источник

3 ответа

12

Попробуйте это

=CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),1),"yyyy-mm-dd")," ",TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),2),"hh:mm:ss"))

Где A1 может быть ячейкой с форматированной строкой ISO-8601 или самой строкой.

    
ответ дан Akshin Jalilov 28.05.2015 в 08:00
источник
  • Хммм. Это не выглядит «легко», но я дам ему завихрение. Я действительно надеялся, что для этого уже есть одна функция. –  Bob Kuhar 28.05.2015 в 08:18
  • Это решение возвращает строку, а не тип даты, которую хотел использовать @BobKuhar. Когда я тестировал его со строкой времени ISO 8601, 2016-07-26T11: 12: 06.711-0400, результат был 2016-07-26 11: 12: 06.711-0400. В качестве строки времени GMT (без смещения временной зоны), 2016-07-26T11: 12: 06.711Z стал 2016-07-26 11:12:07. Это решение просто переформатирует строку ISO 8601. –  L S 26.07.2016 в 17:25
  • Вы должны разделить результат, чтобы разбить его на «2016-07-26» и «11:12:07». Эти две ячейки будут отформатированы как дата и время. –  Akshin Jalilov 28.07.2016 в 06:38
29

Чтобы получить фактическое значение даты, которое вы можете форматировать с использованием стандартного форматирования чисел ...

=DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8))

например.

╔═══╦══════════════════════╦════════════════════╗
║   ║          A           ║          B         ║
╠═══╬══════════════════════╬════════════════════╣
║ 1 ║ 2016-02-22T05:03:21Z ║ 2/22/16 5:03:21 AM ║
╚═══╩══════════════════════╩════════════════════╝
  • Предполагает отметки времени в UTC
  • Игнорирует миллисекунды (хотя вы можете добавить достаточно легко)

Функция DATEVALUE() превращает форматированную строку даты в значение, а TIMEVALUE() делает то же самое в разы. В большинстве таблиц дат & amp; времена представлены числом, где целая часть - дни с 1 января 1900 года, а десятичная часть - это время как часть дня. Например, 11 июня 2009 года 17:30 составляет около 39975,72917.

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

    
ответ дан rcoup 23.03.2016 в 12:01
источник
  • Это решение хорошее. Это лучше, чем принятое. По крайней мере, это возвращает числовое значение Date (Time), а не строку. Я заметил, что если дается смещение часового пояса, оно просто игнорируется. Кроме того, предположение о позициях символов в строке делает это решение коротким и, вероятно, быстрее, чем несколько вызовов подстановки и разделения функций. –  L S 26.07.2016 в 17:29
  • вы можете просто добавить tz в конец. ... + (10/24) где 10 - смещение –  Sam 17.08.2016 в 05:44
10

Мне было гораздо проще использовать =SUM(SPLIT(A2,"TZ"))

Отформатируйте yyyy-MM-dd HH:mm:ss.000 , чтобы снова увидеть значение даты как ISO-8601.

    
ответ дан Chris Bandy 12.05.2016 в 23:14
источник
  • Это решение неплохое. Он возвращает числовое значение Date (Time) в соответствии с запросом вместо строки, как в принятом ответе. Он лучше всего работает с отметками времени GMT, а не с смещениями по часовой стрелке. Если задано смещение, это решение использует время «0». Это решение является умным и коротким, хотя и не таким правильным, как другой ответ, но лучше, чем многие вызовы строковых функций. –  L S 26.07.2016 в 17:38