I have a simple question:whats the formula to transform time into decimals? more exactly im working on a spreadsheet and i want to be able to type in for example hours worked: 11- 11:30 pm and a formula would tell me 12.5 hours how do i do it?


Hi Carlos,

I'm not that familiar with spreadsheets, so I can't help you with the actual implementation, but I can give you a few pointers that might help:

The first thing you should probably do is convert all times into a 24 hour system, that is 11:30 pm should be converted to 23:30 by adding 12 hours to all "pm" times. If the "end time" is the following day, also add 24 hours (or multiples thereof, if you've been working _really_ long).

Next, multiply the hours by 60 minutes, and add the minutes to that. Hence 11:30 pm would become 23:30 pm and then 1410 minutes, while 11:00 am would be 660 minutes.

Now subtract, to get 1410-660 = 750 minutes.

Divide by 60 to get the number of hours as a decimal, i.e. 750/60 = 12.5.

As a formula, assuming HS:MS, HE:ME are the (S)tart and (E)nd (H)ours and (M)inutes in a 24 hour clock, you would have:

Total hours = ( (HE*60+ME) - (HS*60+MS) ) / 60

Hope this helps,
Go to Math Central