Getting calculated date to display the next working date if result is Saturday and Sunday

[Kevin]

Hi,

I have date 13/12/17(T) and I have events to happen at various intervals of days before and days afterwards. T+3 and T+4 in this case would be a Saturday(16/12/17) and Sunday(17/12/17). I am trying to get the results to display the next working day 17/12/17.

Any suggestions appreciated?

Thanks

K

 

[pecoflyer]

Perhaps try the WORKDAY function

 

[Kevin]

Hi, I have tried that but it adds a day regardless of the calculated date. So if T+1 = Thursday 14/12/17, the workday function =Workday(T+1,1) is displaying as 15/12/17 whereas I want it to stay as is. Similarly, if I try to do previous working day, it is deducting a day whether or not it is working day or not.

 

[NARAYANK991]

Hi ,

To know whether a date is a weekday or a weekend , try :

=NETWORKDAYS(date , date)

If it is a working day , it will return 1 , else it will return 0.

Hence , based on this , you can use an IF to add either a day or 0 to the WORKDAY function.

Narayan

 

[Nightlytic]

Hi, I have tried that but it adds a day regardless of the calculated date. So if T+1 = Thursday 14/12/17, the workday function =Workday(T+1,1) is displaying as 15/12/17 whereas I want it to stay as is. Similarly, if I try to do previous working day, it is deducting a day whether or not it is working day or not.

You might want to attach a file and show on it what output you want... The workday formula works as you ask, it's the day you give it, more or less specified number of work days (refer 3rd argument to a list of holiday dates to capture these as non-working days also)

 

[Kevin]

hi,
Should have uploaded this earlier.

Thanks for your responses so far. All useful for my education.

Thanks
K

 

[Chihiro]

Your issue is you are adding T basis to date.

No need. Formula should be...
=WORKDAY($B$1,A4)

But... why should 4 workdays after 12/13/2017 be 12/18/2017?
Considering Sat & Sun as weekend. It should be 12/19/2017.

Or do your workplace observe 1 day weekend?
If you need to pick custom weekend, use...
=WORKDAY.INTL($B$1,A4,11) or something similar.
11 being Sunday only weekend.

 

[Kevin]

Chichiro, Thank you.

Thank you to everyone who took the time to respond.

 

[bosco_yip]

Try………

In B4, copied down :

=B$1+A4+MID(2100000,MOD(B$1+A4,7)+1,1)

Regards
Bosco

 

[Kevin]

Thank you Bosco. Another option