VBA For Dates

[raf]

Is this possible:

user will enter any date to a textbox1 and another 3 text boxes will display: the 1st anniversary date, 2nd anniversary date and Last day of the previous month before the 3rd anniversary month.

For eg: textbox1: June 1, 2010
textbox2: June 1, 2011
textbox3: May 31, 2013

 

[Smallman]

@raf

The textbox 3 value is 31 May 2012 not 2013. The formula to achieve the result based on putting the first date in B2 is:

1st
=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2))

2nd
=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))

3rd
=DATE(YEAR(B2)+3,MONTH(B2),DAY(B2)-1)

Take care

Smallman

 

[raf]

thank you so much smallman! now that answer made me the smallest...

 

[Smallman]

Not a problem @raf.

See you next time.

Smallman

 

[raf]

Not a problem @raf.

See you next time.

Smallman

smallman, I have follow up question, i really tried to create formula for this using the dates instruction you gave.. but cant compose a working one:

What if the user will input any date (day value is greater than one) in the Txtbox 1 and
another 3 text boxes will display: the 1st anniversary date, 2nd anniversary date and Last day of the 3rd anniversary month.

For eg:
Input: June 2, 2009
textbox1: June 2, 2010
textbox2: June 2, 2011
textbox3: June 30, 2012

Input: JAN 20, 2009
textbox1: JAN 20, 2010
textbox2: JAN 20, 2011
textbox3: June 31, 2012

1st
=DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)) ----I'll use this

2nd
=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2)) ----I'll use this


3rd cant compose code for this.. :D


thanks man!

 

[NARAYANK991]

Hi ,

If you have Excel 2007 or later versions , you can use the EDATE function , as follows :

=EDATE(B2,37)-DAY(B2)

Narayan

 

[raf]

Hi ,

If you have Excel 2007 or later versions , you can use the EDATE function , as follows :

=EDATE(B2,37)-DAY(B2)

Narayan

Thanks man! I appreciate this so much!