CODEWITHSUNDEEP
stringexceldate
prabhu
prabhu

Reputation: 113

Regarding Date issue in Excel

I have issue on my Date field in Excel. Format is like this "2016-07-15T14:07:08.000Z". I tried to convert it into like this "15/07/2016 14:07:08". Help plzz

Upvotes: 0

Views: 31

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60314

You need to turn the string into something Excel will recognize as a time stamp, and then format it accordingly.

eg:

=TEXT(--SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"Z",""),"dd/mm/yyyy hh:mm:ss")

or, for a "real" date-time and not a text string:

=--SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"Z","")

and custom format the cell the way you want.

Upvotes: 2

teylyn
teylyn

Reputation: 35935

You will need a combination of Left and Mid functions to extract the text values and plug them into Date() and Time() functions. Like this (my regional settings are DMY):

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+
TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

enter image description here

Upvotes: 0

Related Questions