# Date and Time conversion

#### fede

##### New Member
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
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.

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
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
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.

Replies
1
Views
92
Replies
5
Views
511
Replies
4
Views
240
Replies
9
Views
484
Replies
7
Views
119

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.

### Which adblocker are you using?

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

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