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?

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:

Please Login or Register  to view this content.

time after + sign

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

and combined:

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

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..

@Kaper -

Thank you for the reply..

Your insights ere really helpful..

Marking the thread solved and adding reps..