CODEWITHSUNDEEP
exceldatetimeexcel-formula
Tony
Tony

Reputation: 9581

Excel DateValue = Formatting

Looking to convert the following 2015-10-07T23:59:59 into something that Excel actually recognizes as a date so I can then graph/chart with this data... I tried using datevalue and custom formatting but can't get the syntax quite right.

Upvotes: 0

Views: 168

Answers (4)

BrakNicku
BrakNicku

Reputation: 5989

To get datetime from your string, you only need to replace T with space, convert to numeric and apply appropriate cell format.:

=1*SUBSTITUTE(A1,"T"," ")

Upvotes: 1

CactusCake
CactusCake

Reputation: 990

If the timestamp isn't needed, use =DATEVALUE(LEFT(A1,10)). If it is needed, then use =DATEVALUE(LEFT(A1,10))+TIMEVALUE(RIGHT(A1,8)). (Where cell A1 contains the datetime string you are trying to parse).

This will return the Julian date value (E.g. 42284 for the day, 42284.96 for day & time) which you can then apply Excel's built in date formatting to, it will also work for charts and pivot table grouping.

Upvotes: 0

ryuu9187
ryuu9187

Reputation: 1172

How about yyyy-mm-ddThh:mm:ss for the custom format?

Upvotes: 0

Thapipo
Thapipo

Reputation: 311

I dont know if Excel can understand that type of format date, you should try to write your own custom function to get it in the format that you want.

Upvotes: 0

Related Questions