Calculate date task will be completed in excel

1
votes

I am building a schedule on excel given that I work a 9/80 work schedule (9 hrs monday through Thursday, Every other friday off, and the fridays i do work i only work 8 hours)

I have a list of tasks along with the number of hours it will take to complete them. I want to add the number of days to my start date to figure out the date I will complete the task. I created a list of off fridays and holidays and am using the workday() function to calculate the date of completion. My problem is that I am not sure how to handle the fridays where i work 8 hours instead of 9, for tasks that are worked on a friday. so for example lets say i start Tuesday and have 36 hours to complete a task during a week where i am working on friday. If i use the workday function it will say that I will complete the task friday of that week but in reality i still have 1 remaining hour left to complete the task and so It will really be completed monday of next week. Any help would be appreciated

enter image description here

1
exceldate
My thought is a VBA solution to get around the Friday problem. I'll be watching this to see if someone has a formula idea instead. – dev1998
This is a complicated problem, not even considering the odd work schedule, which makes it even more challenging. Definitely possible without VBA, but you'd need some helper columns. Since your schedule is so irregular, I'd set up a helper table for that. Also, your "date completed" column is not enough information, since you also need to know at what hour in the day it was completed. You will need to add another column there also. I don't think WORKDAY is helpful here since your schedule is so irregular. – ImaginaryHuman072889

1 Answers

0
votes

As I see it, the trick lies in determining the number of days spent on the task. What you want is to first calculate normal days based on 9 hours a day and then add 1/9 th of a day for every 80 hours spent. Also adjust this calculation for particular day of the start date.

Determine the weekday of the startdate: in J11 enter =WEEKDAY(I11,2). This will give 1 for Monday, 2 for Tuesday and so on.

Insert a helper column before days column as 'offset days': =E13+CHOOSE($J$11,0,9,18,27,36)

Formula for 'days' column: =(E13/9.001) + QUOTIENT(F13,80)/9

and finally for 'date completed' column: =WORKDAY($I$11,G13, HolidayList) where 'HolidayList' is the name of the range where holidays are listed.

I have tested this minimally and found proper result. You should test this extensively for various startdate and different hours. Let me know if you get expected results.