The NETWORKDAYS Function

Our sales rep is out Monday to Friday selling encyclopedias and he is required  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 and the total workdays this month. The following formulas will help speed up the task for our sales rep – He might wish to get a new job as he sees digital media has taken over, and if you want to purchase an encyclopedia Amazon probably has a better deal.  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)) The 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]) use 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 our rep only takes weekends off! The EOMONTH function The EOMONTH function returns the serial number of the last day of the month before or after a specified number of months.(see emonth in date and times) 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. The TODAY or NOW Function use the TODAY function to return the current date. You simply enter the TODAY function like this: =TODAY()       Date only =NOW()         Date and Time It is important that your computer time and date are correct as it will pick up the date from your computer clock. NETWORKDAYS Limitations The NETWORKDAYS function considers workdays as Monday to Friday. However in Excel 2010 a new function is included NETWORKDAYS.INTL this function allows you to enter custom weekend parameters. The syntax is:  =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) as shown below.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor