Custom Formatting Time Duration & Editing A Time Calculation

Update: I’ve tried the calculations separately. When I go to insert in Custom Format it won’t let me use the third and I am using “;”.

1. [<0.0416666666666667]m”mm” s”ss”
2. [=0.0416666666666667]h”hs”
3. [>0.0416666666666667]h”hs” m”mm”

07:00AM 07:59AM - 59mm 0ss
07:00AM 09:00AM - 2hs
07:00AM 08:01AM - 1hs 1mm

If you are using a European locale, change decimal points to decimal commas, e.g. <0,0416666666666667

I potentially see two very different questions, and I'm not sure which question to focus on:

1) The "auditing" question that is trying to understand whether the calculations are correct and trying to understand where different sources come to different results. When I want to "audit" a calculation like this, I tend to ignore the number formatting questions and format the numbers to show as much information as possible. In this case, that means a number format that shows out to milliseconds like hh:mm:ss.000 or [hh]:mm:ss.000. I would not expect floating point errors to contribute more than 1 second of error, so any calculations that are different by more than a second (the errors you are highlighting are 1 or more minutes) represent a difference in calculation scheme. As near as I can tell, Excel's calculations are correct for the inputs and calculation scheme given. As with past, similar conversations on these topics, we on this side of the internet have no way of auditing the "outside" numbers you've entered. I expect that about all we can do is affirm that Excel knows how to add and subtract numbers correctly, and that the "errors" that you point out are due to different calculation schemes.

2) The number formatting question that wants to apply 3 different number formats based on 3 conditions. If I understand correctly, I don't think you will succeed using basic number formatting. I think you will need to use full conditional formatting where you use a formula to test for each condition and apply the appropriate number format. If you need help with conditional formatting, start here: https://support.microsoft.com/en-us/...b-f1951ff89d7f Let us know if you have trouble putting the formulas together or applying the formats.

Thank you for your feedback.

1) After further work on this and receiving official assistance from employer, how do you make the decimals zero? Example:

528pm 805pm = 2:37 but converted to decimal it’s 2.61666666666667 (cell-cell)*24. How do I convert it instead to 2.6200000000?

I have tried ROUND, INT, MOD functions.

2) Need more time for the Conditional Formatting.

Rounding a decimal hours number to the nearest 1/100th of an hour should be as easy as ROUND(decimal hour calculation,2).

Do you have an example (maybe in a new sample file)? I cannot recreate a case where ROUND(calc,2) fails to round to the nearest 1/100th.

The only possible "improvement" I could see for a "take difference, then round, then sum" calculation scheme might be a LET() or LAMBDA() function that will allow you to specify the ROUND() operation for multiple differences at once. (Note that C23 is using a "take difference, then sum, then round" scheme, showing that the the placement of the round operation can make a difference in the final result). Since my older version doesn't support either of these functions, I will let others more familiar with these suggest the proper syntax.

You could use:

Formula:

Please Login or Register  to view this content.

After a few days, I will venture a response. If I understand rorya's formula, and you want to apply it to the conditional sums in G4 and G5, I would expect to simply nest the ROUND(...) part of the function inside of an IF() function that decides what to do based on column D. Maybe try =SUM(BYROW(...,LAMBDA(r,IF(INDEX(r,4)=1,ROUND((...)*24,2),0)))) [did I get the parentheses all in the right place?]. Similar for task type = 2.