Calculating a constant formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a vacation tracking spreadsheet, and I need the formulas to go
monthly, which I have done, but say someone started Dec 01/04, and then I
make the formula to make it every 30 days from there (or the day before the
date the employee started) (which I have using EDATE function), how can I
make sure that when it reaches February, that it calculates it on the last
day of February, and then the rest of the months after February do the same
thing? An example of what my formula does:
Start Date: Dec.01/04
Next Date: Dec.30/04
Next Date: Jan 30/05
Next Date: Feb 28/05
Next Date: March 28/05

But it should be:
Start Date: Dec.01/04
Next Date: Dec.31/04
Next Date: Jan 31/05
Next Date: Feb 28/05
Next Date: March 31/05

I know this is confusing, but can anyone help?
 
Hi
in A1 enter your start date
in A2 enter
=DATE(YEAR(A1+1),MONTH(A1+1)+1,0)
and copy this formula down
 
Sonia

Try the End of Month Function

Start Date: 1/12/04
EOMonth =EOMONTH(B31,0)
EONextMonth =EOMONTH(B32,1) and copy this formula down

Regards
Peter
 
This something new in '03? i've never seen it in XP or
lower. Go with Frank's if you're using any other Office
version.

Kevin M
 
Hi
you need to install the Analysis Toolpak Addin to use EOMONTH (was
available in Excel 97 as well)
 
Back
Top