在excel中使用vba添加x天数

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 =周五，2013年4月05日)>>周三，1900年1月03日

of course I need Tuesday, April 09, 2013

当然，我需要在2013年4月9日星期二。

Thanks

谢谢

3 个解决方案

#1

Have you used the DateAdd function?

你用过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

#2

I think this code is what your after using the DateAdd(<base e.g. Day = "D">, <number>, <date>) function:

我认为这段代码是您使用DateAdd后的代码( ，， )函数: ，例如day>

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 FALSE if no input is entered.
如果没有输入，输入函数将返回Boolean FALSE。
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 = AskForDeadlinePlus4 is its usage;
如果您想在另一个VBA代码中使用，i = AskForDeadlinePlus4是它的用法;
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
但是，您也可以在单元格中使用它，但只有在必要时，对于每个计算，这将提示输入，对于每个单元格，=AskForDeadlinePlus4;和
Plus I've added a check to see if a date was entered as the user may not enter a valid one.
另外，我还添加了一个检查，看看是否输入了日期，因为用户可能没有输入有效的日期。

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

#3

Instead of using DateAdd, which requires more typing, you could also use DateValue. Following would do it.

您还可以使用DateValue，而不是使用DateAdd，这需要更多的输入。后会做。

DateValue(strUserResponse )+I2

Another solution would be using the conversion function, CDate.

另一个解决方案是使用转换函数CDate。

CDate(strUserResponse )+I2

#1