# VBA Date from cell, then calculate 3 months later!

• I'm trying to figure out the VBA for an idea I have; I can do this via the formulas easy but I'm trying to get around it with VBA inside a form!

I have a userForm that has a textBox where I place a date (e.g. 20-10-2017) and I would like a script to place a 1 month, 3 months and 6 months dates in to 3 different cells, is this possible?

In other words: I enter 20-10-2017 and 3 different textboxes give me different results: 20-11-2017 (1 month), 20-01-17 (3 months) and 20-05-2017 (6 months).

My textboxes are called:

StartDate: (I enter the date here)
Reminder 1: (automatic date placed)
Reminder 3: (automatic date placed)
Reminder 6: (automatic date placed)

Could someone point me in the right direction please? Thanks in advance.

• Re: VBA Date from cell, then calculate 3 months later!

Assuming four text boxes and a command button.

On the command button, this code

• Re: VBA Date from cell, then calculate 3 months later!

Thanks Alan, works perfect for a text box. Will now try with a date picker.

• Re: VBA Date from cell, then calculate 3 months later!

Adding a fixed numbers of days (eg. 30, 90, 180) to a date will not return 1, 3 & 6 calendar months from the original date.

Try this, it should work for UK dating system or any dating system as long as the Locale setting and format of date entry in TextBox1 are the same.

Code
``````Private Sub CommandButton1_Click()
Dim i As Integer, Mnths, dt As Date
dt = TextBox1
Mnths = Array(1, 3, 6) '// These are the number of months to add.

For i = 1 To 3
Me.Controls("TextBox" & i + 1) = DateAdd("m", Mnths(i - 1), dt)
Next

End Sub``````

The DateAdd function is a VBA function that is not available as a worksheet function.

Syntax

The syntax for the DateAdd function in Microsoft Excel is:
DateAdd ( interval, number, date )

Parameters or Arguments

interval
The time/date interval that you wish to add. It can be one of the following values:

[TABLE="class: std_table"]

[tr]

[TH]Value[/TH]
[TH]Explanation[/TH]

[/tr]

[tr]

[td]

yyyy

[/td]

[td]

Year

[/td]

[/tr]

[tr]

[td]

q

[/td]

[td]

Quarter

[/td]

[/tr]

[tr]

[td]

m

[/td]

[td]

Month

[/td]

[/tr]

[tr]

[td]

y

[/td]

[td]

Day of the year

[/td]

[/tr]

[tr]

[td]

d

[/td]

[td]

Day

[/td]

[/tr]

[tr]

[td]

w

[/td]

[td]

Weekday

[/td]

[/tr]

[tr]

[td]

ww

[/td]

[td]

Week

[/td]

[/tr]

[tr]

[td]

h

[/td]

[td]

Hour

[/td]

[/tr]

[tr]

[td]

n

[/td]

[td]

Minute

[/td]

[/tr]

[tr]

[td]

s

[/td]

[td]

Second

[/td]

[/tr]

[/TABLE]

number
The number of intervals that you wish to add.dateThe date to which the interval should be added.

date
The date or time to which the interval should be added.

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: VBA Date from cell, then calculate 3 months later!

Thank you KJ, playing around with the code to get it working. Would you know if this could work in a DTPicker because I'm not having much luck making it work?

I've changed the name of the DTPicker to 'StartDate' and the 3 texboxes are called 'Reminder1', 'Reminder3' and 'Reminder6'.

• Re: VBA Date from cell, then calculate 3 months later!

Try this

Code
``````Private Sub CommandButton1_Click()
Dim i As Integer, Mnths, dt As Date
dt = StartDate.Value
Mnths = Array(1, 3, 6) '// These are the number of months to add.

For i = 0 To 2
Me.Controls("Reminder" & Mnths(i)) = DateAdd("m", Mnths(i), dt)
Next

End Sub``````

I never use Excel's built-in Date Picker (especialy now as it is unavailable in Excel 2016 64Bit), there are plenty of better alternatives available as Add-ins, such as Ron de Bruin's, see here: https://www.rondebruin.nl/win/addins/datepicker.htm

If you are using Excel's built-in date Picker then you MAY be able to do away with the command button and have the 3 text boxes fill automatically when the date in the Date Picker changes by using

Code
``````Private Sub StartDate_Change()
Dim i As Integer, Mnths, dt As Date
dt = StartDate.Value
Mnths = Array(1, 3, 6) '// These are the number of months to add.

For i = 0 To 2
Me.Controls("Reminder" & Mnths(i)) = DateAdd("m", Mnths(i), dt)
Next

End Sub``````

I am not 100% sure if excel's Date Picker even has a Change Event property!

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: VBA Date from cell, then calculate 3 months later!

Works perfectly, thank you so much.

• Re: VBA Date from cell, then calculate 3 months later!

You're welcome.

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!