Excel End of Period Dates

“Funny” days versus funny “Days”

Guest post by Roger Govier, Microsoft Excel MVP

Do you ever have “Funny” days?

You know, the days when you start thinking about a problem, overcomplicate it and get carried down a long and spurious path where your vision becomes totally tunnelled and you can’t “see the wood for the trees”.

I have them from time to time, and evesn though I have been using Excel for over 20 years, and make my living by writing applications for clients and / or training new or experienced users of Excel, I can still get myself tied up in knots on occasion.

I had such a day recently calculating Excel end of period dates and thought that I would write about it, as it does cover many aspects of dealing with Days / Dates.

In an application I was developing for a customer who operates a consultancy business dealing with lots of different clients, with varying dates for their financial year end, I was wanting to calculate what the year-end date would be based upon any given start date.

As all good Excel people know, dates are stored internally as a serial number e.g. the 18^th August 2015 is stored as 42234. How that date is shown on the spreadsheet is all down to formatting and here in the UK I normally choose a format of 18 Aug 2015, whereas in the US, people would be more likely to use Aug 18 2015, but either way the serial number is constant.

This allows us to easily add or subtract any number of days to or from a given date, or to arrive at a new date, or to take an earlier date away from a later date to get an interval of days.

With this thought in mind, off I went with my train of thought to get the last date of a financial year, all I do is add 365 days to the start date.

Of course to get the last date of the year, I quickly realised I only had to add 364 days, not 365.

But, wait a minute, what if it is a leap year, then I would need to add 365 days, not 364

So now, I would need to test whether the End Year divided evenly by 4 or 400, in which case it would be a leap year, or in the case where it divided evenly by 100, and not by 400, then it would not. At this point, I realised that I was being stupid!!

Excel has a built in Function called EOMONTH(), which takes 2 parameters, a Start Date and the number of months required

I just needed to remember that Eomonth uses base 0, so to get the end of the year, then it is just 11 months to add to the start date. Eomonth already has the intelligence that it needs to subtract one day and provide me with the last day of the month, and not the first day of the next month.

Note: This could also be solved with the DATE() function, but it is longer to type and makes more function calls having to use YEAR() and MONTH() as well as DATE().

=DATE( YEAR ( startdate ), MONTH ( startdate )+12, 0 )

This makes use of the fact that Excel takes the Zero’th day of a month, to be the last day of the preceding month, so I need to set it to day 1 then subtract a day.

So problem solved without having to do extra, tortuous calculations about leap years.

Of course, I was also being very stupid about leap years, as there is a very easy test that you can make utilising the above functions to determine whether a year is a leap year or not.

=MONTH(DATE( YEAR ( startdate ), MONTH ( startdate ),29)   
Correction:
=MONTH(DATE( YEAR ( startdate ), 2 , 29)

This will return the result of 2 if it is a Leap Year, and 3 if it is not as excel will automatically determine that the 29^th February is in fact the 1^st March, if the year is not a Leap Year

Or, is the problem solved? What about funny “Days”?

To be more precise, I really mean funny “Dates” as opposed to funny “Days”.

I’m not sure about the rest of the world, but here in the UK we do have some funny Dates. For example, our Tax Year runs from 06 Apr to 05 Apr, and there are the Quarter Days, which are used with many types of Rental agreements, which are as follows

As you can see, the intervals between the dates are not consistent, (not that they could ever be even with either 365 or 366 days to be divided by 4).

And just to make things more awkward, in Scotland they use Cross Quarter days which fall between the two with even more irregular intervals.

So, the Eomonth() function which only works to complete calendar months, is not going to help me when I have to deal with the vagaries of some of the dates used in the UK. Nothing can predict those differing quarter days, so they would need to be “hardwired” into any calculations, but starting from one of those Quarter days, I need to be able to calculate what is the last day of that year.

Equally, in countries without the vagaries that exists in the UK, there are companies who pay their Employees every 14 days, and companies who run their accounting periods as 13 x 4 weekly periods (adding in the extra day or 2 days as appropriate when required).

So you might need to know what are the starting and ending dates for Payroll period number 29, or what are the Staring and Ending dates for accounting period number 7.

You may well have reports set up with dynamic headings which say something like

="Financial Period from "&TEXT(pStart,"dd mmm yy")&" to "&TEXT(pEnd,"dd mmm yy")

Where pStart and pEnd are the calculated dates for the relevant period.

Calculating Excel End of Period Dates

But to calculate the end of a period, be it days, months or years after a start date, when the start date is not the first or last day of an given month can be achieved with a slightly modified version of the DATE() function used earlier. Changing the formula to:

=DATE(YEAR(startdate) + addyear, addmonth,DAY(startdate) + addday-1)

Where addyear etc.is the variable number of years, months or days you want to add and typically you would only be adding one of those three variables.

Now, I wanted to carry out my calculations within VBA (as opposed to functions on the worksheet) and I have always been aware of the DATESERIAL function which is almost the direct equivalent of the worksheet DATE function. Equally I was aware of the VBA DATEDIFF function to calculate an interval between two dates, the DATEPART function for returning different elements for a date, but for some reason even though it has been within Excel VBA since Excel 2000, I had never encountered the DATEADD function.

The syntax is DATEADD ( interval, number, date ) where the parameters are

interval The time / date interval that you wish to add

It can be one of the following values:

*Time can also be added to any start date

number The number of intervals that you wish to add.

date The date to which the interval should be added.

So, within VBA

Dim StartdDate as Date, EndDate as Date

EndDate = DATEADD( "m" , 12 , StartDate) -1

would produce the correct answer for adding 12 months to a start date and give the correct ending date, and will deal with any time periods not necessarily those starting or ending with month start or end dates.

I then decided, it would be nice to have a worksheet function to carry out this task for me, so I created the following, which I have added to my Personal.xlsm file so that it is always available to me, and I copy the function into any workbooks that I create and supply to my customers.

Function EOPERIOD(Interval As String, Number As Long, pStart As Date) As Date'
 
EOPERIOD = DateAdd(Interval, Number, pStart) - 1
 
End Function

So, on a worksheet, with a start date in cell A1, then the formula entered in cell B1 of =EOPERIOD( "m" , 12 , A1 ) will give the correct result and provide more versatility than EOMONTH.

Because I am inherently lazy, I hate typing long names and typing “text” into formulae, I wrote another function using the same principles, which is in fact my favourite and the one I use all of the time now. I have called it EOP()

Since the main additions for many people will always be Days, 14 Days, 28 Days, Months or Years, I decided that I could use the numbers 1, 2, 3, 4 and 5 to represent these parameters.

=EOP (4 , 12 , A1) would be the way I would write the function above and with 06 Apr 2015 in A1, it would return me 05 Apr 2016 in B1 or it could be =EOP(5 , 1, A1) for the same result.

For the Payroll period, the Start of Period 29, would be the end of Period 28 plus 1 day, so

pStart=EOP(2 , 28 ,A1) +1 and pEnd =EOP(2 , 29, A1)

For the 7^th accounting period (when 28 days are used) then

pStart=EOP(3 , 6 ,A1) +1 and pEnd =EOP(3 , 7, A1)

For this to work, my function just requires some case statements:

Function EOP(Interval As String, Number As Long, pStart As Date) As Date
   Select Case Interval
   Case 1
       EOP = DateAdd("d", Number, pStart) - 1
   Case 2
       EOP = DateAdd("d", Number * 14, pStart) - 1
   Case 3
       EOP = DateAdd("d", Number * 28, pStart) – 1
   Case 4
       EOP = DateAdd("m", Number, pStart) - 1
   Case 5
       EOP = DateAdd("yyyy", Number, pStart) - 1
   Case Else
   End Select
End Function

So, having had a “funny” day and been entirely stupid, at least it had a good outcome and caused me to create a function which is more useful and versatile than the inbuilt Excel function. I think that this is something Microsoft should have done themselves, but when it comes to Dates they do seem to be very US centric.

Footnote:

If anyone is really interested about why the UK has such a strange tax year, there is a fascinating history available on either of the following links:

http://robertleach.co.uk/wp-content/uploads/2011/05/Why-6-April1.pdf

https://en.wikipedia.org/wiki/Quarter_days

Thanks

A big thank you to Roger for sharing this Excel technique with us.

Roger Govier is an Excel MVP based in the UK, and has been an MVP since 2007. He undertakes assignments in Excel and VBA for clients worldwide from small businesses to those with multi-billion $ turnover. In addition, Roger carries out training in all aspects of Excel either at client’s premises or at his office.

You can contact Roger at: