Weeks + date calculations in vba

[winram05]

Hi,

I am creating excel database (input via Userform) where i am facing two issues. could someone help me on below:


1. I am entering data through form and it is saving in sheet2 . The data contains numbers, date, text etc. But the numbers are saving in text format which is difficult for excel calculations.

2. Day + weeks = Day (example: 06/29/2017 (any day) + 2 weeks (14 days) = 07/13/2017)

in form, there is date picker and combo box to select the weeks. if we select date and weeks then in text box it should display the expected date (07/13/2017)

 

[CalcSux78]

When you use CDate(textbox.value) it will convert the value to a date value (instead of a text value.) For my example, TextBox1 is the entered date, ComboBox1 is the number of weeks, and TextBox2 is the new date to send to the worksheet.

Private Sub ComboBox1_Change()
n = ComboBox1
TextBox2 = Format(CDate(TextBox1) + 7 * n, "mm/dd/yyyy")
End Sub


Private Sub CommandButton1_Click()
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1) = CDate(TextBox2)
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim n%
n = ComboBox1
TextBox1 = Format(TextBox1, "mm/dd/yyyy")
TextBox2 = Format(CDate(TextBox1) + 7 * n, "mm/dd/yyyy")
End Sub