Add or Subtract X Days From Date

  • <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"><html><head><meta http-equiv="Content-Language" content="en-us"><title>Add/Subtract Dates In Excel VBA</title><meta name="description" content="2 Criteria VLOOKUP In Excel"><meta name="keywords" content="2 Criteria, VLOOKUP,Excel"><link rel="STYLESHEET" href="http://www.ozgrid.com/css/ozgrid.css" type="text/css"><script type="text/javascript" src="http://www.ozgrid.com/SideNavJS/stmenu.js"></script>
    </head><body><div align="center"></div><h1>Add/Subtract Dates In Excel VBA</h1><h2>EXCEL VBA: Create A Date Adder</h2><p>Here's how to create relatively easy Date Adder by using a UserForm, 3 TextBox Controls, 5 Label Controls, 1 ComboBox Control and 1 CommandButton Control.</p><h2 align="center">&nbsp;<b><a href="http://www.ozgrid.com/download/DateAdder.zip">DOWNLOAD DATEADDER</a></b></h2><blockquote><ol style="line-height: 200%"><li>TextBox1 = Start Date</li><li>TextBox2 = Positive/Negative Amount</li><li>ComboBox1 = Period. <i>I.e Days, Weeks, Months, Year</i></li><li>CommandButton1 = Add/Take\</li><li>Label1 = Result</li></ol><h2>CODE</h2></blockquote>
    <pre>
    Dim strPeriod As String



    <b>Private Sub ComboBox1_Change()
    </b> If ComboBox1.ListIndex = -1 Then
    MsgBox &quot;Invalid period&quot;, vbCritical
    ComboBox1.SetFocus
    Exit Sub
    End If

    With ComboBox1
    If .ListIndex = 0 Then strPeriod = &quot;D&quot;
    If .ListIndex = 1 Then strPeriod = &quot;WW&quot;
    If .ListIndex = 2 Then strPeriod = &quot;M&quot;
    If .ListIndex = 3 Then strPeriod = &quot;YYYY&quot;
    End With


    <b>End Sub
    </b>


    <b>Private Sub CommandButton1_Click()
    </b>
    If Not IsDate(TextBox1) Then
    MsgBox &quot;Non valid date&quot;, vbCritical
    TextBox1 = vbNullString
    TextBox1.SetFocus
    Exit Sub
    End If

    If Not IsNumeric(TextBox2) Then
    MsgBox &quot;Invalid amount&quot;, vbCritical
    TextBox2 = vbNullString
    TextBox2.SetFocus
    Exit Sub
    End If

    If ComboBox1.ListIndex = -1 Then
    MsgBox &quot;Invalid period&quot;, vbCritical
    ComboBox1.SetFocus
    Exit Sub
    End If

    Label1 = Format(DateAdd(strPeriod, TextBox2, TextBox1), &quot;dddd dd mmm yyyy&quot;)
    <b>End Sub
    </b>


    <b>Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    </b>If TextBox1 = vbNullString Then Exit Sub
    If Not IsDate(TextBox1) Then
    MsgBox &quot;Non valid date&quot;, vbCritical
    TextBox1 = vbNullString
    Cancel = True
    End If
    <b>End Sub</b>




    <b>Private Sub UserForm_Initialize()
    </b> ComboBox1.List = Split(&quot;Day,Week,Month,Year&quot;, &quot;,&quot;)
    <b>End Sub</b></pre></body></html>