Adding a date from a specific cell into the DateAdd function

Hi

Been trying to implement this piece of code into my program and can't seem to get the following bit to work:

Range(eeDispatchDate) = DateAdd("yyyy", 5, " ") & Range(SenorityDate).Value

I basically want the date as from the senority date cell to be input to the function so i can add 5 years to it. I haven't changed the rest yet but will once i get this working. here is the full code below:

Sub cmdEMP_email_Dispatch_date_Click()

Dim AwardDue, eeDispatchDate, SenorityDate As String
Dim x, z As Integer

For x = 2 To Module1.GlobalCount

AwardDue = "G" & x 'where G is Award due i.e. 5 yrs
SenorityDate = "J" & x 'where J is Senority Date
eeDispatchDate = "L" & x 'Where L is Dispatch Date

'format the date fields to type Date (DD/MM/YY)
Range("L:L").Select
Selection.NumberFormat = "dd/mm/yy;@"

If (Range(AwardDue) = 5) Then
Range(eeDispatchDate) = DateAdd("yyyy", 5, " ") & Range(SenorityDate).Value
ElseIf (Range(AwardDue) = 10) Then
Range(eeDispatchDate) = Range(SenorityDate) + 3652
ElseIf (Range(AwardDue) = 15) Then
Range(eeDispatchDate) = Range(SenorityDate) + 5479
ElseIf (Range(AwardDue) = 20) Then
Range(eeDispatchDate) = Range(SenorityDate) + 7305
ElseIf (Range(AwardDue) = 25) Then
Range(eeDispatchDate) = Range(SenorityDate) + 9131
ElseIf (Range(AwardDue) = 30) Then
Range(eeDispatchDate) = Range(SenorityDate) + 10957
End If
Next

MsgBox "The Employee Email Dispatch date/s has now been updated. Please ensure you manually check this data before submission to GloboForce."

End Sub

I know its a syntactial error but just can't work it out! Any help is appreciated, thanks.



Answer this question

Adding a date from a specific cell into the DateAdd function

  • HKEC


    At first glance:

    DateAdd("yyyy", 5, " ") & Range(SenorityDate).Value

    should be:

    DateAdd("yyyy", 5, Range(SenorityDate).Value)

    cheers,

    Chris




  • joycemok

    Top class! Many thanks :D
  • Adding a date from a specific cell into the DateAdd function