Calc date one year from today

[Ken]

Im trying to calculate a date range that will monitor
warranty (364 days) from date of purchase order ??

Thanks for the help
Ken

 

[Norman Harker]

Hi Ken!

Use:

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

It will even cover the difference between Leap Years and non-Leap
years which your 364 has a problem with.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[ken]

Worked like a charm.

Thank you very much for your time

Ken

 

[Ken]

I forgot one important part. In the fourmula how would I
calculate for the following: 90 days, 180 days

 

[Frank Kabel]

Hi
do you wanr 90 / 180 days or rather 3 / 6 months?

 

[Guest]

I think I have it
=DATE(YEAR(I2),MONTH(I2)+3,DAY(I2)-1) This would = 90
=DATE(YEAR(I2),MONTH(I2)+6,DAY(I2)-1) This would = 180

Ken

 

[Norman Harker]

Hi Ken!

Use:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)
and:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+180)

The day argument will carry forwards to the next month if it exceeds
the number of days in the month in A1.

And here, from my collection of items of rare errors:

"The limit to the Days is set at 32760 but if you exceed that number
you get the date plus 32760 days with no warning that you've exceeded
the limit. Not likely to arise much but still not desirable that you
should get the wrong answer." With months the limit is 32764 but you
get #NUM! if you exceed it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
though this will work in most cases you'll get into problems if I2 is
the end of a month. e.g. 08/31/2004 + 3 months would result in
11/31/2004 -> 12/01/2004

A better formula for theses cases would be
=DATE(YEAR(A1),MONTH(A1)+added_months,MIN(DAY(A1),DAY(DATE(YEAR(A1),MON
TH(A1)+added_months+1,0))))
where A1 stores your date and added_months is the added month value

 

[Norman Harker]

Hi!

These are not correct. Unfortunately 3 and 6 month periods have
different numbers of days in them

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Frank!

Re: Adding (or subtracting) months.

A minor nuance but which can solve a major problem of copying down or
across:
=DATE(YEAR(A1),MONTH(A1)+added_months,MIN(DAY($A$1),DAY(DATE(YEAR(A1),
MONTH(A1)+added_months+1,0))))

If you make the base month's day absolute reference you can then copy
down or across

Alternative for adding months is:

=EDATE($A$1,(ROW(A2)-ROW($A$1))*AddMons) '[Adds number specified in
named cell AddMons]

If our requirement is for dates to be across the page, then substitute
ROW by COLUMN. In most cases I would not use $A$1 but would use a
named cell e.g. "BaseDate" and A2 would be replaced by the address of
the cell adjacent to "BaseDate" and is the second in the series of
dates.

Or there's an approach used by Peter Dorigo:
=MIN(DATE(YEAR(A1),MONTH(A1)+ AddMons +{1,0},DAY($A$1)*{0,1}))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ken]

OK, I see where I was put 90 months. This is now
working.

Thanks