MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel 1st Day of Next Month

with 48 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:


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:


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).


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).


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


Hope this helps them and others looking for the trick.

Written by maclochlainn

February 1st, 2011 at 7:54 pm

48 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…..


    15 Sep 11 at 1:25 am

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


    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.


    20 Apr 12 at 2:47 am

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


    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….


    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:


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


    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!)


    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


    (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).


    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 😛 like

    a1 ~ january

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

    Think those students needs a lesson to think outside the bawks 😛


    20 Feb 13 at 6:18 am

  13. That’s true, some student should play with the tech more. 😉


    20 Feb 13 at 8:08 pm

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


    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.


    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


    11 Feb 14 at 3:36 am

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


    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!


    18 Jul 14 at 11:04 pm

  20. Looking for a formula that returns the first of the following month unless the source date is the first of the month, then I want the date to stay the same. For example, February 5 = March 1 but February 1 = February 1

    Kevin Graves

    25 Jul 14 at 9:24 pm

  21. Kevin,

    Based on your logic, you’d use this:



    Hope that helps you.


    25 Jul 14 at 10:09 pm

  22. Thank you for your help. My date format is M/D/YYYY and when I enter that formula 7/10/2013 is returning 2/1/1900?

    Kevin Graves

    25 Jul 14 at 11:09 pm

  23. Kevin, Change the formatting of the cell to numeric or simply do this in an adjoining cell. It assumes that the cell is A1:


    The value should be 32, which translates to 2/1/1900. The formatting of the cell only controls the representation of the number because all dates are numeric timestamps. Those without decimal values (numbers to the right of the decimal points are integers), those with decimal values are real numbers. So, a cell displays 2/1/1900 when you format a cell to display dates and enter the number 32.


    26 Jul 14 at 2:00 am

  24. I do see where changing the value to numeric results in 32, however, I’m looking for the result of the date 8/1/2013.

    Kevin Graves

    26 Jul 14 at 7:34 am

  25. Kevin, Are the source dates calculated or entered manually? If the former, check the calculation; and if the latter re-enter the base date. Sometimes users create dates like this by bad date mathematics or by simple typos, like for getting a key dash (-) or forward slash (/).


    26 Jul 14 at 8:57 am

  26. Thank you for all your help on this. I copied and pasted the dates from another sheet, but I re-entered manually and get the same results. My data is in C12. The formula I entered is:


    I had forgot to replace one of the A1s. The formula is now returning the desired result for all days of the month except when it is the first of the month. 11/1/13 is returning 12/1/13 instead of staying 11/1/13.

    Kevin Graves

    26 Jul 14 at 9:12 am

  27. Kevin,

    My guess is that your date is a date-time (or time-stamp). That means the source C12 column isn’t an integer, like my A1 column in the first example.

    While this is probably more programming jargon that you’d like, the EOMONTH function is an overloaded function and takes an int or double (integer or real number) as it’s parameter, and returns an integer. More or less, the IF function compares your C12 value as 11/1/13 at 12:01 A.M. against the result of the EOMONTH function (11/1/13 at 12:00 A.M.) and determines that they don’t match and returns the first of the next month.

    You can solve this by using the TRUNC function around the source date (B4 in my case and C12 in your case), like:



    You should note that the TRUNC function effectively rounds down to the integer and removes the fractional portion of the real number before making a comparison. It’s an explicit casting process when we take proactive measures to ensure the value before the IF function compares it.

    That should fix the last problem. Hope you liked the post.


    26 Jul 14 at 4:59 pm

  28. […] the EOMONTH (End Of Month) function […]

  29. Excellent… thanks so much!

    Kevin Graves

    27 Jul 14 at 9:03 am

  30. Excellent! very helpful to me today 🙂


    12 Feb 15 at 8:28 am

  31. I’m new to Excel, but I’m learning more each day. I have a form that needs to be printed for each day of the month and I need to have the date printed at the top of the form. I need to print these forms at the beginning of the month for the whole month. How do I get the correct date on each form.



    16 Mar 15 at 4:24 pm

  32. Ron, Open Page Setup, click on the Header/Footer tab, and click on the Insert Date button; and you can put the date on each worksheet in a workbook.


    20 Mar 15 at 12:27 am

  33. Thanks maclochlainn,
    I did that but it puts the same date on each sheet.
    When I print 30 sheets ,I want a different date on each sheet.



    21 Mar 15 at 4:43 pm

  34. Ron, What are bases for the different dates? I can’t really make any suggestions until you provide more information.


    22 Mar 15 at 7:17 pm

  35. This is a medical sheet for my mother-in-law, that she fills out daily. The sheets are identical, except for the date. I want to print a sheet for each day of the month, with the date at the top of the form. This way I can print her all the sheets she needs for the month, instead of printing one sheet daily.
    Thanks for your help


    23 Mar 15 at 4:37 pm

  36. Ron, The easiest way to do what you want is to designate a cell location to hold the date on all worksheets. Then, on the first worksheet enter your date, on the second worksheet reference the first worksheet’s cell and add 1. Repeat that for each worksheet.


    23 Mar 15 at 8:11 pm

  37. Is there a way to set up a formula that will display the date as: 1st, 2nd, 3rd, or Xth day of the month?

    Ana Arnold

    12 Apr 15 at 2:33 pm

  38. Ana, You should use conditional formatting.


    12 Apr 15 at 4:27 pm

  39. Hi I have a column of dates i.e. 12/05/2015, 13/05/2015… I am looking for formula to return each June 30th as number 1? Any ideas?


    12 May 15 at 3:29 pm

  40. Let me start over, I only have one (1) work sheet, I want to print this 1 worksheet 30 times for the month, putting a different date at the top of each printed form. I don’t want to create 30 worksheets.
    Is there a way to do this without changing each date manually.
    Again thanks for all your help


    15 May 15 at 12:40 pm

  41. Zaneta,

    It took a couple minutes, but here’s the formula to always find the 6/30 date of any year (column A displays the number formatted as a date in column B):



    You can use this formula to find the date and then divide by 6/30 to get 1. You can divide any date by 6/30 to calculate a ratio. The ratio is negative for numbers before the 6/30 and positive for numbers above 6/30. If you embed the ratio inside an ABS() function, you always get a positive ratio either way.


    18 May 15 at 1:17 am

  42. Ron, you should be able to record a macro to do that.


    18 May 15 at 1:42 am

  43. Working on a sheet to calculate the working hours of employee.
    In a format if we keep the days static taking Monday as the 1st day of the week and Sunday as an off date.
    How can we assign date to that month without change the day (as they are the head)

    Cell # D2 E2 F2 G2 H2
    ……. 1 2 3 4

    However next month may start from G2 cell number.

    So please help me to derive a formula to get a calendar of date in one row keeping day as static.

    Gauri Shankar

    26 May 15 at 9:28 am

  44. Gauri, I’m not sure what you’re trying to do? Days are sequential numbers and the formatting maps to the months. You’d need to give more information to help.


    26 May 15 at 8:28 pm

  45. Thanks a bunch for this. Very helpful. Was able to adapt the formula to lock in ’20th of the following month’.


    27 Jul 15 at 6:00 pm

  46. Good day Wizrds,

    i have a sheet 1 loaded with calculation already and i input on daily basis. i want to have a calculation summary on other sheet (sheet 2), i need a formula that whenever i input another date on the sheet 1 i will not change the formula on the sheet 2. e.g. sheet 1 Day 1 C1 (2+3) on sheet 2 will appear(5) but if i input day 2 C2 in sheet 1 it will not appear in sheet 2 (hope you got the idea)



    4 Oct 15 at 6:47 am

  47. The easiest way is transform that to a table and then change your formula to add the range of the table.


    4 Oct 15 at 9:52 am

  48. It shows 31 which is correct but it change the year, If I put the same formula here =Day(Today()) and change the formatting to either short date or long date, the error will show clearly. The year will change into 1900. I just want excel to remain on the current year.


    31 May 16 at 4:18 am

Leave a Reply