Software

Ask something
Discussion Locked

formula for month end dates in Excel

0 helpful
9 replies

by csuu 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

9 replies

by dklippert

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.

by sgt_shultz

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.

by sgt_shultz

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...

by ananthap

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

This discussion is closed to new replies.