Johnny works Monday to Friday selling encyclopedias (no one has told him about Wikipedia yet) and he wants to forecast his sales each day to see how he’s performing against his targets.
He uses this simple formula to extrapolate his sales:
=sales this month to date / workdays passed this month X total workdays this month
Johnny is a bit of a technophobe (obviously…he’s still selling encyclopedias) so he doesn’t have the benefit of Excel, but we can help him out with the NETWORKDAYS, EOMONTH and TODAY functions in Excel like this:
The NETWORKDAYS function returns the whole number of workdays between two dates. We can even exclude holidays.
=NETWORKDAYS(start_date, end_date, [holidays])
I’ve used the EOMONTH and TODAY functions to calculate the start_date and end_date arguments:
Note: The holidays argument is optional and I haven’t used it in this example as Johnny only takes weekends off!
Download the Excel workbook to see an example of the holidays argument in use.
The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.
Where ‘months’ is the specified number of months before or after the start date.
You can calculate months before the start date by using a negative number as I have done above; EOMONTH(TODAY(),-1) which gives the last day of the previous month.
I use the TODAY function to return the current date. You simply enter the TODAY function like this:
And it will pick up the date from your computer clock.
The NETWORKDAYS function considers workdays as Monday to Friday.
Thankfully in Excel 2010 NETWORKDAYS.INTL function was introduced to allow you to enter custom weekend parameters.
The syntax is:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
If you don’t have Excel 2010 there is an alternative solution for setting custom weekends here.