Convert JSON Date to Excel Date

Hi,

I have a Date in this format /Date(1516645800000+0530)/ which I would like to convert to Excel date?

Can anyone help me with this?

Thank you

Deep Dave

HAve you checked: https://www.mrexcel.com/forum/excel-...me-format.html

Hi,

Thank you for the reply.. Yes, I did check that. However, I am not sure how to handle the +0530 part.

Any ideas?

Originally Posted by davsth

what is the +0530 ?

the first part converts to a date 22/1/2018 18:30 but what is the second part? and offset of that number of hours?

It is the value of the time zone..

You can extract parts of the string using/combinimg MID, LEN, LEFT, SEARCH etc.

No sample file with at least few representative samples - I probably overestimated possible complications, but:

date and time before + sign:

Formula:

=LEFT(SUBSTITUTE(A2,"/Date(",""),FIND("+",A2)-7)/86400000+25569

time after + sign

Formula:

=TIME(--MID(A2,FIND("+",A2)+1,LEN(A2)-FIND("+",A2)-3),LEFT(RIGHT(A2,3),2),0)

or

Formula:

=TIMEVALUE(LEFT(RIGHT(A2,5),2)&":"&LEFT(RIGHT(A2,3),2))

and combined:

Formula:

=LEFT(SUBSTITUTE(A2,"/Date(",""),FIND("+",A2)-7)/86400000+25569+TIME(--MID(A2,FIND("+",A2)+1,LEN(A2)-FIND("+",A2)-3),LEFT(RIGHT(A2,3),2),0)

or

Formula:

=LEFT(SUBSTITUTE(A2,"/Date(",""),FIND("+",A2)-7)/86400000+25569+TIMEVALUE(LEFT(RIGHT(A2,5),2)&":"&LEFT(RIGHT(A2,3),2))

@Kaper -

Thank you for the reply..

Your insights ere really helpful..

Marking the thread solved and adding reps..