I’ve just inherited a monthly finance timetable that provides a series of working day deadlines for each month of the year. The way it has been set up requires quite a bit of manual adjustment to ensure that bank holidays and weekends are reflected.
In trying to neaten this up a little bit, I’ve been exposed to some interesting date formulas that are worth jotting down for future reference.
I have a few hidden columns and rows in my workbook shown here in grey, that calculate the first day of the month, the first working day of the month and the Day Difference that deals with finance convention to show the first working day as WD1 and not WD 0.
How to Calculate the First Working Day of the Month
Here I use the WORKDAY function along with the EOMONTH function (End of Month).
The following formula works out the last day of the previous month and then finds the very next working day.
=WORKDAY(EOMONTH(F8,-1),1, HOLS)
The WORKDAY function has the syntax:
= WORKDAY(StartDate, Number of Working Days before or after start date, \[optional list of holidays\])
In this case I have named the series of bank holidays as HOLS.
This is a useful link which explains why you might use the end of month function in conjunction with the WORKDAY function
What is the Day Diff Column Doing?
In finance we tend to talk about WD1 as being the first working day of the month and WD-1 as the day before the first working day of the month. In that case there is no zero.
If I want to use this figure in a date difference formula I will have a problem as I transition between negative and positive working day differences.
The formula I have in the Day Diff column is =IF(D11<0,D11,D11-1)
Which is just saying that if the Working day value is negative than I will use that value but if it is positive I will deduct 1 day. For Working Day 1 the Day Diff = o For Working Day -1 the Day Diff = -1
How do I return a date that is a certain number of working Days away from the first working Day of the Month?
Here we are back to use the WORKDAY function although I have used the slightly more complex WORKDAY.INTL function which allows you to use non-standard weekends if you so wish. I’ve used the default 1 = Sat & Sun weekends so could replace with WORKDAY.
=WORKDAY.INTL(F$9,$E11,1,HOLS) or =WORKDAY(F$9,$E11,HOLS)
This takes the first working day of the month that we calculated above and then applies the Day Difference. Hopefully you can then see why we want the day Difference for Working Day 1 to be 0. That way the formula will return the first working day of the month and not the day after! Date calculations can get confusing.
If you’ve mastered this function you might be interested to see the snazzy ways that Chandoo is using it to calculate monthly working days for projects that span periods.
I’m sure that others are using different techniques to create your monthly working day timetables and if you think you have a neater solution than the one I’ve presented I would be really interested to see it.
If you want to download the spreadsheet, I have saved a copy of the Finance Working days Timetable here.