Calculating Date in Decimal Format

[IndigoSim]

Hi

What is a formula that subtracts two times and gives me the result in decimal format? Eg: Column A has time in a two decimal format ie 12pm is entered as 12.00, Column B has time the same format ie 4.30pm is entered as 4.30 and column C is column A minus Column B ie 12.00 - 4.30 = 4.5hrs. These columns represent a start time and a finish time and I want to calculate the amount of time spent in a decimal format ie: 1.2 hours etc so I can add up time spent on different projects.

Thanks alot

 

[scifibum]

Here's a start. To convert one time in your format of "12.30" representing "12:30" to a decimal time format (i.e. 12.5):

=ROUNDDOWN(A1,0)+((A1-ROUNDDOWN(A1,0))/0.6)

if you have 12.30 in A1, you get 12.5 result. If you have 12.20, you get 12.333333333...

Hope this helps.

 

[Brian from Maui]

Hi

What is a formula that subtracts two times and gives me the result in decimal format? Eg: Column A has time in a two decimal format ie 12pm is entered as 12.00, Column B has time the same format ie 4.30pm is entered as 4.30 and column C is column A minus Column B ie 12.00 - 4.30 = 4.5hrs. These columns represent a start time and a finish time and I want to calculate the amount of time spent in a decimal format ie: 1.2 hours etc so I can add up time spent on different projects.

Thanks alot

Why not just enter time that Excel recognizes. It's just a matter of entering a colon instead of a decimal. You'll be better off in the long run. If you want an answer in decimals, multiply by 24.

 

[scifibum]

Good point from Brian. If you don't have control over how the data is entered you might be better off doing a Find/Replace on the existing data to replace "." with ":".