Spreadsheets include powerful functions to manipulate dates (Make sure to always input any dates as a real date recognised as such – read the 21 Commandments if you’re not sure).
Today we want to look at the ability to calculate working days, or specific days of the week, between dates. This comes in handy for a retail business whose activity is higher or lower during week days or weekends. A restaurant or bar might have its activity impacted by Fridays hence need to count such days. Website visitors usually fluctuates for different days of the weeks. You probably have in mind other cases when know the day of the week matters.
2 formulas will be used:
=NETWORKDAYS()
=NETWORKDAYS.INTL()
=NETWORKDAYS()
This formula counts the number or working days between 2 dates. It considers Saturdays and Sundays as not-working days.
From Monday to Sunday of the same week, NETWORKDAYS() will return 5 (Because a week has 7 days, including 2 days of weekend in case that’s not clear 🙂
Combining number of total days between dates and removing week days calculated with NETWORKDAY() we can know the number of weekend days between dates.
NETWORKDAYS() can be set with specific holidays to refine the calculation, that we will not cover here.
=NETWORKDAYS.INTL()
That’s when it becomes very interesting: Because NETWORKDAYS() only consider Saturdays and Sundays as weekend it’s restrictive and frustrating in places of the world where other days are considered weekends (read about that on Wikipedia).
NETWORKDAYS.INTL() solve the issue by allowing us to set what we consider weekends, as it takes the 3rd parameter ‘weekend’: NETWORKDAYS.INTL( start_date, end_date, [weekend] ).
The ‘weekend’ can be set through a shortcut:
- 1 is for Saturday and Sunday weekends
- 2 is for Sunday and Monday weekends
- etc
What’s even more interesting is that we can use a string to represent the week: 7 numbers representing each the day of the week from Monday to Sunday (MTWTFSS): 0s for week days and 1s for weekend days.
This allows powerful usage:
- 0000011 means Monday to Friday are weekends and Saturdays Sundays are weekend.
- 1000000 means Monday is weekend day
- 0111111 means Monday is week day while all other days are weekends… effectively counting number of Mondays
- Want to count how many Tuesdays, Wednesdays and Thursdays? use “1000111”
- Need to count how many Thursdays and Fridays? use “1110011”
- We can reproduce NETWORKDAYS() by using “0000011”, and the inverse “1111100” will count the number of weekend days
Which days do you need to count? We made a generator of the weekend parameter:
Do you want this generator and the spreadsheet used for this article illustrations? Access it here
