# datetime - Convert string to a epoch time in Excel

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.

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)
``````

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.

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.

``````=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))
``````

The custom format is:

``````dd/mm/yyyy hh:mm:ss
``````

This will get you the date with accuracy to the minute. You can follow the pattern to get seconds if necessary.

``````=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)
``````

You could use this version to convert your string to Epoch time

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

This uses `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
{"ft_lang_label":"__label__en","ft_lang_prob":0.6717109,"math_prob":0.99172693,"size":1734,"snap":"2019-13-2019-22","text_gpt3_token_len":615,"char_repetition_ratio":0.14566474,"word_repetition_ratio":0.0,"special_character_ratio":0.38754326,"punctuation_ratio":0.19026549,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99765086,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-05-21T17:53:51Z\",\"WARC-Record-ID\":\"<urn:uuid:e02a813d-56e1-4f53-a309-977ed8107e62>\",\"Content-Length\":\"16032\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:3ccaed44-4de6-4a7e-b955-8b603d251014>\",\"WARC-Concurrent-To\":\"<urn:uuid:c5fb67b3-5e64-46f6-a07d-5278b8b42eaf>\",\"WARC-IP-Address\":\"104.31.73.18\",\"WARC-Target-URI\":\"http://www.dlxedu.com/askdetail/3/f35fc763f01b30fa45e5e706b0f4cc4e.html\",\"WARC-Payload-Digest\":\"sha1:PTWW5Q2BPJOUD7UMGN32JETNPUJGRG3N\",\"WARC-Block-Digest\":\"sha1:YLTWABGWTIEBRNQ4CMZZBZ3NIPMVMW7T\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-22/CC-MAIN-2019-22_segments_1558232256494.24_warc_CC-MAIN-20190521162634-20190521184634-00162.warc.gz\"}"}