MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel 1st Day of Next Month

with 13 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 1, 2011 and you want cell C1 calculated as the first day of February in the same year.

=EDATE(B1,1)

You can also use the EOMONTH function. 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 first day of February in the same year.

=EOMONTH(B1,0)+1

A subsequent question asked how to calculate the 15th day of a 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

Posted in Excel 2007,Excel 2008,Excel 2010,Excel 2011,Microsoft Excel

«
»

13 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 [...]

    Excel 1st Day of Next Month

    1 Feb 11 at 8:34 pm

  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

Leave a Reply