“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 18th 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 29th February is in fact the 1st 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:
|y||Day of the year|
*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 7th 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.
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:
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:
|roger at technology4u.co.uk (replace 'at' with @)|
|Technology 4 U, Glanusk Farm,|
|Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK|
|Tel +44 (0) 1873 880266|
|Mobile +44 (0) 7970 786191|
This looks like the basis of a solution to a problem I want to solve but I can’t see it.
I want to know, for each calendar month, on which day I will receive my UK state pension – which is paid on a 28 day cycle – why, I don’t dare to ask !
Once a year I get two payments in a calendar month due to the 28 day cycle.
Is there a simple formula that could take a base date when the pension was paid and deliver in a cell beneath each subsequent month either the day I get paid AND once a year the two days I get paid ?
If cell A1 contains the first date in the year that you get paid, then all you need to do is enter the following formula in cell A1:
Then copy down.
Thank you (again) for an excellent, enlightening post.
One comment, if I may:
In the formula which determines whether a given date’s year is a leap year, the result is 2 if it’s a leap year and 3 – if it is not. (Not the other way around, as you explained…)
It is well known (as you’ve stated in your post) that a leap year is divided by 4 or 400 evenly. It is also well known that Excel has a bug, in which the year 1900 is considered a leap year, where it isn’t.
Some while ago, I’ve written a “replacement” formula for Excel’s WEEKDAY function. Not only does it solve this bug, but it also allows you to find the weekday of dates prior to 01/01/1900, whereas Excel ignores such dates.
Here’s the link to the post on Google+
Today is the Jewish New Year’s Eve.
I take this rare opportunity to wish you and all the Excel community worldwide a happy new year.
Happy New Year to you, Meni.
Roger Govier should take the credit for the post.
Thanks for sharing your formula to get around dates prior to 01/01/1900, and spotting the typo (of sorts), I’ve corrected it now.
How nice to hear from you.
I must apologise for the typo in the article – totally my fault, not Mynda’s – and well spotted.
How right you are that Microsoft got the calendar wrong between 01 Jan 1900 and 01 Mar 1900. This was of course, because Lotus 123 got it wrong with their product, and MS wanted Excel to be consistent with 123 – so we live with the consequence.
I might have guessed that a mathematician like you (with a little help from Lewis Carroll) would come up with a magnificent formula to give the correct values for this period, and for dates back to 1600.
Another anomaly for which we can thank Lotus, is the cell notation.
Multiplan, the forerunner of Excel used purely R1C1 notation, with columns having numbers as well as rows.
Since R1C1 is the correct notation, and is the order of parameters within functions like INDEX(), when columns were given letters, then the cell references should have been 1A, 2A, 1B, 2B and not A1, A2, B1, B2 to be consistent.
But Lotus had already used a C1R1 notation of A1, A2 etc. and once again Microsoft followed suit to be consistent.
The rest, as they say, is history!!!
A very Happy New Year to you and all of your family.
Thank you, Roger, for your kind words.
It is indeed very rare (at least for me) to be complimented by an Excel MVP. I’m only a novice in Excel. You’ve made my day…
Actually, I’m not a mathematician… Perhaps you’ve got that impression from the book about Paul Erdős I’d sent you. As I mentioned on my blog, the urge to compose the formula came from an idea expressed by Lewis Carroll, the famous author, mathematician, logician and photographer.
I’m just an English & Math (and Arabic) teacher, with an avid appetite for Excel.
Thank you once again.
I can identify with so many of these sentiments but especially the “lazy typist” one
If I can save one keystroke on a regularly-performed sequence then it can make my day
The game does have some rules though: no VBA; standard Excel desktop; mouse only when strictly necessary (and this is rare); any ctrl, shift, alt combos count as 1½ keystrokes; repeated keystrokes only count as ½; in the event of a tie, ease of typing wins
Latest win is using End, Home, Home instead of Ctrl-end, Home (which took over from Shift-enter, Home and Ctrl-.-.-.) for getting to the beginning of the last line of a pasted range (I have to combine data from many sheets of one workbook onto one sheet of another to get a flat data format)
OK, it’s trivial, but it keeps me amused
“OK, it’s trivial, but it keeps me amused”
Excel is good like that 😉
Sounds like you need to combine sheets with Power Query
A few minutes just using the user interface, will create the result for you, and then a simple Refresh at each month end will do all of the work.
that’s a really great idea, unfortunately
1 it’s an add-in
2 we’re still using 2007
3 stop spoiling my fun (at least Mynda understands!)