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?
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.
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))