Rachel has attended:
Excel Intermediate course
Using =WORKDAY.INTL to specify non-working days
I completed the Excel intermediate training last week; mange tak (thank you so much!) Jens for such an entertaining and engaging training session.
I had a question related to Simon's post in July this year (Calculating "clear days") that I was wondering if you could please expand upon. You mentioned in our session that the =WORKDAY.INTL formula can be used to specify non-working days when your working pattern doesn't fit the standard Monday-Friday pattern; however we didn't have time to cover this. My manager Melissa and I work Monday-Thursday; could you please explain how we can use this function to avoid having to list every Friday as a holiday?
Many thanks and best wishes,
Rachel
RE: Using =WORKDAY.INTL to specify non-working days
Hi Rachel,
Thank you for the forum question.
Both WORKDAY.INTL and NETWORKDAYS.INTL let you define custom working patterns without having to manually list every single holiday or non working day.
Here’s how it works:
________________________________________
The weekend argument
Both functions have an extra parameter called weekend. Instead of assuming Saturday–Sunday, you can specify which days are non working.
You can do this in two ways:
1. Numeric codes (pre defined patterns like “1” = Saturday–Sunday, “7” = Friday–Saturday, etc.)
2. Custom 7 character string (most flexible). Each character represents a day of the week starting with Monday.
o "1" = non working
o "0" = working
________________________________________
Example for Monday–Thursday working pattern
Since you and Melissa work Monday–Thursday, that means Friday, Saturday, Sunday are non working.
So the weekend string would be:
"0001111"
• Monday = 0 (working)
• Tuesday = 0 (working)
• Wednesday = 0 (working)
• Thursday = 0 (working)
• Friday = 1 (non working)
• Saturday = 1 (non working)
• Sunday = 1 (non working)
________________________________________
Formula examples
• WORKDAY.INTL:
• =WORKDAY.INTL(A1, 1, "0001111")
This gives the next working day after the date in A1, skipping Fri–Sun.
• NETWORKDAYS.INTL:
• =NETWORKDAYS.INTL(A1, A2, "0001111")
This counts how many working days fall between the start date in A1 and end date in A2, excluding Fri–Sun.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
