CODEWITHSUNDEEP
excelvba
Andrei Ion
Andrei Ion

Reputation: 1827

Subtracting from a date in VBA?

I'm having big problems doing operation with the date in Excel VBA. I have a form that has a textbox where the user will enter the date. The problem is that he may enter it in different formats (eg, 1.08.2011 for 1st of August, or 8/1/11 for the same day). Now what I want to do is to subtract some days from that date that he enters in the TextBox. I had to success so far and I don't know how to do it. I tried something like this

Format((Format(Me.datalivrare.Value, "dd.mm.yyy") - 4), "dd.mm.yyyy")

Where datalivrare is that textbox where the user enters the date and 4 is the number of days I want to subtract from that date... and I want the format to always be dd.mm.yyyy no matter what they enter in that textbox.

Upvotes: 16

Views: 102832

Answers (5)

FreeSoftwareServers
FreeSoftwareServers

Reputation: 2791

Just a simple answer, as many aren't using the OP's code.

Eg: Minus 4 days

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dateadd-function

Sub DateTest()
 Dim DateVar As Date
 DateVar = DateAdd("d", -4, Date)
 Debug.Print DateVar
End Sub

Upvotes: 0

CoveGeek
CoveGeek

Reputation: 435

It is important to check if the user entered a value that VBA can interprit as a date so first you should:

If isDate(Me.datalivrare.Value) Then
    str_Date = Format(DateAdd("d", -4, CDate(Me.datalivrare.Value)), "dd.mm.yyyy")
Else
    MsgBox "Not a valid date value", vbCritical + vbOkOnly, "Invalid Entry"
End If

I think bluefeet's answer had the most information so far and I borrowed the use of DateAdd and CDate.

Upvotes: 2

Lance Roberts
Lance Roberts

Reputation: 22842

First cast to Date, then subtract days, then format appropriately:

Format(DateAdd("d", -4, CDate(Me.datalivrare.Value)), "dd.mm.yyyy")

Upvotes: 7

Taryn
Taryn

Reputation: 247680

I suggest looking at the DateAdd function for VBA

http://www.techonthenet.com/excel/formulas/dateadd.php

http://office.microsoft.com/en-us/access-help/dateadd-function-HA001228810.aspx

You could do the following:

Format(DateAdd("d", -4, CDate(Me.datalivrare.Value)), "dd.mm.yyyy")

Upvotes: 32

Einacio
Einacio

Reputation: 3532

the best to add and substract from dates on vba is dateadd() (with negative number for substractions) also, in your example code there's a missing y on the format string (it accepts 1, 2 or 4 y, only)

Upvotes: 6

Related Questions