# First day of next month formula based on month in an initial sheet

In sheet 1, I have a cell with the date in it... such as (07/01/15)... what I need is a formula to put in sheet 2 that would calculate the first day of the following month... such as (08/01/15)... .  Some suggest to add 32 to the date but that is not working on all months.

I am using Microsoft Excel 2002

Thank you
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EngineerCommented:
=DATE(YEAR(C4),MONTH(C4)+1,1)

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Group Finance ManagerCommented:
Assuming the date is in A! on Sheet1 try this

=EOMONTH(Sheet1!A1,0)+1
Commented:
Saqib's solution will work unless the month is December.
Roy's solution will work in all cases.
EngineerCommented:
akb, Did you test it?
Microsoft Excel ExpertCommented:
The OP has mentioned that he/she has Microsoft older than 2003, therefore the EOMONTH function is a part of the Analysis Toolpak. If you get an error when you try to use the function, you can make sure the toolpak is loaded in this manner:
Make sure the Analysis Toolpak check box is selected.
Click on OK.

Remember that EOMONTH returns a serial number. Excel does not automatically format the serial number as a date. In other words, you will need to explicitly format the cell as a date.
Commented:
I apologise Saqib. I have tested and it does work. I assumed that adding 1 to month 12 would give you 13, it does not.
Microsoft Excel ExpertCommented:
Saqib'a formula only adds a month to existing date , if the date is middle month the formula will give middle of next month.

While EOmonth function gives the beginning of next month with that +1 regardless whether of the numbers of days in the dxisting date.

So if the OP wants to jump ahead one month for the the in question then Saqib formula works, if OP wants to get the next months first day for any day of previous month then it only can be best achieved by using EOMONTH
EngineerCommented:
Are you sure ProfessorJimJam?
Microsoft Excel ExpertCommented:
Apologies Saqib,  your formula works perfectly.

i misread your formula, before i did not see the date fucntion there.

date function does the job,  year function take the year for that given date and month function gets the current month of given date +1 which makes jump to the next month and 1 at the last syntax of Date function give the first date of the next month.

in this case i would personally prefer Saqib's formula, as for older version of excel, dont have to add analysis tool pack add-in.

EOMONTH function comes handly when someone would like to find end of the month.
Commented:
EOMONTH function comes handly when someone would like to find end of the month.
The DATE function can easily handle that as well.  Just subtract 1 from the Day parameter.  For instance:
Date(2015, 12, 1) returns the 1st day of December
Date(2015, 12, 1-1) returns November 30. You can also use
Date(2015, 12, 0) which returns November 30 as well since obviously 1-1=0.

Ron
Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.