General discussion

Locked

formula for month end dates in Excel

By csuu ·
How do I list the month end (or begin month) dates automatically (by formula)in a row or in a column? For example, Jan 31,2003; Feb 28,2003; etc.

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to formula for month end dat ...

The place to look for "Date Math" is
www.cpearson.com/excel/datetime.htm
Here's one suggestion. choose one cell to place the starting date, say A1.
Enter the following formula in the first cell of the column where you want the dates displayed, say F1.

=DATE(YEAR($A$1),MONTH($A$1)+ROWS($F$1:F1),0)
Now grab the fill handle at the lower right corner and fill the formula down as far as it may be needed.

Collapse -

by csuu In reply to

Poster rated this answer.

Collapse -

by sgt_shultz In reply to formula for month end dat ...

my version of Excel (2000) has an EOMONTH function which returns the serial number of the end of the month. if you don't have that function, you may have to install it, Tools/Add-ins.

Collapse -

by sgt_shultz In reply to

here is the formula that will yield the end of the month date for January 03. change the last parameter (the month offset - the zero in this example) to 1 thru 11 for the rest of the months. remember to format the cells in the date format you want, else you just see a strange number..
=EOMONTH("01/01/03",0) -put in January cell
=EOMONTH("01/01/03",1) -put in february cell
and so on...

Collapse -

by sgt_shultz In reply to

oops. the thing you want to Add-In is the Analysis Pack....

Collapse -

by csuu In reply to

Poster rated this answer.

Collapse -

by ananthap In reply to formula for month end dat ...

In a cell, typing "date(2003,10,1) - 1" (without the quotes) will give you "September 30, 2001". That is, the first day of the next month MINUS 1 day.

End

Collapse -

by csuu In reply to

Poster rated this answer.

Collapse -

by csuu In reply to formula for month end dat ...

This question was closed by the author

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums