Time to Decimals in Excel

James240

Hiya Currently im doing a project for work on EXCEL but i have no idea how to convert a column that is set as time i.e HR:MIN:SEC into a decimal figures in another column. If we assume that 0.50 is 00:30:00 min and 1.00 is 01:00:00hr and everything in between is decimilised as well what would the formula be to convert it?? Any help would be much appreciated thanks James

kwikbreaks

I had a play and this is how it seems to work..

If you set a cell format to time and enter a valid time (I chose to set A1 to 01:15:00) then set a different cell formatted as a number equal to it (with =A1) the cell shows as 0.05 If you multiply that by 1440 (minutes in a day) it come out at 75 which is 75mins since midnight the same as 01:15:00

So the time is stored as minutes since midnight / 1440

bob_a_builder

No neither do I.

But Excel has a VB based lang/macro capability built in

And VB has the concept of date / time serial numbers where the interger part is the date and the fraction part is the time

Maybe take a look at that option

John_Gray

What kwikbreaks says, except that since you want the time in decimal hours, the multiplier is 24. Format that cell as Number, with as many decimal places as you want (3 will give a precision of just under 4 seconds, 2 would give a precision of 36 seconds).

ComplexP

James240 wrote: »

Hiya Currently im doing a project for work on EXCEL but i have no idea how to convert a column that is set as time i.e HR:MIN:SEC into a decimal figures in another column. If we assume that 0.50 is 00:30:00 min and 1.00 is 01:00:00hr and everything in between is decimilised as well what would the formula be to convert it?? Any help would be much appreciated thanks James

Ok, here's what to do (I think).

I use the cell format category of 'Custom' and type 'hh:mm:ss'.

Excel sees time as a 24hr clock with 24:00:00 equal to 1.0 (100%).

Therefore 01:00:00 will be 1/24= 0.41666...
and 04:00:00 will be 4/24= 0.1666...

and so on.

If you want to work in minutes you'll have, for example

01:04:00 as 64/1440= 0.0444... (since there are 1440 minutes in a day).

and working in seconds will be something like

01:15:30 as 4530/86400= 0.0524... (since there are 86400 seconds in a day)

I'd avoid trying to work in decimals, it's much better to go with the fraction format. ie x/24 or x/1440 or x/86400.

Hope this helps.

ComplexP

Mobeer

Excel stores dates as decimals, where
0 = 1 January 1900
0.75 = 1 January 1900 18:00
1 = 2 January 1900
etc

so to see a number as a date just format the cell to be a date

[the date for value 0 will change if Tools>Options>Calculation has the 1904 date system in use, not a default]

john_s_2

At the risk of sounding picky, 0 is 00/01/1900. 1 is 01/01/1900.