Getting Complex with Dates

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
2 Sunday, Monday
3 Monday, Tuesday
4 Tuesday, Wednesday
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

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…

 

Posted in Uncategorized Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>