Perform calculations with dates and working days

Instructions: If it is easier to work directly in Excel, you can open this in Excel by clicking on the Excel Icon.

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Because Excel stores dates as a number (representing the number of days between 01/01/1900 and the date-this is the default [number format] for dates), it is relatively easy to perform calculations such as adding/subtracting a specified number of days/months/years from a date. Here we will look at functions in Excel to do this as well as to work out the number of days/months/years between two dates.

We will also look at the concept of working days. Excel has in-built functions to calculate the number of working days between two dates and to work out what date is represented by any given working day of a month. It will take into account not just weekends but also a list of public holidays which you specify. This functionality is particularly useful for finance staff who generally work to a calendar of working days.

One word of warning with dates: ensure that Excel recognises and displays dates according to your regional format (e.g British dates are in the default format dd/mm/yyyy whereas American format is mm/dd/yyyy). Provided your PC is set up with regional settings correctly (in the Control Panel of Windows), Excel should automatically apply the correct format for dates as the default. To explicitly convert a date into the correct number format use a [number format or TEXT function]. As a Brit, I’ve used the British date format in the examples below though the same concepts apply equally for any regional date format.

The DATE function in Excel takes 3 parameters (inputs) – a year, a month and a day – and returns the corresponding date. For example =DATE(2012,6,22) returns 22/6/2012 (22nd June 2012).

To get these inputs from an existing date you can use the YEAR/MONTH/DAY functions to extract these components. For example =YEAR(15/08/2015) will return 2015, =MONTH(15/08/2015) will return 8 and DAY(15/08/2015) will return 15. You can then pass some or all of these components into a date function to get another date. Alternatively you can use the DATE function to refer to a date within a function. For example to assess whether the date in cell A2 is greater than 1st Jan 2012 we could write =IF(A2>Date(2012,1,1).

The DATE function can also be used to add/subtract a specified number of days/months/years to a date. E.g. =DATE(YEAR(“30/12/2014”), MONTH(“30/12/2014”)+5, DAY(“30/12/2014”)) adds 5 months to 30/12/2014, returning 30/05/2015.

Whilst probably the most transparent, this can be quite cumbersome to add/subtract dates so as an alternative Excel has provided some quicker ways. To add/subtract days we can simply use the addition/subtraction signs – a date plus a number adds that number of days to the date. For example = “15/08/2015”+5 returns 20/08/15.

In addition the EDATE function allows you to easily add or subtract a whole number of months to a date. For example =EDATE(“16/01/2011”,6) returns 16/07/2011-i.e the date with 6 whole months added to 16/01/2011. To subtract a number of months use a negative number instead: =EDATE(“13/04/2011”,-3) gives 13/01/11 –the date exactly 3 months before 13/4/2011. Note that the EDATE function is only available in Excel 2007 onwards by default –in Excel 2003 you need to have the Analysis Toolpak Add-in enabled to use it.

Instead of working with a specific date you might want to work with the last or first day of either that month or a previous/future month relative to that date. For this you can use the EOMONTH function which gives you the last day of the month for a specified date. For example =EOMONTH(“12/01/2011”,0) gives the last day of January 2011 – 31/01/2011.

Putting in a number greater than 0 adds on the specified number of months to the date and then finds the last day of that month e.g. =EOMONTH(“15/08/2011”,5) gives the last day of Dec 2011 (5 months later)-31/12/2011. You can also use negative numbers: =EOMONTH(“15/08/2011”,-5) gives the last day of the month 5 months earlier – i.e. the last day of March 2011 which is 31/03/2011.

To get the first day of a month simply use the EOMONTH function and add 1 at the end. So the first day of the month for the date 13/06/2011 you can use =EOMONTH(“13/06/2012”,-1)+1.

If instead you simply need to know how many days are in any given month you can combine EOMONTH with the DAY function: =DAY(EOMONTH(“17/06/2012”,0)) returns 30 which is the number of days in June 2012.

To calculate the difference (in days/months/years) between two dates we can use the DATEDIF function. Strangely enough there’s no intelli-sense provided for this though the syntax is =DATEDIF(Date From, Date To, period string). The period string represents the type of difference you want to calculate – “d” for days, “m” for months and “y” for years.

For example =DATEDIF(“03/02/2011″,”16/05/2011″,”m”) returns 3 as there are 3 whole months between January 2011 and May 2011. The number of days between the two dates is calculated by =DATEDIF(“03/02/2011″,”16/05/2011”,”d”) which returns 102.

Note that the ‘date from’ has to be earlier than the ‘date to’ or it will return a #NUM error. If you’re unsure which of the two dates being passed in will be earlier then put both DATEDIF combinations within an IF function, or put them one way round and then use IFERROR to try the other way round in the event that the first DATEDIF returns an error, such as =IFERROR(DATEDIF(…),DATEDIF(..))

Also it is often more useful to consider a rounded number of months or years between two dates rather than always working with whole numbers. For example DATEDIF(“01/03/2012”, “30/06/2012”, “m”) will return 3 because there are 3 whole months between the dates, ignoring the day (fractions of a month), but in reality there are 4 months between the first day of March and last day of June. We could arrive at this more accurate answer using a formula like =ROUND(DATEDIF(“01/03/2012″,”30/06/2012″,”d”)/365*12,0) which works out the number of days between the dates and gets an exact number of months by multiplying by (12/365) – in this case 3.98 – and then rounds that up/down to the nearest number (in this case 4).

Another common requirement is to work out only the month or day component of a difference between days. So the difference between 01/01/2012 and 01/03/2013 is 1 year and 2 months – to return this value, we can use =DATEDIF(“01/01/2012″,”01/03/2013″,”y”) to corectly return the year difference of 1 but =DATEDIF(“01/01/2012″,”01/03/2013″,”m”) returns 14 rather than 2. To get just the difference in months ignoring the year we can use the period string “ym”. So =DATEDIF(“01/01/2012″,”01/03/2013″,”ym”) returns 2. In a similar vein =DATEDIF(“01/01/2012″,”15/03/2013″,”md”) returns the number of days ignoring the month – in this case 14. So if we want to express the difference between two dates in words we could write something like =DATEDIF(“01/01/2012″,”15/03/2013″,”y”) & ” year(s)” & DATEDIF(“01/01/2012″,”15/03/2013″,”ym”) & ” months ” & DATEDIF(“01/01/2012″,”15/03/2013″,”md”) & ” days” which returns “1 Year(s) 2 Months 14 days”

There are two working day functions in Excel: WORKDAY which returns the date of a specified working day since the start date specified, and NETWORKDAYS which takes two dates and returns the number of working days between them. Both functions allow you to pass in a list of holidays which it will take into account (in addition to weekends) – this list is passed in as a range of cells, either by selecting a range or using a [named range]. The Topic Example workbook has a list of a list of the UK Public Holidays in 2012 in the ‘Holidays’ tab which are referred to by the Named Range ‘PublicHolidays’.

The syntax of the WORKDAY function is =WORKDAY(start_date, days, [holidays]). The ‘start_date’ is the date you want to have as your reference point (e.g. the first date of the month), ‘days’ is the number of working days after that day which you want the date of. For example =WORKDAY(“01/06/12”,3) returns the date 3 working days after the 1st June 2012 which is the 6th June (the 2nd/3rd of June are a weekend). Note that this is actually the 4th working day of June (rather than the 3rd) because we returned the date 3 working days after the 1st June. Therefore to return the nth working day of a month either pass in n-1 into the function using the first day of the month as the start date, or have the start date of the last day of the previous month. I find the latter method more intuitive – you can use the EOMONTH function described above to reference this. For example to work out the 3th working day of June 2012 we can use =WORKDAY(EOMONTH(“01/06/12”,-1),3) which returns 05/06/2012. This doesn’t yet take into account that the 4th/5th June 2012 were public holidays in the UK (the Queen’s Diamond Jubilee!), so passing in our optional named range of PublicHolidays the function becomes =WORKDAY(EOMONTH(“01/06/12”,-1),3,PublicHolidays) which returns 07/06/12 instead.

The syntax of the NETWORKDAYS function, which allows you to calculate the number of working days between two dates, is =NETWORKDAYS(start_date, end_date,[holidays]). The NETWORKDAYS function will work out the number of working days between the ‘start_date’ and the ‘end_date’. For example =NETWORKDAYS(“01/06/2012″,”05/06/2012”) gives 3 as there is a weekend in-between.

To take into account public holidays as well you just need to pass in the range of cells with holiday dates, so for example =NETWORKDAYS(“01/06/2012”, “05/06/2012”,PublicHolidays) returns 1 reflecting the public holidays of 4th/5th June 2012. Also unlike the DATEDIF function, the NETWORKDAYS function does allow the ‘start_date’ to be after the end_date’ and will just return a negative number as a result.

In the Topic Example Workbook, write functions in the yellow cells as per the description:

Date Calculations

• In cell B6, B8 and B10 extract the Year/Month/Day aspect of the Date in cell B2 using the appropriate function
• In cells B12, B14 and B16, add the specified number of days/months from the date in cell B2 using addition, the DATE function and EDATE function
• In cells B20/B22 find the Last/First day of the month of the date in cell B2 using EOMONTH
• In cells F7, F9, F11 and F13 calculate the difference between the date in cell B2 and that in cell F2 by using the DATEDIF function

A demonstration is shown in the video clip below.

Working Day Calculations

• In cell F3 use a WORKDAY function to return the date that is 9 working days after the start date (in cell B1), ignoring public holidays
• In cell F6 write the same function but this time take into account the list of public holidays (B3:B11 on the ‘Holidays’ tab, referred to by the [named range] PublicHolidays)
• In cell F9 use the EOMONTH function to find the last day of the May 2012 with reference to the start date in cell B1. Then use this as part of a WORKDAY function to find Working Day 3 of June 2012 taking into account Public Holidays (Named Range PublicHolidays)
• In cell N3, use the NETWORKDAYS function to work out the number of working days between the start date (in cell B1) and the end date (in cell K1), ignoring public holidays
• Repeat the same function in cell N6 but this time take into account public holidays (Named Range PublicHolidays)

A demonstration is shown in the video clip below.

Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Function – Excel Magic Trick #151: DATEDIF function (between two dates)