MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel 1st Day of Next Month

with 19 comments

I had an interesting reaction when I told my students they had to create a dynamic Excel model that required them to enter the months of the year without AutoFill feature. They were stumped. They couldn’t find a function to perform it for them.

It’s really quite easy, the EDATE function lets you find it directly. Enter any day in the current month as the first parameter and one as the number of months to add as the second parameter to the function. VoilĂ , you have a function to add a month to any date. The only time it returns the first of the next month is when the source date was the first day of the current month.

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of February in the same year or February 14, 2011:

=EDATE(B1,1)

Here’s the solution when cell B1 contains January 14, 2011 and you want cell C1 calculated as the fourteenth day of December in the prior year or December 14, 2010:

=EDATE(B1,-1)

You can also use the EOMONTH function to find the first or last day of the month. It lets you find it the first day of the next month regardless of the start date. All you need is a simple trick.

Enter any day in the current month as the first parameter and zero as the number of months to add as the second parameter to the function, and then add one.

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the last day of January in the same year (rounding up).

=EOMONTH(B1,0)

Here’s the solution when cell B1 contains any day in January and you want cell C1 calculated as the first day of January in the same year (rounding down).

=EOMONTH(B1,-1)+1

A subsequent question asked how to calculate the 15th day of the current month, and here’s the formula:

=EOMONTH("10-Jul-2012",-1)+15

Hope this helps them and others looking for the trick.

Written by maclochlainn

February 1st, 2011 at 7:54 pm

19 Responses to 'Excel 1st Day of Next Month'

Subscribe to comments with RSS or TrackBack to 'Excel 1st Day of Next Month'.

  1. [...] They couldn’t find a function to perform it for them [...]

  2. Which class was this for? This was something we covered when creating supply chain excel sheets in Business 212. It was a lot of fun playing around with these functions. Thanks for the reminder of a great class that I did in the past.

    Jordan Smith

    2 Feb 11 at 5:19 pm

  3. Does this not apply to Excel 2000? Can’t get it work, or find any reference to it in the formula help pages…..

    robbo

    15 Sep 11 at 1:25 am

  4. I don’t recall, and recently tossed the Office 2000 software.

    maclochlainn

    15 Sep 11 at 8:26 am

  5. Great tip! Just saved me a lot of time!

    Jamie Geisler

    12 Apr 12 at 7:20 am

  6. Nice work, the formula works perfectly, you’ve made my work easier.

    Edwin

    20 Apr 12 at 2:47 am

  7. Thank you so much. This helped me with my dissertation

    Gregory

    4 Jul 12 at 7:33 am

  8. Hi i need formula to find upcomeing date … an example would be if today date is 21/7/2012 i want the formula to know what is the upcoming 15th day….

    Deepak

    21 Jul 12 at 12:04 am

  9. If you’re trying to find the 15th of the same month, the formula would be this:

    =EOMONTH("10-Jul-2012",-1)+15

    It basically goes back to the last day of the prior month and adds fifteen days.

    maclochlainn

    22 Jul 12 at 10:20 pm

  10. GREAT HELP! Thanks!!!! New HR Manager just asked me “how to find the first day of next month … of any date” (to calculate employees service dates); I couldn’t find that exactly in the formula screen, so typed that in the on-line help … and came across your answer! Now he thinks “I” am the excel formula genius! haha :) (nope — giving YOU all the credit!)

    Mim

    30 Aug 12 at 3:57 pm

  11. I was trying to do this, earlier and before researching I hit a basic way of doing it, just use

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

    (Assuming Cell A1 has the date you wish to find the date of the first date of next month.)

    It returns the correct date, even when A1 is in December. (At least in LibreOffice, I assume even Excel should do it).

    Horace

    26 Sep 12 at 3:50 am

  12. im useing norwegian version of excel where we use ; instead of , to seperate formulas. also text and datevalue is what im assumeing is used in english version.
    By reading my suggestion\answer you agree to not saying my formula doesnt work and that you have to send your computer to me as a donation so i can research what went wrong.

    Im Kinda confused that they didnt come up whit alot of formula workarounds, like what Horace suggested, i can think of plenty that revolves on just finding next month even inaccurate ones :P like

    a1 ~ january

    a2 ~ =text(datevalue(1&”.”&A1)+32,”mmmm”)

    Think those students needs a lesson to think outside the bawks :P

    SpreadSheetNinja

    20 Feb 13 at 6:18 am

  13. That’s true, some student should play with the tech more. ;-)

    maclochlainn

    20 Feb 13 at 8:08 pm

  14. Above formula helped me a lot. You saved my day

    Sankara

    22 Oct 13 at 11:37 am

  15. Worked perfectly – thank you, thank you! :)

    HR Carol

    3 Dec 13 at 2:17 pm

  16. Amazing…thank you! You don’t know the work-arounds I’ve gone through to get this data in the past and how useful this will be.

    sprytgrrl

    27 Dec 13 at 4:56 pm

  17. Amazing!! thank you.
    Does anyone know how to round up or down months?
    i.e. 16th feb –> 1st march & 14th feb –> 1st Feb

    Brian

    11 Feb 14 at 3:36 am

  18. Brian, I updated the post for you. I think you have the answer now.

    maclochlainn

    11 Feb 14 at 11:44 am

  19. Was looking for this and was spending hours trying to figure it out on my own with a series of nested IFs. Thank you so much!

    Ryan

    18 Jul 14 at 11:04 pm

Leave a Reply