vba 在excel中使用vba将x天数添加到日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16259166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add x number of days to a date with vba in excel
提问by xyz
I am tring to add x number of days to a Long date with a pop up box.
我想通过弹出框将 x 天数添加到长日期。
Public Function AskForDeadlinePlus4() As String
Dim strUserResponse As String
strUserResponse = InputBox("Enter Validuntil Date: Add # of Days To Survey end date")
strUserResponse = FormatDateTime(strUserResponse + I2, vbLongDate)
ActiveSheet.Cells(2, 10).Value = strUserResponse 'the 2, 10 is the cell reference for J2 - row 2, column 10.
End Function
Where Survey end date in cell I2.
其中，单元格 I2 中的调查结束日期。
When I run this I get (Googling how to do this I am tiring)
当我运行它时，我得到（谷歌搜索如何做到这一点我很累）
4 + I2(where I2 = Friday, April 05, 2013) >>Wednesday, January 03, 1900
4 + I2（其中I2 = Friday, April 05, 2013）>>Wednesday, January 03, 1900
of course I need Tuesday, April 09, 2013
我当然需要 Tuesday, April 09, 2013
Thanks
谢谢
回答by David Zemens
Have you used the DateAddfunction?
你用过这个DateAdd功能吗？
Sub DateExample()
Dim strUserResponse As String '## capture the user input'
Dim myDate As Date '## the date you want to add to'
Dim numDays As Double '## The number of days you want to add'
strUserResponse = InputBox("Enter Validuntil Date: Add # of Days To Survey end date")
numDays = InputBox("How many days to add?")
myDate = CDate(strUserResponse)
MsgBox DateAdd("d", numDays, myDate)
End Sub
回答by glh
I think this code is what your after using the DateAdd(<base e.g. Day = "D">, <number>, <date>)function:
我认为这段代码是你使用该DateAdd(<base e.g. Day = "D">, <number>, <date>)函数后的代码：
Public Function AskForDeadlinePlus4() As String
Dim strUserResponse As Date, iNumber As Long, rResponse As Variant
AskForDeadlinePlus4 = "" 'set default value
iNumber = CLng([I2])
rResponse = InputBox("Enter Validuntil Date: Add " & iNumber & " Day(s) To Survey end date")
If rResponse = False Then
'no value entered
Exit Function
ElseIf Not IsDate(rResponse) Then
'no date entered
Exit Function
Else
'valid date entered
strUserResponse = DateAdd("D", iNumber, CDate(rResponse))
End If
AskForDeadlinePlus4 = FormatDateTime(strUserResponse, vbLongDate)
End Function
Just a few points though:
只是几点：
- The input function will return the Boolean FALSEif no input is entered.
- The test you used above is a function and will return a value when used
- If you want to use in in another VBA code,
i = AskForDeadlinePlus4is its usage; - But you can also use it in a cell but only when necessary as with every calculation this will prompt an input and for every cell its in,
=AskForDeadlinePlus4; and - Plus I've added a check to see if a date was entered as the user may not enter a valid one.
- 如果没有输入，输入函数将返回布尔值FALSE。
- 你上面使用的测试是一个函数，使用时会返回一个值
- 如果要在另一个VBA代码中使用，
i = AskForDeadlinePlus4是它的用法； - 但是您也可以在单元格中使用它，但仅在必要时，因为每次计算都会提示输入，并且对于每个单元格中的输入，
=AskForDeadlinePlus4；和 - 另外，我添加了一个检查以查看是否输入了日期，因为用户可能没有输入有效的日期。
If you want to use in VBA:
如果要在 VBA 中使用：
Sub GetInfo()
'the 2, 10 is the cell reference for J2 - row 2, column 10.
ActiveSheet.Cells(2, 10).Value = AskForDeadlinePlus4
End Sub
回答by ExcelinEfendisi
Instead of using DateAdd, which requires more typing, you could also use DateValue. Following would do it.
除了使用需要更多输入的 DateAdd，您还可以使用 DateValue。以下会做。
DateValue(strUserResponse )+I2
Another solution would be using the conversion function, CDate.
另一种解决方案是使用转换函数 CDate。
CDate(strUserResponse )+I2
