CODEWITHSUNDEEP
excel
Mathilde
Mathilde

Reputation: 13

How to account for weekends in the workdays-excel function?

I am trying to count the number of days between a number of dates and a deadline corresponding to each date, and was hoping that anyone had experience with the excel worksheet function WORKDAYS? The function in it’s basic form counts the number of working days between any two dates which I would like to use but with the slight alteration that deadline and the date should not be included in the number of working days, such that there will be one working day between a deadline the 12th of October and an initial date the 10th of October for example. To do this alteration I have so far just subtracted two days from the number of working days of the excel-function but this causes problems when the deadline or initial date is in a weekend. I was therefore hoping that any of you have an idea how to solve my problem?

The data consists as mentioned above of several deadlines, each at midnight (7/10/16 00.00.00) whereas the initial dates are at some point in time during the day when some events are happening. Take for example the initial date 3. of October 2016 and lets say that something happened at 3 p.m. There is therefore no working days between this event at 3. p.m. and the deadline at midnight (4'th of October 2016 at 00.00.00 (midnight)).
I have used ”=if(Deadline="-";"No deadline";Workdays(Deadline;Initial Date)-2)” to construct the following data which is the closest I have been to count the number of days correctly, so I was hoping that anyone more experienced in excel has some idea of how to improve on the counter when any of the dates are weekends?

Deadline     Initial Date   No. work days
7/10/16      5/10/16        1 
  -          11/10/16       No deadline
26/8/16      10/11/16       -34
  -          7/10/16        No deadline 
1/9/16       7/10/16        -29
24/10/16     18/10/16       3
  -          3/10/16        No deadline
24/10/16     17/10/16       4 
17/10/16     10/10/16       4
30/9/16      4/11/16        -28
11/11/16     2/11/16        6
1/11/16      14/10/16       11
11/10/16     30/10/16       -16
4/10/16      3/10/16        0

Kind regards,

Upvotes: 1

Views: 157

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60484

I'm not sure what you expect when Deadline occurs before Initial_Date, but since the value is negative you need to add rather than subtract two days.

So far as adjusting the two dates so they do not occur on a weekend, you would subtract one calendar day and add one workday to move Initial_date to the first workday after the weekend; and add one calendar day then subtract one workday to move Deadline to the Friday preceding. This will not change the date unless the date in question occurs on a weekend.

The formula below does all that:

=IF(Deadline="-","No Deadline",NETWORKDAYS(WORKDAY(Initial_Date-1,1),WORKDAY(Deadline+1,-1))-2*SIGN(Deadline-Initial_Date))

Upvotes: 0

Vijayakumar Udupa
Vijayakumar Udupa

Reputation: 1145

This formula gives you 0 if A1 is a workday, 1 if its not.

=IF(WORKDAY(A1-1,1)=A1,0,1)

So, you should alter your formula like

=if(Deadline="-";"No deadline";Workdays(Deadline;Initial Date)- (IF(WORKDAY(Initial Date-1,1)=Initial Date;0;1)) + IF(WORKDAY(Deadline-1,1)=Deadline;0;1))  )

Note: I'm using excel 2016 and functions are named as WORKDAY and NETOWRKDAYS. Looking at your formula, function names may be different. Please check that as well.

Upvotes: 1

Related Questions