Tricks with dates in Excel

Working with dates in Excel is fairly easy however here are some tricks that I have found useful:

Finding the end of a month

If you have a date in A1 then the end of the month is

=EOMONTH(A1,0). 

Note that this also allows you to find the date of the end of the month in X months time by replacing 0 with X

Finding the last Friday of the month

Again with a date in A1 the following calculation returns the last Friday in the month.

= EOMONTH(A1,0)-MOD(WEEKDAY(EOMONTH(A1,0))+1,7)

Adding months to a date

You can't just add days to a date to make the 1^st of January become the 1^st of April.  To add whole moths (or years) to a date use the DATE formula which builds a date up from the year, month and day.

If you have your date in A1 and the number of months to be added in A2:

=Date(year(A1), month(a1)+A2, day(A1))

Obviously if you want to add years you can move the +a2 up into the year section of the calculation.  You can also just add a number  into the calculation in place of "+a2".

Working days between two dates

Excel has a great formula NETWORKDAYS which calculates the week days between a start date and end date and also removes defined holidays.

If you have the Start in A1, the end date in A2 and some holidays in the range B1:b5.

=NETWORKDAYS(A1,A2,B1:B5).

This calculation assumes the "start" is at the beginning of the day and the "end" is at the end of the day so is A1 = A2 the formula will return 1 working day.  Number of working days between two dates.

Calculating how many months or years lie between two dates

If you want to know how many whole months or years lie between two dates you can either use the Month() calculation or a rather well hidden DATEDIF function which is a hangover from the migration from Lotus 123.  It has been there since very early versions but tends not to be documented.

Using the MONTH calculation means that you also need to check that the YEARS are the same otherwise you could under calculate the difference if the period covers one or more year ends.  The DATEDIF formula gets around this.

The syntax of the DATEDIF calculation is =DATEDIF(date1, date2, interval)

Assuming the dates are in A1 and A2, to find the whole months between A1 and A2

=DATEDIF(A1, A2, "m")

There are different intervals:

Note that if you have the interval in another cell you can use DATEIF(A1, A2, B1) however B1 needs to contain m with no quotes around it.

This function can be very useful if you want to return a text string for instance "Period is X years and Y months".  This would be written as

="Period is " & DATEDIF(A1, A2, "y") & " years and " & DATEDIF(A1, A2, "m") & " months"

Obviously if the A1 and A2 are in the same year this would return a "0 years" element which might not be desirable.  This can be avoided with an IF statement to check what value the 1^st DATEDIF will return.

="Period is " & if(DATEDIF(A1, A2, "y") = 0, "",DATEDIF(A1, A2, "y") & " years and ") & DATEDIF(A1, A2, "m") & " months"

Calculating the start of a week

B1-(WEEKDAY(B1)-2)

Find the day of the week

Use WEEKDAY to return a number (normally 1-7) corresponding to the day of the week.   The syntax for this is =WEEKDAY(Date as serial number, return_type).    Some important thigs to note are:

• the date needs not to be in a "20-02-2017" type format in the calculation. If you have the date in a cell (A1) this is fine because excel converts it into a serial as soon as you type it into the cell.  However if you have typed it into the calculation then it will not work, in which case you'll need to use the DATE function within the calculation:=WEEKDAY(DATE(2017,02,20))
• The return_type entry changes the number code for each day in the week (see the table below). If you don't include the return_type (as I didn't above) then Excel assumes 1.
• If you're using a version of excel before 2007 the optional return_type doesn't exist and Excel behaves as if the value is "1"

Calculate Date + x avoiding weekends

Recently I needed to calculate when deliveries would arrive avoiding any deliveries over the weekend. 

To do this I used an IF statement to check if the simple Date + X would be a weekend and if so add the required number of days on.  This is simplified by using a return_type of 2 so that  weekdays are 5 and below:

=IF(WEEKDAY(I9+M9,2)>5,I9+M9+(8-WEEKDAY(I9+M9,2)),I9+M9)

I9 contained the Date the delivery would be dispatched.

M9 contained the delivery time (normally 2 days)

The formula breaks down into

• WEEKDAY(I9+M9,2) = day of the week that (Dispatch + delivery time) falls on
• If this is more than 5 (I.e. Saturday or Sunday) then add some time on.
• This is calculated with the 8 – the day of the week (Saturday = 6, Sunday = 7), thus add either 1 or 2 days.
• If the delivery day is Friday or less just show the simple calculation of Dispatch + Delivery time.

Dealing with "hidden" time; date functions not working as expected

Sometimes when copying in dates, for instance from MSP or another sheet a date will come across looking innocent "12/02/17" however another date which looks the same will actually be slightly different so when calculate using the two apparently identical dates you will end  up with different results.  This is because the Serial Date value is actually different because it contains a time element (as all dates do).

If you don't care what the time "hidden" in the date is you can strip it out using the DATE function:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)).

This has pulled out the year, month and day of A1 and then recombined them into a new date (which will still display as 12/02/17) however the time is now set to midnight (0:00).  If you do this to all the dates copied in then you will have removed the "hidden" time and they will calculate in the same way.

Finding which quarter a date is within

Where the date is in A1 use the formula ROUNDUP(MONTH(A1)/3,0) to display the quarter.  This works by dividing the month value by 3 and then rounding up to the nearest integer value.

If you want to find the date for the end of the quarter for date A1 you need to combine some of the formulas which we've used so far:

=EOMONTH(DATE(YEAR(A1),ROUNDUP(MONTH(A1)/3,0)*3,1),0).

This formula is finding the quarter (MONTH(A1)/3) and using in the Date formula to find the 1^st of the month; DATE(YEAR(A1),ROUNDUP(MONTH(A1)/3,0)*3,1) and then using the EOMONTH formula to find the end of the month.

Likewise if you want to find the start of the quarter the formula is:

=EOMONTH(DATE(YEAR(C2),ROUNDUP(MONTH(C2)/3,0)*3,1),-3)+1