Hello, I'm trying to calculate the Starting date and time of a project using the End Date(Due by Date) and the estimated hours needed. I am also trying to exclude weekend days and I only count hours within my start and end time for each day.

I'm adding a reference to a previous question where the End Time was calculated this way from the Start time and estimated hours, but I am having trouble modifying this to achieve my goals. Any help would be appreciated, I am using Excel 2007.

The previous question is linked here: https://www.teachexcel.com/talk/989/calculation-of-end-date-by-using-start-date-and-time-and-working-hours-as-i

And pasted below is the original question with the answer formula:

Here incident is created at particular date and time .We set some priorties priority severity resolve issue in time p1 s1 4 hrs p2 s2 9hrs p3 s3 27hrs p4 s4 45hrs Now we need to calculate closure date by excluding saturdays and sundays and timings 9am to 6 pm. if incident created on Friday evening 6 we need to calculate hrs from Monday 9 am because of weekends. Pls solve this. Selected AnswerYou are in luck! There is a great formula for this.

Select All```
=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
$F$2,1,0))+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
```

In this example it is assumed that you have 4 cells setup like this:

- A2 is the starting date/time
- B2 is the number of hours
- E2 is the starting time of the workday
- F2 is the ending time of the workday

You can change the cell references as you need but be careful since it's a huge formula and you will need to change things in a few different places.

(I found this formula on the web a while back and it has worked great for me.)

posted by: don (rep: 1989) on Tue Aug 30, 2016 at 10:25 pm