Having dealt with normal date calculation, and then working days, it’s time to look at some of the other date functions that are often used.

This week, we’ll look at Weekday.  Here’s a scenario: If you want to check some dates to see if any of them are Sundays, you could simply type them all in and change the formatting.  If however, you’d like that list of dates to be changed, and have another cell highlight if a date falls on a Sunday, then you need to use a function.

Use this example: Type the date 1st January for each year from 2010 to 2015 in a row on a spreadsheet, going from A1 to E1.  If you like, to prove the days of the week, format the dates as Long Date style, which shows the day of the week as well.

What you will see, is that 1st Jan in 2011 fell on a Saturday, and in 2012 it was a Sunday.

To work out the date that should be the Public holiday, if it’s a Saturday we need to add 2 to the date, and if a Sunday, then add 1.

IF functions will be looked at in another post, but we’ll use one in this example as a final result.

We will first use the WEEKDAY function to find out the number which represents Saturday or Sunday.

The syntax for WEEKDAY is: =WEEKDAY(Serial_Number,Return_Type)
where Serial Number is the stored number for the date (remember we talked about dates having values, so today (19th December 2012) has the value of 41,262
and Return_Type represents what day of the week you think of as the beginning – we’ll use Monday as the start day, so we will type in a 2 here (Sunday start is 1, Monday start is 2).  This means that if the number returned is a 6 or a 7, then it’s a Saturday or Sunday.

In cell A2, type the following:  =WEEKDAY(A1,2) then press ENTER

You should see the result of 5, as 1st Jan 2010 was a Friday.

Copy this function across to E2, and you will see the numbers representing the other dates.

To find a simple answer to the scenario, type the following into cell A3: =IF(A2=6,A1+2,IF(A2=7,A1+1,A1))

This is going to put a date number into cell A3, if the result from A2 is a 6, then the date number has 2 added, and if the result in A2 is a 7 then 1 is added to the date number.

Copy this function from A3 over to E3.  You will see a series of numbers – simply format then the same as A1 to E1, with Long Date.

You will see a list of dates, which have adjusted to the following Monday’s date compared to the top row, if the start date had been over a weekend.

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:

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…

When teaching Excel classes, invariably the subject of dealing with dates crops up.

Excel can be used to calculate dates. Firstly though, you need to know how to enter the date so that Excel knows that a date is being used.

Type dates in the format of dd/mm/yy e.g. 03/12/12 for 3rd December 2012. Excel will automatically display as 03/2/12/2012. You can also use Dec 12, though Excel will actually store the date from the first of the month i.e. 01/12/2012

There is a neat trick to always enter the current date – type Ctrl and ; (the semi-colon).  Excel picks up the current date from your computer.

Try it yourself – in cell A2, type Ctrl ; – today’s date should appear e.g. 3/12/2012

In Cell B2 type in a future date (let’s say 25/12/2012)

In cell C2, type =B2-A2 The number of days between the two dates will appear, in this example, 22

In some older versions of Excel, a date may be displayed, for example it might display 23/01/1904  This look very peculiar, but here I need to explain about how dates are stored.

Way back in the early days of computers, the different manufacturers decided that to use dates, they must agree on “which” date to start from.  The majority of manufacturers chose 1st January 1900 to be date 1.  At the time of this blog, today’s date shows as 41,246 if changed to a number format.

Note: Some manufacturers chose to begin in 1904, so now and again if you share files between those computers and PC’s, then you may see that the dates are four years out!

To change the 1900 date into a number, simply click the comma in Home > Number and the number will be displayed.