Convert string to era in Excel

Question

I am trying to convert a string in the following format: 20130817T140000Z(August 17, 2013 at 14:00) to a time in an era (seconds since 1970) in MS Excel 2013.

I tried cell formatting, but it does not work with Tand Zor in the general format.

Answer 1

This will convert your date to somethign Excel, will understand if you have a date in cell A1, then convert it to Epoch Time

=(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,10,2),MID(A1,12,2),MID(A1,14,2))-25569)*86400)

Answer 2

Assuming the conversion string is in cell B1, you can use the following with a custom cell format to get the date / time.

=DATE(MID(B1,1,4),MID(B1,5,2),MID(B1,7,2))+TIME(MID(B1,10,2),MID(B1,12,2),MID(B1,14,2))

:

dd/mm/yyyy hh:mm:ss

Answer 3

:

A1=20130817T140000Z
A2=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
A3=(DATEDIF("01/01/1970 00:00:00",A2,"D")+TIME(MID(A1,10,2),MID(A1,12,2),MID(A1,14,2)))*24*60*60

A1 - , A2 - , A3 - .

: @user2140261.

Answer 4

. , , .

=DATE(VALUE(LEFT(A1,4)),VALUE(RIGHT(LEFT(A1,6),2)),VALUE(RIGHT(LEFT(A1,8),2)))+VALUE(RIGHT(LEFT(A1,11),2))/24+VALUE(RIGHT(LEFT(A1,13),2))/(24*60)

Answer 5

Epoch

=(TEXT(LEFT(A1,8)&MID(A1,10,6),"0000-00-00 00\:00\:00")-25569)*86400

This uses a function TEXTto convert your string to a string that looks like a valid date / time - when you subtract 25569 (1/1/1970), which will connect this string to a valid date / time and the result can be multiplied by the number of seconds in day to get epoch time