# Calculating Date in Decimal Format

#### IndigoSim

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### scifibum

##### Well-known Member
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

##### MrExcel MVP
IndigoSim said:
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

##### Well-known Member
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 ":".

Replies
1
Views
125
Replies
17
Views
518
Replies
14
Views
345
Replies
2
Views
113
Replies
4
Views
65