Convert string to a epoch time in Excel

Question

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 T and Z or the format in general.

Answer 1

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)

Answer 2

Try this:

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.

Useful link

Update: based on @user2140261 suggestions.