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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top