Adding Time that is actually in decimal form?

[IAMJoshua]

Somehow data has been collected in dec form and now I have to add it up in like form.

I have some time entries in excel I need to add up and show result back in tenths of time.

7.3 is 7 hr 30 min
7.4 is 7 hr 40 min
14.7 is really 8 hour 10 min
so I would like to be able to show as a result 15.1

how can I do this in excel?

I do appreciate the help. I have tried several formulas with rounding but I am not getting close to resolving this.

 

[Gerald Higgins]

Hi I, welcome to the board.

I understand how 7.3 is 7 hr 30 min and 7.4 is 7 hr 40 min, but how do you get 14.7 from 8 hr 10 min ?

And how do you get your result of 15.1 ?

 

[Andrew Fergus]

Hi and welcome to MrExcel!

Assuming the values you want to add are in cells A1:A3 (change to suit your actual data) then the following array formula should provide what you want:

=SUM(INT(A1:A3))+INT(SUM(MOD(A1:A3,1))/0.6)+MOD(SUM(MOD(A1:A3,1)),0.6)

This is an array formula, so once you have entered it do not press Enter - instead press Ctrl + Shift + Enter. You will know if you have done this correctly because curly brackets {} will automatically appear at each end of the formula.

Andrew

 

[barry houdini]

This method uses DOLLARFR function from Analysis ToolPak.....

=DOLLARFR(SUMPRODUCT(TEXT(A1:A3*100,"0\:00")*24),6)

 

[IAMJoshua]

Thanks I believe this is what I want. Yes I did have my number off in the example.

Hi and welcome to MrExcel!

Assuming the values you want to add are in cells A1:A3 (change to suit your actual data) then the following array formula should provide what you want:

=SUM(INT(A1:A3))+INT(SUM(MOD(A1:A3,1))/0.6)+MOD(SUM(MOD(A1:A3,1)),0.6)

This is an array formula, so once you have entered it do not press Enter - instead press Ctrl + Shift + Enter. You will know if you have done this correctly because curly brackets {} will automatically appear at each end of the formula.

Andrew

 

[barry houdini]

=SUM(INT(A1:A3))+INT(SUM(MOD(A1:A3,1))/0.6)+MOD(SUM(MOD(A1:A3,1)),0.6)

I think you'd need to add some rounding functions though. If I put 6.1 in A1 and 4.5 in A2 the result I get is 10.6. Shouldn't that be 11?

The same issue could affect my suggestion so I'd fix that with

=DOLLARFR(ROUND(SUMPRODUCT(TEXT(A1:A3*100,"0\:00")*144),0)/6,6)

 

[Andrew Fergus]

I think that's a floating point error, but as suggested the round function will fix that, like this:
=SUM(INT(A1:A3)) + INT(ROUND(SUM(MOD(A1:A3,2)),1)/0.6) + MOD(ROUND(SUM(MOD(A1:A3,1)),2),0.6)

Andrew

 

[IAMJoshua]

Can you explain what the error is? The rounding gives the wrong resut so I think the following formula is correct.

=SUM(INT(A1:A3))+INT(SUM(MOD(A1:A3,1))/0.6)+MOD(SUM(MOD(A1:A3,1)),0.6)

I think that's a floating point error, but as suggested the round function will fix that, like this:
=SUM(INT(A1:A3)) + INT(ROUND(SUM(MOD(A1:A3,2)),1)/0.6) + MOD(ROUND(SUM(MOD(A1:A3,1)),2),0.6)

Andrew

 

[Andrew Fergus]

Sometimes the formula would produce something like 10.6 instead of 11.0. In debugging the formula (using menu option Tools > Formula Auditing > Evaluate Formula) I could see parts of the formula had 0.99999999 instead of 1 (i.e. it appears to be a floating point error) which was causing the problem. Per Barry's post above, do you get the correct result for 6.1 + 4.5? Also, have you tried Barry's suggestion?

Andrew