Menu
I have a bunch of ISO-8601 formatted strings in a column of my sheet. How can I get google sheets to treat them as Dates so I can do math on them (difference in minutes between two cells, for example)? I tried just =Date("2015-05-27T01:15:00.000Z") but no-joy. There has to be an easy way to do this. Any advice?
803
To get dates, change the cell format to Date. If providing an explicit string input to DATEVALUE rather than a cell reference, surrounding quotation marks are required. Some date formats are not understood by Google Sheets.
If value is a number or a reference to a cell containing a numeric value, TO_DATE returns value converted to a date, interpreting value as number of days since December 30, 1899. Negative values are interpreted as days before this date, and fractional values indicate time of day past midnight.
To get an actual Date value which you can format using normal number formatting...
=DATEVALUE(MID(A1,1,10)) + TIMEVALUE(MID(A1,12,8))
eg.
| A | B | |
|---|---|---|
| 1 | 2016-02-22T05:03:21Z | 2/22/16 5:03:21 AM |
The DATEVALUE() function turns a formatted date string into a value, and TIMEVALUE() does the same for times. In most spreadsheets dates & times are represented by a number where the integer part is days since 1 Jan 1900 and the decimal part is the time as a fraction of the day. For example, 11 June 2009 17:30 is about 39975.72917.
The above formula parses the date part and the time part separately, then adds them together.
159
I found it much simpler to use =SUM(SPLIT(A2,"TZ"))
Format yyyy-MM-dd HH:mm:ss.000 to see the date value as ISO-8601 again.
29
Try this
=CONCATENATE(TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),1),"yyyy-mm-dd")," ",TEXT(INDEX(SPLIT(SUBSTITUTE(A1,"Z",""),"T"),2),"hh:mm:ss"))
Where A1 can be a cell with ISO-8601 formatted string or the string itself.
16
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With