Date and Time conversion

fede

New Member
Joined
Apr 30, 2004
Messages
29
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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

New Member
Joined
Apr 30, 2004
Messages
29
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

Well-known Member
Joined
Jan 27, 2006
Messages
912
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.
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top