CODEWITHSUNDEEP
datetimegoogle-sheetsutcgoogle-sheets-formula
Filip Blaauw
Filip Blaauw

Reputation: 761

Convert UTC ISO string date to Unix timestamp

I have this UTC date in a Google spreadsheet: 2018-10-18T08:55:13Z and would like to convert it to Unix timestamp (1539852913). I tried this formula, but it's unable to recognize the timevalue:

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

If I can get a valid date and time, I can use this formula to convert to Unix timestamp:

=(A1-$C$1)*86400

Does anyone have a solution for this?

Upvotes: 5

Views: 5475

Answers (2)

pnuts
pnuts

Reputation: 59485

Simpler:

=86400*(left(substitute(A1,"T"," "),19))-2209161600

Replaces T with space and cuts off Z, leaving what's left recognisable as date and time in arithmetical calculations. Convert day and time index into seconds and adjust for the offset.

Upvotes: 4

Forward Ed
Forward Ed

Reputation: 9884

Assuming your date has proceeding zeros for single digit days and month, pull each date string part and drop it into the DATE formula as follows:

Year
=LEFT(A1,4)

Month
=MID(A1,6,2)

Day
=MID(A1,9,2)

Use the date formula
=DATE(year,month,day)
=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))

A similar process can be used for TIME

Hour
=MID(A1,12,2)

Minutes
=MID(A1,15,2)

Seconds
=MID(A1,18,2)

Time
=TIME(Hour,Minutes,Seconds)
=TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

1) There are other methods

2) The formulas will need to be adapted if you do not have leading 0 for each unit. In that case you would need to use FIND to identify the position of key characters and measure the distance between them to determine if there was a single digit unit or double digit unit.

Since the date is the integer part (left of the decimal) represents the number of days since 1900/01/01 (with that date being 1) and decimal portion represents time in terms of fraction of a day, to get a full date and time, you would add the date formula to the time formula as follows:

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

Upvotes: 0

Related Questions