1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'Ask an Excel Question' started by Kevin, Dec 6, 2017.

1. ### KevinNew Member

Messages:
27
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
2. ### pecoflyerActive Member

Messages:
258
Perhaps try the WORKDAY function
Nightlytic likes this.
3. ### KevinNew Member

Messages:
27
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.
4. ### NARAYANK991Excel Ninja

Messages:
16,619
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
Thomas Kuriakose likes this.
5. ### NightlyticMember

Messages:
110
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)
6. ### KevinNew Member

Messages:
27
hi,

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

Thanks
K

File size:
8.5 KB
Views:
6
7. ### ChihiroExcel Ninja

Messages:
5,055

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.
Thomas Kuriakose likes this.
8. ### KevinNew Member

Messages:
27
Chichiro, Thank you.

Thank you to everyone who took the time to respond.
9. ### bosco_yipExcel Ninja

Messages:
2,007
Try………

In B4, copied down :

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

Regards
Bosco

#### Attached Files:

• ###### DateExclSatAndSun.xlsx
File size:
9 KB
Views:
2
Thomas Kuriakose and deciog like this.
10. ### KevinNew Member

Messages:
27
Thank you Bosco. Another option