DateDiff gives incorrect year difference value

[murraymr]

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.

Please Login or Register  to view this content.

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]

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]

37870 formatted left

[shg]

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]

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?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Help! I am a complete newbie to Excel programming

Murray

[murraymr]

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