I am trying to convert string in following format:
20130817T140000Z(17th Aug 2013 at 14:00) to epoch time (seconds since 1970) in an MS Excel 2013.
Tried cell formatting, but it doesn't work with
Z or the format in general.
This will convert your date into somethign Excel will understand, If you have your date in Cell A1, Then convert that into 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)
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 is your input date as text,
A2 is a formatted date, and
A3 is a seconds difference between your date and epoch start date.
Update: based on @user2140261 suggestions.
Assuming the string to convert is in cell B1 you can use the following with a custom format on the cell to get the date/time.
The custom format is:
This will get you the date with accuracy to the minute. You can follow the pattern to get seconds if necessary.
You could use this version to convert your string to Epoch time
TEXT function to convert your string into a string that looks like a valid date/time - when you subtract 25569 (1/1/1970) that will co-erce that string to a valid date/time and the result can be multiplied by the number of seconds in a day to get Epoch time