# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

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

Written by maclochlainn

February 1st, 2011 at 7:54 pm

### 48 Responses 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 ðŸ˜› 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. ðŸ˜‰

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 `IF`s. Thank you so much!

Ryan

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:

`=IF(A1=EOMONTH(A1,-1)+1,EOMONTH(A1,-1)+1,EOMONTH(A1,0)+1)`

Hope that helps you.

maclochlainn

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`:

`=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`.

maclochlainn

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

maclochlainn

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:

`=IF(C12=EOMONTH(C12,-1)+1,EOMONTH(C12,-1)+1,EOMONTH(C12,0)+1)`

I had forgot to replace one of the `A1`s. 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:

`=IF(TRUNC(B4)=EOMONTH(B4,-1)+1,EOMONTH(B4,-1)+1,EOMONTH(B4,0)+1)`

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.

maclochlainn

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 ðŸ™‚

Nirmal

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.

Thanks
Ron

Ron

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.

maclochlainn

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.

Ron

Ron

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.

maclochlainn

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

Ron

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.

maclochlainn

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.

maclochlainn

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?

Zaneta

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
Ron

Ron

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

`=ROUNDDOWN((YEAR(B1)-1900)/4,0)*1461+MOD(YEAR(B1)-1900,4)*365+182`

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.

maclochlainn

18 May 15 at 1:17 am

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

maclochlainn

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.

maclochlainn

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

Grant

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)

Than
Binx

binx

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.

maclochlainn

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.

Ali

31 May 16 at 4:18 am