month wise increased date format

[majidsiddique]

Hi all,
I need date format as like below. please help me.
Thanks.
i use Edate(A1,1) but desired result not achieved.
[TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]
31-Jan-18[/TD]
[/TR]
[TR]
[TD="align: right"]28-Feb-18[/TD]
[/TR]
[TR]
[TD="align: right"]31-Mar-18[/TD]
[/TR]
[TR]
[TD="align: right"]30-Apr-18[/TD]
[/TR]
[TR]
[TD="align: right"]31-May-18[/TD]
[/TR]
[TR]
[TD="align: right"]30-Jun-18[/TD]
[/TR]
[TR]
[TD="align: right"]31-Jul-18[/TD]
[/TR]
[TR]
[TD="align: right"]31-Aug-18[/TD]
[/TR]
[TR]
[TD="align: right"]30-Sep-18[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Try this instead:
=EDATE(A1+1,1)-1

 

[majidsiddique]

Thanks Joe4,
you are so smart.

 

[Joe4]

You are welcome!

The trick is to add one to the first end date, which brings you to the first day of the next month. Then add one month to that date, and subtract one day from that.
So that accounts for the months all having different end dates.

 

[Phuoc]

i use Edate(A1,1) but desired result not achieved.

Use EOMONTH() function:

=EOMONTH(A1,1)

 

[Joe4]

Use EOMONTH() function:

=EOMONTH(A1,1)

Yes, that is even easier yet.
Cannot believe I forgot about that function!

 

[Phuoc]

Another way is to use the EDATE function, but it's a bit long:

=EDATE($A$1,ROWS($A$1:A1))

 

[majidsiddique]

Thanks Phuoc.