Forum Discussion
Complex Working-Day Calculation
I am interning for a pre-construction firm. I was tasked with creating an calculator they can use to quickly find how much a job will cost if they input things like hourly labor, number of guys, hours the job will take, as well as filling out a 7x3 table showing how many hours will be worked per day ie. 8 hours straight time, 1 hours over time, 0 hours double time - for one day. The math for the total cost was very easy, I am having a harder time with the calculation of the calendar days.
I first created a 7x3 table (one column for each day, then straight time, over time x1.5, and double time x2) as well I made a huge equation to calculate how many calendar days the job will take. The base calculation of the equation determines if a day is being worked at all by some binary. IF the day is filled at all (working) =1, IF not (not working) =0.
The equation works very well but only if you work days consecutively in the week meaning it will take into account how many days the weekend is. For instance if there is a break in the week (working Monday, Tuesday, off Wednesday, working Thursday, and Friday) it throws the numbers off by a day or two.
Right now, I have it set up with a big IF formula. If you will work over 1 week, then it begins to factor in weekends. If you do not work over 1 week, it does not factor in weekends. This also means if you do not work over 1 week BUT there is a break in the week, then it throws the days off.
My main question; is there a formula involving some kind of complex function that will correctly identify the number of days not worked (0's or blank boxes) between the first and last day worked (boxes filled in). The part I'm stuck on is the first and last day worked during a week will change.
This means it would allow me to correctly calculate 3 calendar days from working Monday, off Tuesday, and working Wednesday but also calculate 5 calendar days from working Sunday, Monday, off Tuesday, off Wednesday, working Thursday all from the same function.
If this is not possible, the program I have designed now is very useful and the chance that there would be a break in the middle of the work week is very tiny. I just like to over engineer things and I see this as a great opportunity to really learn some advanced excel.
2 Replies
Here is a possible example of how this approach could be implemented in Excel:
- Let’s say your table of data is in the range A2:C8, with column A representing the days of the week, column B representing the number of hours worked on each day, and column C representing whether or not work was performed on that day (1 for yes, 0 for no).
- In a separate cell, you could use the MATCH function to find the first day that work was performed. For example, you could enter the formula =MATCH(1,C2:C8,0) to find the first row in column C that contains a 1 (i.e., the first day that work was performed).
- In another cell, you could use the MATCH function again to find the last day that work was performed. This time, you would need to use a slightly more complex formula to search for the last non-zero value in column C. One way to do this is to use the formula =MAX(INDEX((C2:C8<>0)*(ROW(C2:C8)-ROW(C2)+1),)). This formula will return the row number of the last non-zero value in column C (i.e., the last day that work was performed).
- Once you have the row numbers of the first and last days that work was performed, you can use the COUNTIF function to count the number of non-working days between those two dates. For example, if the first day worked was in row 2 and the last day worked was in row 6, you could use the formula =COUNTIF(C3:C5,0) to count the number of non-working days between those two dates.
I hope this helps!
