Excel 2003 - VBA - Date Math

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

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
 
What if the date is January 30th?

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

with your date in D9


--


Regards,


Peo Sjoblom
 
=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)
 
Bonsour® Craig Brandt avec ferveur ;o))) vous nous disiez :
Is there a real quick way to simply add 1 month to a date.
Click to expand...

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
 
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}))
 
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)
 
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)
 
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
 
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
Click to expand...

=Edate(A1,1)

! check the add-in utility analysis
 
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)
 
Excel 2003 SP3

Bob Phillips said:
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)
Click to expand...
 
Exactly the same as me.

--
---
HTH

Bob


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