ExcelBanter - dates

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - dates (https://www.excelbanter.com/excel-programming/301279-dates.html)

I have a cell in a spreadsheet with Today() in it which
returns the current system date. Is there a way of adding
a month on to this. I know about the DateAdd function in
VBA, but would rather use a cell formula if possible.

TIA

LiSa

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

Gord Dibben Excel MVP

On Sat, 12 Jun 2004 11:51:40 -0700, "LiSa"
wrote:

I have a cell in a spreadsheet with Today() in it which
returns the current system date. Is there a way of adding
a month on to this. I know about the DateAdd function in
VBA, but would rather use a cell formula if possible.

TIA

It all depends on how you define a "month". For example, the formula
suggested will yield 1-Oct-2004 if you add a month to 31-Aug-2004. The
formula is correct; it gives inconsistent results solely due to the
peculiarities of our calendar in which months can have anywhere from 28 to
31 days.

--

Vasant

"LiSa" wrote in message
...
I have a cell in a spreadsheet with Today() in it which
returns the current system date. Is there a way of adding
a month on to this. I know about the DateAdd function in
VBA, but would rather use a cell formula if possible.

TIA

Thanks that works great, but I still have to reference the
cell D1 with =Today() in it.
Is there any way I can have it all in one cell?
-----Original Message-----
one way
=DATE(YEAR(D1),MONTH(D1)+1,DAY(D1))

--
Don Guillett
SalesAid Software

"LiSa" wrote in
message
...
I have a cell in a spreadsheet with Today() in it which
returns the current system date. Is there a way of
adding
a month on to this. I know about the DateAdd function in
VBA, but would rather use a cell formula if possible.

TIA

.

Hi Lisa!

If the Day of Month is likely to be equal to or greater than 29, you
need to make sure what you want.

Take:
A1: 31-Jan-2004

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Returns: 2-Mar-2004

And:
A1: 31-Jan-2005

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Returns: 1-Mar-2005

If you prefer the last day of the month in cases where the day does
not exist, use the formula:

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE( YEAR(A1),MONTH(A1)+2,0))))

This is based upon a general formula that can be copied down or across
of:

=DATE(YEAR(A1),MONTH(A1)+AddMons,MIN(DAY($A$1),DAY (DATE(YEAR(A1),MONTH(A1)+AddMons+1,0))))

Where AddMons is the number of months to be added.

And here’s an alternative that does the same by Peter Dorigo:

=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))

For one off situations not involving copying down or accross, if you
can rely upon Analysis ToolPak being installed and selected, you can
use:

=EDATE(A1,1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

"LiSa" wrote in message
...
I have a cell in a spreadsheet with Today() in it which
returns the current system date. Is there a way of adding
a month on to this. I know about the DateAdd function in
VBA, but would rather use a cell formula if possible.

TIA