Getting Complex with Dates – Part 2

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.

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>