VBA Date from cell, then calculate 3 months later!

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

Assuming four text boxes and a command button.

On the command button, this code

Option Explicit




Private Sub CommandButton1_Click()
Dim tDate As Date
tDate = Me.TextBox1.Value
Me.TextBox2 = CDate(DateValue(Me.TextBox1) + 30)
Me.TextBox3 = CDate(DateValue(Me.TextBox1) + 60)
Me.TextBox4 = CDate(DateValue(Me.TextBox1) + 90)




End Sub

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.

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"]

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

yyyy

Year

q

Quarter

m

Month

y

Day of the year

d

Day

w

Weekday

ww

Week

h

Hour

n

Minute

s

Second

[/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.

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

Try this

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

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!

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

You're welcome.