How to plus or minus exactly one month in Excel?
The best way is to use a built in function in the analysis toolpack, edate.
=edate([date], [months])
Date is the date you are calculating from. Months is how many months you want to add. If you want to go backwards, then months is a negative number. So, 1 months later, put 1; 4 months ago, put -4.
Unfortunately, as much as it seems intuitive, using the date function does not work as intended.
=date(year([date]), month([date])+1, day([date]))
The problem is if the month doesn’t have a certain day, the calculation gets extended into the next month. For example, the formula below returns March 2, 2011 instead of the intended February 28, 2011.
=date(year(DATE(2011,1,1)), month(DATE(2011,1,1))+1, day(DATE(2011,1,1)))
How to get the last day of the month in Excel?
Easiest way, once again, is to use a built in funciton in the analyst toolpack, eomonth.
=edate([date], [months])
The function takes the date provided and uses the second parameter to determine the number of months future or past that should be calculated. So, if you want the last day of the current month:
=edate(now(), 0)
Similar to above, to count backwards, negative numbers for months.