=DATE(YEAR, MONTH, DAY) stop month increasing.

**oneblondebrow** · 02-01-2016, 07:58 AM

Hi all,

I have attached a test sheet in which i am trying to have a formula where in CELL A1 i enter a date and then this date increases to the last day of the month and then repeats 14 times, i used the formula.

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)

This works for every date apart from the 1st of the months as it jumps a month (see highlighted in yellow on test sheet).

Any idea how i can stop this, Or maybe there is a better way to do this formula, Has to be simple though and no macro.

Thanks all.

**shukla.ankur281190** · 02-01-2016, 08:16 AM

Try

A2=IF(DATE(YEAR($B$1), MONTH($B$1), DAY(B1)+1)=EOMONTH(B1,0),$B$1,DATE(YEAR($B$1), MONTH($B$1), DAY(B1)+1)) and drag down

**JohnTopley** · 02-01-2016, 08:21 AM

In A2 and copy down

=IF(A1+1>EOMONTH($A$1,0),$A$1,A1+1)

**Jonmo1** · 02-01-2016, 09:32 AM

This is over complicated
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)

It can simply be
=A1+1

Try this, without the need for the Analysis Toolpack addin (so it can be backward compatible with XL2003).
=IF(DAY(A1+1)=1,A$1,A1+1)

=DATE(YEAR, MONTH, DAY) stop month increasing.

LinkBack

Thread Tools

Rate This Thread

Display

=DATE(YEAR, MONTH, DAY) stop month increasing.

Re: =DATE(YEAR, MONTH, DAY) stop month increasing.

Re: =DATE(YEAR, MONTH, DAY) stop month increasing.

Re: =DATE(YEAR, MONTH, DAY) stop month increasing.

Thread Information

Users Browsing this Thread

Similar Threads

Running Totals from 24 hours to month, month to year without year decreasing

Sort month/date/year data using month and date only

How can i show date in this form (month,last date of that month, Year)?

Need to convert Month/Date/Year to Year/Month/Date so excel will recognize

[SOLVED] Display the first date of the month when the month and year are selected

If Column A Month and Year = ColumnB Todays Month and Year then send email

formula for prior month & year of a month end date.

Bookmarks

Bookmarks

Posting Permissions