Date and Time conversion

[fede]

Hi,

I have a large sheet with a column of 50000+ time values. Unfortunately some of such values are formatted as a "general" or "string" type.

How can I make sure that ALL values are formatted as Time's so that I can make operations and comparison using time-based algebra, and not string algebra?


Thank you

F.

 

[sunnyland]

Hello,


This is not easy to give you an exact answer as I am not sure which kind of format your dates have and if they all present the same format.

Try adding an extra column beside your "dates" value:
Depending of the format:
=VALUE(D1)
will work if your time are Ie:
'13:50

if your time are as this :
'13:50 pm or '13:50 am

try :
=VALUE((SUBSTITUTE(D1,"pm","")))
or
=VALUE((SUBSTITUTE(D1,"am","")))

This will already help to spott out wich one are not fitting the format.


If not post back a bit more details explaining if ALL DATES HAVE the SAME FORMAT and WHICH FORMAT.

 

[fede]

All cells seems to be in "hh.mm" format, but when I select the cells, on some I see "hh.mm.00" in the formula bar, on some others "hh.mm".

the formula you suggested me returns a numeric value between 0 and 1 (e.g. 0.23445), for all the values. What does this mean? They are all correct?

Thank you again

F.

 

[sunnyland]

Hello,

Excel stores times as part of 24 hours, part of 1 day

in a 24 hour clock then divide hour by 24 to get the time result

ie:
9:00 am=9/24=0.375
12:00pm=12/24=0.5
3:00pm=15/24=0.625

then use format cells, use time you will have the correct time displayed.