DateDiff gives incorrect year difference value

**murraymr** · 08-29-2011, 01:25 PM

Last week I was helped to get the solution to moving down a column, and to updating a another column as the result of the calculation below. YearsofService is defined as an integer in the VBA code.

YearsofService = DateDiff("yyyy", c.Value, Date)

When c.value = 06/09/2003 I would expect the years difference to be 7 until next Tuesday. It is coming up as 8. The date format for c.value is dd/mm/ccyy and the locale English(UK).

What have I done wrong?

Murray

**shg** · 08-29-2011, 01:42 PM

If you format c as General, what number appears?

More simply, if you restore Excel's default alignment, does it align left or right?

**murraymr** · 08-29-2011, 01:56 PM

37870 formatted left

**shg** · 08-29-2011, 03:00 PM

Ah. Unlike DATEDIF, from Help,

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

Similarly, DateDiff("m", "12/31/2003", "1/1/2004") returns 1.

**murraymr** · 08-30-2011, 02:30 AM

Here is the code that was using DateDiff. I have tried to modify it to use DateDif, but it is not accepted. How can I execute a formula in a macro or VBA module?

Private Sub Workbook_Open()
Dim YearsofService As Integer, lngLastRow As Long, rngDates As Range

lngLastRow = Sheets("Basic Info").Range("R65536").End(xlUp).Row
Set rngDates = Sheets("Basic Info").Range("R2:R" & lngLastRow)
For Each c In rngDates
    If c.Value > 0 Then
        YearsofService = DateDiff("yyyy", c.Value, Date)
        If YearsofService < 5 Then
            c.Offset(0, 7) = "1 month"
        End If
        If YearsofService = 5 Then
            c.Offset(0, 7) = "5 weeks"
        End If
        If YearsofService = 6 Then
            c.Offset(0, 7) = "6 weeks"
        End If
        If YearsofService = 6 Then
            c.Offset(0, 7) = "6 weeks"
        End If
        If YearsofService = 7 Then
            c.Offset(0, 7) = "7 weeks"
        End If
        If YearsofService = 8 Then
            c.Offset(0, 7) = "8 weeks"
        End If
        If YearsofService = 9 Then
            c.Offset(0, 7) = "9 weeks"
        End If
        If YearsofService = 10 Then
            c.Offset(0, 7) = "10 weeks"
        End If
        If YearsofService = 11 Then
            c.Offset(0, 7) = "11 weeks"
        End If
        If YearsofService = 12 Then
            c.Offset(0, 7) = "12 weeks"
        End If
        If YearsofService > 12 Then
            c.Offset(0, 7) = "12 weeks"
        End If

    End If
Next c
End Sub

I have tried adding a column to hold the formula and its result and updating the macro to use the resulting value, but the Dim line is found to be in error.

Sub UpdateonOpen()
   Dim c.value As Integer, lngLastRow As Long, rngDates As Range

lngLastRow = Sheets("Basic Info").Range("R65536").End(xlUp).Row
Set rngDates = Sheets("Basic Info").Range("X2:X" & lngLastRow)
For Each c In rngDates
    If c.Value > 0 Then
        If c.Value < 5 Then
            c.Offset(0, 2) = "1 month"
        End If
        If c.Value = 5 Then
            c.Offset(0, 2) = "5 weeks"
        End If
        If c.Value = 6 Then
            c.Offset(0, 2) = "6 weeks"
        End If
        If c.Value = 6 Then
            c.Offset(0, 2) = "6 weeks"
        End If
        If c.Value = 7 Then
            c.Offset(0, 2) = "7 weeks"
        End If
        If c.Value = 8 Then
            c.Offset(0, 2) = "8 weeks"
        End If
        If c.Value = 9 Then
            c.Offset(0, 2) = "9 weeks"
        End If
        If c.Value = 10 Then
            c.Offset(0, 2) = "10 weeks"
        End If
        If c.Value = 11 Then
            c.Offset(0, 2) = "11 weeks"
        End If
        If c.Value = 12 Then
            c.Offset(0, 2) = "12 weeks"
        End If
        If c.Value > 12 Then
            c.Offset(0, 2) = "12 weeks"
        End If

    End If
Next c

End Sub

Help! I am a complete newbie to Excel programming

Murray

**murraymr** · 08-30-2011, 02:57 AM

I realised that, when I replaced YearsofService with 'c.value' this replacement updated the Dim line as well. When I removed 'c.value' from it, it worked!

Thank you for the DateDif tip

Murray