Excel 2003 - VBA - Date Math

[Craig Brandt]

Guys,

Is there a real quick way to simply add 1 month to a date.
eg: Jan. 13,2008 + 1 month = Feb. 13, 2006 or Feb. 13, 2008 + 1 Month = Mar.
13, 2008.

Craig

 

[Peo Sjoblom]

What if the date is January 30th?

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

with your date in D9


--


Regards,


Peo Sjoblom

 

[Bob Phillips]

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

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mais qui est Paul]

Bonsour® Craig Brandt avec ferveur ;o))) vous nous disiez :

Is there a real quick way to simply add 1 month to a date.

Same day number or fixed duration ??

the notion of months as the duration is quite inconsistent!
1 month is either 28, 29, 30 or 31 days ????
what about :
Sunday, January 28, 2007 Wednesday, February 28, 2007
Monday, January 29, 2007 Wednesday, February 28, 2007
Tuesday, January 30, 2007 Wednesday, February 28, 2007
Wednesday, January 31, 2007 Wednesday, February 28, 2007

http://en.wikipedia.org/wiki/Injective_function

simply ???
=FLOOR(A1+(365.25/12),1)


even the ISO 8601 does not remove this inconsistency
http://en.wikipedia.org/wiki/ISO_8601#Durations

 

[Jim Cone]

Bob,
Nice one. I am keeping it.
Regards,
Jim Cone



"Bob Phillips"
wrote in message
=MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1}))

 

[Bob Phillips]

I am sure that you realise this, but you just change the {2,1} if you want
more than 1 month hence, such as {7,6} for 6 months hence (the {0,1} stays
fixed)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim Cone]

Bob,
I think I did, but I just made a note anyway.
Regards,
Jim Cone



"Bob Phillips"
<[email protected]>
wrote in message
I am sure that you realise this, but you just change the {2,1} if you want
more than 1 month hence, such as {7,6} for 6 months hence (the {0,1} stays
fixed)

 

[Craig Brandt]

Bob:

I tried working with the formula, but without success. Continued research on
my end, unearthed the following, which seems to fit the bill.

Range("C1") = DateAdd("m", 1, Range("C1"))

I'm going to continue to see if I can figure out where I went wrong with
your formula. Could it be the rev of Excel?

Thanks

 

[Mais qui est Paul]

Bonsour® Craig Brandt avec ferveur ;o))) vous nous disiez :

Guys,

Is there a real quick way to simply add 1 month to a date.
eg: Jan. 13,2008 + 1 month = Feb. 13, 2006 or Feb. 13, 2008 + 1 Month
= Mar. 13, 2008.

Craig

=Edate(A1,1)

! check the add-in utility analysis

 

[Bob Phillips]

I wouldn't have thought so. What version do you have?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Craig Brandt]

Excel 2003 SP3

I wouldn't have thought so. What version do you have?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Exactly the same as me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)