Excel’s Workday() function counts working days before or after the starting date. The counted workdays automatically excludes weekends (Saturday and Sunday) and can account for holidays.
We previously explored the NetworkDays.Intl() function in Microsoft Excel to calculate the number of working days between two dates.
Workday() is similar but returns the date (past or future) after the number of workdays.
It is useful for many purposes. You can find the start or end date of a project with a set number of workdays. Also, calculating invoice due dates or expected delivery times. Some project managers may even use this function to create a Gantt chart to plan, track and execute projects.
Workday syntax
The syntax is as followed:
=WORKDAY(start_date),days,[holidays])
start_date – the start date
- days – the number of working days before or after the start date. A positive value creates a future date, whereas a negative value returns a past date.
- holidays - a range or array of one or more dates that you’re required to exclude from the working calendar(optional)
Returns: an Excel serial date.
Workday.Intl – the superior choice
Like Networkdays.Intl(), Microsoft also created Workday.Intl() that began in Excel 2010 for Windows users and Excel 2011 for Mac. It is notably the superior choice.
Although this function automatically defaults the weekend as Saturday and Sunday, there is flexibility to customise the weekend parameters. It allows the other days of the week to be considered as the ‘weekend’ or non-working days.
We recommend using this function from the beginning. The flexibility to change the weekend dates in the future is very handy, as flexible working hours are becoming increasingly popular.
Workday.intl syntax
The Workday.intl syntax in Excel is:
=WORKDAY.INTL(start_date),days,[weekend],[holidays])
- start_date – the start date
- days – the number of working days before or after the start date. A positive value creates a future date, whereas a negative value returns a past date.
- weekend – which days of the week that should be considered weekends (optional)
- holidays – a range or array of one or more dates that require to be excluded from the working day’s calculation (optional)
Returns: an Excel serial date.
Define weekend or non-working days
The weekend argument is most easily set using the codes listed in this table.
OR you can use a binary string to define a specific set of working and non-working days. This is the ultimate flexibility and great for part-time or shared jobs.
It’s seven characters (zero or one) with each position for a day of the week, starting on Monday. Zero / 0 means work day, One / 1 mean non-working day.
0000011 – is a ‘normal’ work week Mon-Fri working, Sat and Sun weekend. Same as ‘1’ in the list above.
0000110 – Friday/Saturday are days off, working Sun-Thurs. Same as ‘7’ in the above list.
0010011 – working Mon, Tues, Thurs, Fri. Off Wed, Sat and Sun.
0101010 – not working Tues, Thurs and Sat.
0000000 – a seven-day work week.
This string can be hardcoded into the function or smarties could setup a separate table of choices to control it.
Holidays
The Holidays parameter is a range or array of dates.
They could be hard-coded into the formula but normally you’ll link to a range of public holiday dates. Above is an example with a separate table of public holidays. In Workday.Intl() the holidays parameter can be the range G3:G6 or the table reference Holidays[Date].
For more about merging holidays lists see Excel’s NetworkDays() and Holidays in the real world
Multiple Holiday lists in Networkdays()
Complete Excel NetworkDays() solution with holidays & vacations which also applies to Workdays()
