Search Not Just Numbers

Loading...

Monday, 16 July 2012

EXCEL TIP: Finding the month end

Here is a useful tip for finding the month-end from a transaction date

I often have a need to identify the month-end date relating to a particular transaction during the month, i.e. we have the date of the transaction and need to return the date of the month-end.

NB: Most businesses in my experience tend to use calendar months for accounting periods these days, however some (particularly in manufacturing) still have month-ends on a particular day of the week. My tip below will only work for calendar months, but I may cover irregular month-ends in a future post (hint, you will need VLOOKUP).


Another NB: It has been pointed out to me by a number of readers (see James Travers comment below) that there is a quicker solution than that outlined below for users of Excel 2010. It uses a function which I didn't know was there as I didn't need it, having an approach that served me well in earlier versions. In Excel 2010, you can use =EOMONTH(A1,0), where A1 contains the transaction date. The method below is still worth knowing as you will need it if a file has to be accessed by earlier Excel versions. It also uses a number of techniques for manipulating dates that you can use for other purposes.

Here we can use an interesting and very useful feature of the DATE function, along with the YEAR and MONTH functions.

The DATE function returns a date given the year month and day, and is entered as follows:

=DATE(year,month,day)

so =DATE(2012,7,16) returns 16th July 2012 (in whatever date format you have set for the cell).

The YEAR, MONTH and DAY functions work the other way, in that they pull out the year, month and day numbers respectively from a date, so if cell A1 contains the date 16th July 2012, =YEAR(A1) will return 2012.

We could use this to pull out a particular day of the month from any transaction, so if we wanted to return the 25th day of the month of the transaction (where the transaction date,16th July 2012, is held in cell A1), we could use:

=DATE(YEAR(A1),MONTH(A1),25)

which would return 25th July 2012.

However, the actual month-end day obviously varies between the 28th and the 31st. This is where the useful quirk of the DATE formula comes in. Day zero of the month is the last day of the previous month, so

DATE(2012,8,0) returns 31st July 2012.

We can therefore amend the DATE formula above to provide the month end for the transaction date in A1,  to provide:

The Answer:

=DATE(YEAR(A1),MONTH(A1)+1,0)

And don't worry, those of you who are seeing a potential problem around the year end, when MONTH(A1) equals 13. The month formula works in a similarly useful way, so that month 13 of 2012 is month 1 of 2013, so:

=DATE(2012,13,0) returns 31st December 2012 or even;

=DATE(2012,15,25) returns 25th March 2013.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

8 comments:

  1. Or, certainly in 2010 use =Eomonth(start_date, months) with 0 for the number of months...

    ReplyDelete
  2. James

    Well spotted. That function isn't there in earlier versions of Excel and I haven't looked for it in 2010, as I didn't need it, given that I had a solution that worked.

    I will amend the post to reference this.

    Thanks for keeping me on my toes!

    ReplyDelete
  3. EOMONTH is in my version of Excel 2007 as a standard feature. It has actually been around quite a while. In earlier versions, you needed to activate the Analysis TookPak via the Tools menu in order for it to work. EDATE works similarly for moving a cell date forward (or back with a negative parameter) a given number of months.

    ReplyDelete
  4. Thanks John

    I've since found out that it is in the Analysis Toolpak in Excel 2003. However, as I am often developing a spreadsheet to run on multiple versions of Excel for clients, the more I stick to standard features the better.

    ReplyDelete
  5. I couldn't agree more. In pre-2007 days, I lost count of the number of times I had to remind users to activate the Analysis TookPak when they encountered #NAME? errors.

    ReplyDelete
  6. Could you please help me with this?: the pay day is on the 25th of each month except when that is a Saturday/Sunday (so for example for November 2012 it should show November 23rd. Thank you

    ReplyDelete
  7. Anonymous

    If we put the weekend issue aside, then if the date we are referencing is in cell A2, the formula =DATE(YEAR(A2),MONTH(A2),25) would work.

    What we need to do to deal with the weekend is replace the 25 with a nested IF statement that returns 24 if the 25th is a Saturday, 23 if the 25th is a Sunday, otherwise it returns 25.

    We can use the WEEKDAY function to return the WEEKDAY of a date.

    The default setting for the WEEKDAY function is that it returns 1 through 7 for Sunday to Saturday. We could change this by adding a second argument but we don't need to for this purpose, so the formula:

    =WEEKDAY(DATE(YEAR(A2),MONTH(A2),25))

    will return a 7 if the 25th is a Saturday and 1 if it is a Sunday. We now have all of the building blocks we need.

    The formula required is then:

    =DATE(YEAR(A2),MONTH(A2),IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2),25))=7,24,IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2),25))=1,23,25)))

    If you need some help on the IF function take a look at my earlier post at:

    http://www.notjustnumbers.co.uk/2010/10/excel-tip-if-statement-made-simple.html

    I hope that helps.

    ReplyDelete
  8. Everyday life is without a doubt rife with conflicts; pros and cons and even insecurities. Just one warranted hublot replica you have got is by using your own self, as a result try to make which will union an amazing you! Related to ones own noticed this particular blog post simply because important and even strengthening when i made once authoring the application. If you happen to was to help you speed your own self about how thrilled and even pleasant your way of life is without a doubt currently for the climb in fake omega watches, through "1" to be not and even "10" to be fabulous, at which on earth do you speed your own self? It’s an intriguing thought, considering, in cases where you’re similar to many people, you receive generalized provisions to spell it out your way of life, enjoy, "It’s OK", "It’s fair", "I can be happier" additionally, the replica watches sale. At present, used just for a flash, think of your way of life to be as a result thrilled that you really walk around the block available along with a great have fun onto your facial skin and even almost everyone natural treasures everything that you’re close to! Read the well lit tones, see that exquisite does sound, have the peacefulness . by the body processes, quality and even cartier replica that scents available one. Enlarge for that landscapes, does sound, views, is more enjoyable, and even emit a smell so long as you would like. Everything that manifested to help you any specific stress and anxiety it's possible you have become sensing? Everything that made you see within the brain suffering head? The correct way made ones own alignment respond to that envision in absolute bliss? Sorry to say, you don’t live life a global in absolute bliss. Most people contain rado replica watches stressors which come within usa as a result of lots of plans every hour it seems.

    ReplyDelete