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:
=1000/NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY()-1)*NETWORKDAYS(EOMONTH(TODAY(), -1)+1,EOMONTH(TODAY(),0))
NETWORKDAYS Function
The NETWORKDAYS function returns the whole number of workdays between two dates. We can even exclude holidays.
NETWORKDAYS syntax:
=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.
EOMONTH function
The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.
EOMONTH syntax:
=EOMONTH(start_date, 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.
TODAY Function
I use the TODAY function to return the current date. You simply enter the TODAY function like this:
=TODAY()
And it will pick up the date from your computer clock.
NETWORKDAYS Limitations
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.
Sam says
How can we calculate networkdays using power query?
Mynda Treacy says
Hi Sam,
This post covers that.
Mynda
Syed Raza says
It is a good idea to use EOMONTH and TODAY functions to calculate start_date and end_date arguments. In your formula to Johnny for his forecast, it is counting 22 workdays (including holiday on 25th Dec) but actual workdays in December are 21. I think your formula need to adjust by adding 1 day to start-date argument so that the start_date will become December 1 instead of November 30 and then the count of NETWORKDAYS will be 21 which are the actual workdays in December.
=1000/NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY())*NETWORKDAYS(EOMONTH(TODAY(),-1)+1, EOMONTH(TODAY(),0))
Regards,
Syed
Mynda Treacy says
Hi Syed,
You’re correct…the formula starts at 30th November and it should start at 1st December.
Thanks for bringing my attention to the error 🙂
I’ve corrected it in the post for future.
Kind regards,
Mynda.
Jill Hodson says
We are using the Networkdays formula to track response times in hours. We would like to incorporate the holiday piece of this function, but are not sure how to do that. Currently we have 2 different formulas to arrive at the hours as shown below.
Example: Request rec’d = 11/20/12 10:47 am (G2)
Request completed = 11/26/12 8:12 am (I2)
Formula in column “K” =IF(I2=””,””,(ROUND((I2-G2)*24,0.5)))
Formula in column “L” =IF(K2=””,””,IF(NETWORKDAYS(G2,I2)*24<K2,K2-48,K2))
Mynda Treacy says
Hi Jill,
The syntax for the NETWORKDAYS function is:
=NETWORKDAYS(start_date,end_date,[holidays])
In your last formula above you have omitted the holidays argument. You simply need to add it like this:
=IF(K2=””,””,IF(NETWORKDAYS(G2,I2,holidays_range)*24