Excel’s WORKDAY() counts by working days

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.  

excels workday counts by working days microsoft excel 36392 - Excel’s WORKDAY() counts by working days

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.

excels workday counts by working days microsoft excel 36393 - Excel’s WORKDAY() counts by working days

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() 

subs profile e1563205311409 - Excel’s WORKDAY() counts by working days
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address