This might be the most niche post I have ever done but I hope that it might prove useful to someone.
My credit card bill gets made up on or around the tenth of each month but, for reasons I won’t go into here, I needed to much more accurately predict when it was actually going to be created. Through a combination of searching and testing, I discovered that in my case it is always made up on the sixth working day of the month. Turns out that there is an Excel (and Google Sheets) function that can help with that: WORKDAY.INTL.
Introducing WORKDAY.INTL
=WORKDAY.INTL(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 5, 1)Breaking down the above WORKDAY.INTL has four parameters:
- a date, here constructed by taking the current year and month from today’s date and the 1st
- the offset, i.e. the number of working days after the date specified, here five
- what constitutes the non-working days, here one which signifies Saturday and Sunday, there are different options shown in the help for the function
- holiday dates, not shown here but we will come on to that below.
This, on the face of it, worked fine but, of course, public holidays throw a spanner in the works. Excel has an answer for that as you can provide a reference to a list of dates that you want to be considered non-working. Therefore, the formula is extended thus:
=WORKDAY.INTL(DATE(YEAR(TODAY()), MONTH(A2), 1), 5, 1, $E$2:$E$11)That Still Doesn’t Look Right!
In theory, that should have been fine but checking against previous statement dates I could see that there were some months where the formula returned a date earlier than the actual statement date. The answer was in the way that WORKDAY.INTL worked.
If the first of the month falls on a working day (i.e. Monday to Friday here in the UK) then WORKDAY.INTL includes that day in the calculation as the first working day. Therefore, to get the sixth working day you count five after the first. However, if the 1st falls on a weekend (Saturday or Sunday in the examples) then it starts the count from the next working day (usually the Monday) meaning that you actually get only five working days. This doesn’t seem right to me but my chances of getting Microsoft to respond as to why it is like that is close to nil so I had to cater for that instance.
=IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(A2), 1))=1,WORKDAY.INTL(DATE(YEAR(TODAY()), MONTH(A2), 2), 5, 1, $E$2:$E$11),IF(WEEKDAY(DATE(YEAR(TODAY()), MONTH(A2), 1))=7,WORKDAY.INTL(DATE(YEAR(TODAY()), MONTH(A2), 3), 5, 1, $E$2:$E$11),WORKDAY.INTL(DATE(YEAR(TODAY()), MONTH(A2), 1), 5, 1, $E$2:$E$11)))This is actually a lot simpler than it looks as what we have here is the original formula repeated three times in an IF statement. Using the WEEKDAY function to workout the day of the week (Sunday = 1, Monday =2 etc.) we can say that if the 1st falls on a Sunday then start the count on the 2nd and if the 1st falls on a Saturday then start the count on the 3rd.
If you want to have a play you can view and download a sample spreadsheet here.