## Excel 1st Day of Next Month

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.

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

Excel 1st Day of Next Month1 Feb 11 at 8:34 pm

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 Smith2 Feb 11 at 5:19 pm

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

robbo15 Sep 11 at 1:25 am

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

maclochlainn15 Sep 11 at 8:26 am

Great tip! Just saved me a lot of time!

Jamie Geisler12 Apr 12 at 7:20 am

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

Edwin20 Apr 12 at 2:47 am

Thank you so much. This helped me with my dissertation

Gregory4 Jul 12 at 7:33 am

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

Deepak21 Jul 12 at 12:04 am

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.

maclochlainn22 Jul 12 at 10:20 pm

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

Mim30 Aug 12 at 3:57 pm

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

Horace26 Sep 12 at 3:50 am

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

SpreadSheetNinja20 Feb 13 at 6:18 am

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

maclochlainn20 Feb 13 at 8:08 pm

Above formula helped me a lot. You saved my day

Sankara22 Oct 13 at 11:37 am

Worked perfectly – thank you, thank you!

HR Carol3 Dec 13 at 2:17 pm

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.

sprytgrrl27 Dec 13 at 4:56 pm

Amazing!! thank you.

Does anyone know how to round up or down months?

i.e. 16th feb –> 1st march & 14th feb –> 1st Feb

Brian11 Feb 14 at 3:36 am

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

maclochlainn11 Feb 14 at 11:44 am

Was looking for this and was spending hours trying to figure it out on my own with a series of nested

`IF`

s. Thank you so much!Ryan18 Jul 14 at 11:04 pm