Working with a Different Working Week in Excel

International functions to the rescue

Let’s say you are transitioning to retirement (lucky you) and you only work four days a week. You have Wednesdays off to play golf. You may still do projects and you need to figure out completion dates based on a start date and working days. Excel can help you.

Excel has a WORKDAY function that works with workdays and weekends, but it treats the working week a Monday to Friday. You could hack it and include every Wednesday as a public holiday (WORKDAY can handle public holidays) but there is an easier way.

The Saturday / Sunday weekend isn’t universal. Some countries have a different weekend. To handle those differences Excel added two international functions.

WORKDAY.INTL – returns an end date based on a start date and working days.

NETWORKDAYS.INTL – returns the number of working days between a start date and an end date.

Both these functions can use a list of holiday dates.

They have an extra argument (third argument) compared to WORKDAY and NETWORKDAYS. The extra argument allows for the selection of specific days to be treated as weekends. See image below.

This following formula treats Sunday as the weekend, so Saturday is a working day.

=WORKDAY.INTL(A1,B1,11)

Now a hack that is not widely known is that the third argument can accept another type of entry.

The formula below treats Wednesday, Saturday and Sunday as the weekend.

=WORKDAY.INTL(A1,B1,"0010011")

You can enter seven digits in quotation marks that represents the week. The first digit is for Monday and the last digit is for Sunday. Zero represents a workday and a 1 represents a non-workday (the weekend).

So if you are out playing golf on Wednesday, the above formula will work out your workdays.

You can still add a range of holiday dates as shown below.

=WORKDAY.INTL(A1,B1,"0010011",F1:F10)

The NETWORKDAYS.INTL can also accept the same third argument and fourth arguments.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.