Monthly Archives: May 2014
In previous blogs on functions I showed you how to use a bunch of text functions. This time it’s the turn of date functions. Note that I will be using UK/European date annotation – day/month/year rather than the US month/day/year format.
Invariably, you will have dates somewhere in your spreadsheets and chances are you will need to do some sort of calculation or comparison based on those dates. I will show you a handful of functions I have used over the years that have come in handy at some point or other. This is not a definitive list of functions but ones that you should find a use for on a regular basis.
A quick note on how dates work in Excel (or even Access for that matter);
A date is not a date…it’s a number. Whatever format you apply or use to denote a date is purely for the benefit of us humans. If we see 24/04/2014 or 15-Jun-2014 we instantly recognise it as a date. To the computer this is complete gibberish. It has no concept of what a calendar is and therefore stores dates as a number. Have you ever entered a date and then seen it displayed something like 41257? This is in fact 14th December 2012.
According to Microsoft, the dawn of time is 1st January 1900. Try entering it in a cell and then change the cell format to GENERAL. You should see the number 1. Any date after that is simply a number representing the number of days from that date. So 14th December 2012 is the 41,257th day since 1st January 1900. So if you calculate one date minus another, it’s not being clever by checking a calendar, it is simply subtracting one number from another to give you the difference.
So now let’s look at some functions…
As the name suggests, this will give you today’s date. However, this is a dynamic date, which means that whenever you open your file the date will automatically update to whatever “today” happens to be. You must always include the two brackets after TODAY i.e. =TODAY()– it is after all a function and won’t work without them.
Comes in handy for things like checking when invoices are due/overdue for payment, checking when certificates or training are up for renewal…etc.
For a non-dynamic version of today’s date just type it in as normal or use the keyboard shortcut Ctrl + ; (semi-colon) Bear in mind that it will not update itself and will remain as a fixed value. Use Ctrl + Shift + : (colon) to enter the current TIME (non-dynamic). Use =NOW() to display a dynamic version of the current DATE and TIME but personally I find this quite pointless…you may find a use for it.
This function, along with the next two (MONTH and YEAR) have their uses on their own but are more useful when they are combined with the DATE function, but I will show you how each one works in its own right.
DAY extracts the day portion of a date value. If I have the date 24/04/2014 it will return the answer 24. The syntax for DAY is =DAY(cell reference or date)
Instead of returning the day portion of a date, this returns the MONTH portion. Using the same date as above, this will return the answer 4. The syntax for MONTH is =MONTH(cell reference or date)
The final one in our trio extracts (somewhat unsurprisingly) the YEAR portion of a date. Again, using the same date as the previous two examples will return the value 2014. The syntax for YEAR is =YEAR(cell reference or date)
Examples using DAY, MONTH and YEAR;
Now you know what those three do, it leads us nicely onto the DATE function…
The DATE function combines all three of the previous functions in the order YEAR, MONTH, DAY. Where this function comes in handy is in its ability to add or subtract days, months or years from a date. It can also be used to calculate specific dates e.g. the last Friday in the month. Example, adding 3 months to a date in cell A1;
You can even combine DATE with other functions, nesting for example VLOOKUPs to calculate how many days/months or years to add/remove from the date based on search criteria in another column.
In the example below, we have look up table that has repair lead times against a product. Next to it we have a table showing when the product is received and we need to work out the expected repair completion date based on the lead time for each product.
As the name suggests, this works out the End Of the MONTH. I used to work at a company that had a wide variety of payment terms including some odd “end of next month + 20 days “, so working out when payment was due could be challenging. EOMONTH can look both forwards and backwards from a given date.
The basic syntax for EOMONTH is =EOMONTH(date or cell reference, number of months).
Where you enter the number of months, use 0 (zero) for current month (i.e. same month as the selected date, negative numbers to go back a number of months or positive numbers to go forwards.
=EOMONTH(A1,0) will give the last day of the month in cell A1
=EOMONTH(A1,0)+30 will give me the date that is 30 days after the end of the month in cell A1
=EOMONTH(A1,1)+20 will give me the date that is 20 days after the end of the month following the month in cell A1
=EOMONTH(A1,-2)+20 will give me the date that is 20 days after the end of the month two months before the month in cell A1 (figure that one out!)
Use this to calculate the number of working days between two dates. If you are using 2007 or earlier, then you will only have NETWORKDAYS which by default assumes the weekend is Saturday/Sunday. If you are using 2010 or later, then you have NETWORKDAYS.INTL which allows you to choose pretty much any combination of two consecutive days to represent the weekend or even any one day in the week for those unfortunate to have to work a 6 day week.
You have the option to list any bank holidays, shut down days etc that you want to exclude from any calculations of working days. The best way to do this would be using a named range containing all your holiday/shut down dates, but typing in the range as normal works just as well…your choice.
The basic syntax of this function is:
=NETWORKDAYS.INTL(start date, end date, weekend day(s), holiday dates)
Here is an example calculating working days including/excluding some holiday dates;
The more basic NETWORKDAYS will not give you option to choose your weekend days, defaulting to Sat-Sun.
Whereas NETWORKDAYS calculates the number of working days between two dates, WORKDAY and WORKDAY.INTL give you a date in the future or the past based on a given number of working days e.g. what date was it 20 working days ago? You have exactly the same options as NETWORKDAYS/NETWORKDAYS.INTL – exclude/include holidays and if using 2010 or later, pick your weekend day(s).
The WEEEKDAY function returns a number that represents a day of the week. Unfortunately, the week in Excel begins on a Sunday (1), ending with Saturday (7) – this might be due to an error in Excel that thinks 01/01/1900 (or day 1 according to Excel) was a Sunday, but in fact was a Monday. The problem corrects itself from March 1st 1900, so all dates from that date will display the correct day of the week,
You can change the first day of the week by setting the optional parameter after the date to 2 (Monday = 1 through to Sunday = 7) or set it to 3 (Monday = 0 through to Sunday = 6). If you have 2010 or later then you have the option to start your week from any day in the week e.g. Wed through to Tue.
As with all Excel functions, combining or nesting functions will give you very powerful or practical calculations. It also avoids having a load of extra intermediate columns in your worksheet. Here are a couple of examples for you;
Calculating the last working day in the month:
Calculating the last Friday in the month:
For some more brilliant examples of complex date calculations go to http://www.cpearson.com/excel/DateFunctions1.aspx