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
我在用Excel VBA做操作时遇到了大问题。我有一个表单,它有一个文本框,用户可以在其中输入日期。问题是他可能会以不同的格式输入(例如,8月1日的1.08.2011,或者同一天的8/1/11)。现在我要做的是减去他在文本框中输入的日期。到目前为止,我已经取得了成功,但我不知道如何去做。我试过了
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.
datalivrare是用户输入日期的文本框,4是我想从日期中减去的天数。我希望格式总是。mm。yyyy,不管他们在那个文本框里输入什么。
4 个解决方案
#1
24
I suggest looking at the DateAdd function for VBA
我建议查看VBA的DateAdd函数
http://www.techonthenet.com/excel/formulas/dateadd.php
http://www.techonthenet.com/excel/formulas/dateadd.php
http://office.microsoft.com/en-us/access-help/dateadd-function-HA001228810.aspx
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")
#2
6
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)
在vba上添加和减除日期的最佳方法是dateadd()(下标为负数),而且,在示例代码中,格式字符串中缺少y(它只接受1、2或4 y)
#3
5
First cast to Date, then subtract days, then format appropriately:
首先是到日期,然后减去天数,然后适当格式化:
Format(DateAdd("d", -4, CDate(Me.datalivrare.Value)), "dd.mm.yyyy")
#4
1
It is important to check if the user entered a value that VBA can interprit as a date so first you should:
重要的是要检查用户是否输入了VBA可以作为日期插入的值,所以首先您应该:
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.
我认为bluefeet的回答提供了最多的信息,我借用了DateAdd和CDate的用法。
#1
24
I suggest looking at the DateAdd function for VBA
我建议查看VBA的DateAdd函数
http://www.techonthenet.com/excel/formulas/dateadd.php
http://www.techonthenet.com/excel/formulas/dateadd.php
http://office.microsoft.com/en-us/access-help/dateadd-function-HA001228810.aspx
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")
#2
6
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)
在vba上添加和减除日期的最佳方法是dateadd()(下标为负数),而且,在示例代码中,格式字符串中缺少y(它只接受1、2或4 y)
#3
5
First cast to Date, then subtract days, then format appropriately:
首先是到日期,然后减去天数,然后适当格式化:
Format(DateAdd("d", -4, CDate(Me.datalivrare.Value)), "dd.mm.yyyy")
#4
1
It is important to check if the user entered a value that VBA can interprit as a date so first you should:
重要的是要检查用户是否输入了VBA可以作为日期插入的值,所以首先您应该:
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.
我认为bluefeet的回答提供了最多的信息,我借用了DateAdd和CDate的用法。