EDATE For END of Months - Copying Down A Formula To Produce List of Month END Dates 1

[PWD]

Good afternoon, I've just discovered EDATE - yes, I would like a medal!

I have used it to produce a list (Column F) of month commencing dates to be picked in a data-validated cell. Being the lazy and inquisitive sort I wonder if I hadn't already produced that list, so wasn't able to use it, was there a function that was 'smart enough' to do the same as:-

=EOMONTH(F4,0)

It won't stop any shows if there isn't - just wondering.

Many thanks,
DÇ$

 

[SkipVought]

hi,

Its not clear what you are asking.

EOMONTH() is specialized. But you can use DATE() as well.

Suppose that a2 has the first of the month. Then the last of the month (any month) is
[tt]
=DATE(YEAR(A2), MONTH(A2)+1,0)
[/tt]

 

[PWD]

Hi Skip I just want to produce a list of month end dates.

31/01/2015
28/02/2015
31/03/2015
30/04/2015
etc. etc.

Without having to type them all. As I've said I can produce a list of month starts using EDATE and then use EOMONTH to produce what I want i was just curious to see if it could be done in one step.

Many thanks,
D€$

 

[SkipVought]

I guess I still don't understand your question.

 

[zelgar]

If Cell A1 is 31/01/2015, and you want Cell A2 to be 28/02/2015, have the formula in A2 =EOMONTH(A1,1)
Copy & Paste the formula from A2 down

 

[1726]

An alternative is not to use a formula.

Enter the last date of a month in a cell. e.g. 31/01/2015 (I'm UK based!)

With the Right mouse button fill the date down the column.

On releasing the mouse select: Fill Months from the displayed menu.

Cheers

 

[PWD]

Ta da!!!!! I'm in the UK too.

Wow, I've never come across that feature before - not even accidentally. What a time saver - I checked and it gets leap years right too.

Many thanks,
D€$