Subtracting two numbers using Substitute

[android1]

Hi,

I have 205 (general format) in cell C13, 171.08 (general format) in C14. 205 is 205 hours, 171.08 is 171 hrs 8 minutes.
To subtract these I an using the following-> =SUBSTITUTE(TEXT(D13,"00.00"),".",":")-SUBSTITUTE(TEXT(C13,"00.00"),".",":").
My result is -9:52 (format h:mm). It should be -33:52 (33hrs 52 mins).
This formula and formatting returns the correct result in all other sheets in this workbook except this. Everything seems the same in each worksheet.

Gerry

 

[jeffreybrown]

Hi Gerry,

Maybe

=(C13-C14)/24

format as [h]:mm

 

[android1]

Thanks Jeff,

No luck. Tried that at the beginning before the Substitute formula finally solved it. All for this one particular worksheet that is.

 

[Jonmo1]

Please Clarify your cell references..
Your description says C13 and C14
But your formula has D13 and C13

I have 205 (general format) in cell C13, 171.08 (general format) in C14. 205 is 205 hours, 171.08 is 171 hrs 8 minutes.
To subtract these I an using the following-> =SUBSTITUTE(TEXT(D13,"00.00"),".",":")-SUBSTITUTE(TEXT(C13,"00.00"),".",":")

Anyway, since both numbers are in decimal format
I would do plain old B1-A1
Then divide that by 24 to get a real time value.
=(EndTime-StartTime)/24

And format the cell as [h]:mm

 

[android1]

Hi Jonmo,

Should be D13 & C13. This doesn't work for me. The numbers are pulled from a report so in my example, 171.08 (171 hrs 8 mins).
I will never have higher than .59 ie 171.6 would be 172 hours. That's why I needed the substitute formula to subtract the two.

 

[Jonmo1]

OK I see..

But your formula IS pulling the correct value of 33:52
The issue is that the cell is considering it a TIME of Day, not Time Elapsed.
So 33:52 is actually 1 DAY and 9 hours 52 minutes
But formatting that as a standard time h:mm is only going to show the 9:52.

Try formatting the cell as [h]:mm as suggested before..

 

[android1]

Yes, thank you, that was it. I did not know the brackets made a difference. When I saw [h]:mm I thought yeah, that's what I have h:mm.
Thanks for the replies. Another lesson learned.

 

[Jonmo1]

You're welcome.