Need to add two sets of minutes with custom formatting and round

bh24524

Active Member
Joined
Dec 11, 2008
Messages
322
Office Version
  1. 365
  2. 2007
Hello, I'm in the process of creating a shared spreadsheet to track how much time a worker who is on light duty due to Worker's Comp injuries should be paid when they have to leave for Dr or PT appointments. The Workers Comp insurance will pay them for that time away from work. The spreadsheet looks like this so far:

1714671056578.png


I basically have the formulas which are pasted below in the sheet borrowed from another spreadsheet that uses a somewhat similar approach but with only one set of times and for a completely different purpose. The reason why these formulas look so crazy is in large part due to the formatting of the cells in Columns, C, D, and F thru H. These are Custom formatted so that all a person has to do in those cells is type only the numbers and it will add the colons, so typing 100 it puts it as 01:00 and 308 it puts it as 03:08. This was set up like this just to minimize keying and the formulas I was given work with that. Here is a list of what formulas are located where:

Column E:
Excel Formula:
=IFERROR(MROUND(IF(OR(AND(LEN(C7)=3,LEN(D7)=3,VALUE(LEFT(C7,1))>VALUE(LEFT(D7,1))),AND(LEN(C7)=4,LEN(D7)=3,VALUE(LEFT(C7,2))>VALUE(LEFT(D7,1))),AND(LEN(C7)=4,LEN(D7)=4,VALUE(LEFT(C7,2))>VALUE(LEFT(D7,2)))),ROUND((TEXT(D7,"00\:00")-TEXT(C7,"00\:00"))*24+(12),2),ROUND((TEXT(D7,"00\:00")-TEXT(C7,"00\:00"))*24,2)),0.25),"")

This formula basically subtracts the return time from the leave time to get the total time they are absent. it rounds that to the nearest quarter hour. It also takes into account the possibility of one time being an AM time and the other time being a PM time(The LEN portions of the formula) So with the above screenshot, typing 1000 in C7 for 10:00 would give column E a 4.25 value if the return time 208 (02:08) stays the same.

Column I:
Excel Formula:
=IFERROR(IF(H7="",MROUND(IF(OR(AND(LEN(F7)=3,LEN(G7)=3,VALUE(LEFT(F7,1))>VALUE(LEFT(G7,1))),AND(LEN(F7)=4,LEN(G7)=3,VALUE(LEFT(F7,2))>VALUE(LEFT(G7,1))),AND(LEN(F7)=4,LEN(G7)=4,VALUE(LEFT(F7,2))>VALUE(LEFT(G7,2)))),ROUND((TEXT(G7,"00\:00")-TEXT(F7,"00\:00"))*24+(12),2),ROUND((TEXT(G7,"00\:00")-TEXT(F7,"00\:00"))*24,2)),0.25),MROUND(IF(OR(AND(LEN(F7)=3,LEN(H7)=3,VALUE(LEFT(F7,1))>VALUE(LEFT(H7,1))),AND(LEN(F7)=4,LEN(H7)=3,VALUE(LEFT(F7,2))>VALUE(LEFT(H7,1))),AND(LEN(F7)=4,LEN(H7)=4,VALUE(LEFT(F7,2))>VALUE(LEFT(H7,2)))),ROUND((TEXT(H7,"00\:00")-TEXT(F7,"00\:00"))*24+(12),2),ROUND((TEXT(H7,"00\:00")-TEXT(F7,"00\:00"))*24,2)),0.25)),"")

This formula basically does the same as what is in column E but it looks to see if column H has a value. If it does, it will do the calculation from Column F to what is in column H. If there is nothing in H, then it does the calculation from Column F to column G.

What I ultimately want is based on what is illustrated above to have a total column that calculates the totals in Columns E and I. Here's the tricky part though, I can't simply add the two columns together. We reference the screenshot for why. If we look at John Smith's example, he has two sets of time absences, one from the middle of the day and one shortly after that for the end of the day. John's first absence was from 12:00 to 2:08 for one appointment, so 2 hours and 8 minutes which the formula rounds to 2.25 which is correct. John's second absence of time was from 3:00 to 4:08(His shift normally ends at 5:00 but the company offered an early dismissal at 4:08, so his absence time would there would be 1 hour and 8 minutes which the formula rounds to 1.25 which is also correct. If we add both E and I, we would get 3.5 hours. However, if we combine the actual times of absences, we get 3 hours and 16 minutes which rounds to 3.25 hours.

3.25 hours of pay would be the result we are ultimately after, so I am wondering what formula I could use for that. Should it be an additional column, maybe a Grand Total column?

Thank you in advance for the help and let me know if anything needs clarified better.
 

Attachments

  • 1714669784006.png
    1714669784006.png
    33.3 KB · Views: 4

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
My apologies for overthinking this. I just figured it out. :rolleyes: I eliminated the MROUND portion from both formulas in E and I and then added a totals column in J. Using the following formula in J
Excel Formula:
=IF(I6="",MROUND(E6,0.25),MROUND(I6+E6,0.25))
seems to be getting me what I need.
 
Upvote 0
Solution

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top