After last week’s note about dates, a few people asked me about different ways to deal with dates and calculations.
Excel has a variety of different functions to use, and the most commonly used is NETWORKDAYS. The syntax for the function is =NETWORKDAYS(start_date, end_date, holidays) where Start date is the “oldest date”, End Date is the “newest” date, and Holidays are dates to exclude.
This function works out a number of working days between two dates, assuming that Saturdays & Sundays are not working days. any dates referred to as holidays are also excluded.
To work out how many working days there are until Christmas day, you would use, for example: =NETWORKDAYS(“12/12/2012″,”25/12/2012″,”25/12/2012″) The last argument showing that, of course, Christmas day itself is a public holiday.
In the newer versions of Excel, there is also a new function NETWORKDAYS.INTL, which has an added argument Weekend – the syntax being =NETWORKDAYS.INTL(start_date, end_date, weekend, holidays) . The Weekend element allows you to choose which days of the week are to be used as weekends – refer to the following table to decide:
|1 or omitted||Saturday, Sunday|
So, if you run a hotel and have Mondays & Tuesdays as weekends in place of the traditional Saturday & Sunday, you would use the function like this: =NETWORKDAYS.INTL(“12/12/2012″,”25/12/2012″,3,”25/12/2012″)
Note: It is easier to use cell references than to type the dates into the function, but if you do type in the dates, you MUST use the inverted commas: “12/12/2012″ as Excel would othersiwe try to calclate 12 divide by 12, divided by 2012 – which calculates are 0.000497!
More on Dates next week…