Dateadd equivalent Worksheet Function

[Derick Reinecke]

Hi,

Does anyone know of an equivalent worksheets function in
Excel for the DATEADD function.

I'm trying to take a date eg. 31/12/02 and subtract 3
months. One option to use is Date(Year(31/12/2002),Month
(31/12/2002)-3,day(31/12/2002)). However, this return
01/10/02. I think when substracting -3, this effectively
subtract 90 days. I need a function to take the number of
days in a month into account.

Any help would be appreciated.

Thanks

Derick

[Bernie Deitrick]

Derick,

Basically, you need to check if the month of the produced date is actually
three months before the entered date. For example, put your 31/12/2002 in
cell A1, then use the formula:

=IF(MONTH(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))<>MONTH(A1)-3,DATE(YEAR(A1),MON
TH(A1)-2,0),DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)))

This works because the 0th day of the month 2 months before the date is
actually the last day of the month 3 months before.

There are some slick array formulas that do this as well, but I think this
way is a little easier to read/understand.

HTH,
Bernie

"Derick Reinecke" <drei...@kio.uk.com> wrote in message
news:377201c2b8a6$bacb1af0$cef82ecf@TK2MSFTNGXA08...

[Bernie Deitrick]

And I should have added that the 31st day of a month with 30 days is the 1st
day of the following month, which is why you were getting 1/10/2002.

Bernie

"Bernie Deitrick" <dei...@consumer.org> wrote in message
news:eRI2cXNuCHA.2492@TK2MSFTNGP10...

[Norman Harker]

Hi Derick!

I'm not sure what the DATEADD algorithm was / is but I think that you have
quite a few choices.

Manually you can use Edit > Fill > Series and that will accept a base date
and positive and negative steps of months.

Then there's formula approaches.

Normal formula:

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

Modified formula:

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

In your case Addmons is negative but that doesn't produce any essential
problems.

The modified formula uses an algorithm that uses the same day in the month
or the last day of that month if the same day doesn't exist. That is the
same algorithm that Edit > Fill > Series uses.

It's based on a formula by Chip Pearson which I've adapted to produce a
general solution for any equal number of monthly gaps plus I've adapted by
absolutely referencing the base date so as to allow use for copying down or
accross to produce a series of such equally spaced dates. This thus allows
you to replicate by formula the algorithm used by Edit > Fill > Series.

There's nothing essentially wrong with the 'Normal' formula but most people
wouldn't like or agree with the answers given where, for example you add 3
months to 30-Nov-2003.

Normal returns 1-Mar-2004. Adapted formula returns 29-Feb-2004.

As another alternative you can use Analysis ToolPak's EDATE.

=EDATE(A1,Addmons)

A lot easier!

But the trouble comes with when you try to copy it down or across to
generate a series of such dates similar to that generated by Edit > Fill >
Series as applied to problem dates where DoM >=29.

You can correct for that using some row or column counting basis but my view
is why bother when we have one that works without reliance on Analysis
ToolPak being installed and selected as an Add-In.

Regards

The same works in reverse.

Whether you prefer adapted formula or normally recommended depends upon your
definition of months.

--
Norman Harker
Sydney, Australia.
njha...@optusnet.com.au

"Derick Reinecke" <drei...@kio.uk.com> wrote in message
news:377201c2b8a6$bacb1af0$cef82ecf@TK2MSFTNGXA08...

[J.E. McGimpsey]

Using months is problematic. Consider adding three months to 28 Feb
2003. Is the answer:

a) May 23, since that's 3 (28-day) months after 28 Feb? That would
make 1 March + 3 months = 2 June.
b) May 28, since the day is the same (28)? (1 June)
c) May 28, since thats 28 + 31 + 30 days after 28 Feb? (1 June)
d) May 29, since that's 90 days after 28 Feb? (30 May)
e) May 31, since that's the end of the month? (1 June)

If you want the date to be three months previous, meaning the month
number is 3 less than the base month and the day is the same as the
base day, or the end of the month, whichever is less, then this will
work:

A1: 31/12/2002
B1:
=MIN(DATE(YEAR(A1),MONTH(A1)-3,DAY(A1)),DATE(YEAR(A1),MONTH(A1)-2,0))

Of course, you could also use a wrapper for DateAdd:

Public Function DATEADD2(myInterval, myNumber, myDate) As Double
myDate = myDate + 1462 * ActiveWorkbook.Date1904
DATEADD2 = DateAdd(myInterval, myNumber, myDate)
End Function

In article <377201c2b8a6$bacb1af0$cef82ecf@TK2MSFTNGXA08>, Derick