Shaft960
asked on
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
I am using Microsoft Excel 2002
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Saqib's solution will work unless the month is December.
Roy's solution will work in all cases.
Roy's solution will work in all cases.
akb, Did you test it?
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:
Choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box.
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.
Choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box.
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.
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.
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
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
Are you sure ProfessorJimJam?
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.
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.
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
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
=EOMONTH(Sheet1!A1,0)+1