CODEWITHSUNDEEP
excelvbadatetimeoffset
leo pd
leo pd

Reputation: 35

How to count the number of Years between two dates

This macro counts the number of years between the first and last date of a work experience. I made this work but getting this number in a weird format (Like a Date instead of a number). Looks like that something might be wrong. Thanks in advance for your comments. 

Sub CuentaExperiencia()
Worksheets("Candidatos").Activate
'esta macro contabiliza los años de experiencia sumados

Range("T2").Select
Do Until ActiveCell.Offset(0, -18).Value = ""
    If ActiveCell.Value = "" Then
    ActiveCell.FormulaR1C1 = DateDiff("d", (ActiveCell.Offset(0, -2)), (ActiveCell.Offset(0, -1)))
    End If
    ActiveCell.Offset(1, 0).Select
Loop

Range("AE2").Select
Do Until ActiveCell.Offset(0, -29).Value = ""
    If ActiveCell.Value = "" Then
    ActiveCell.FormulaR1C1 = DateDiff("d", (ActiveCell.Offset(0, -2)), (ActiveCell.Offset(0, -1)))
    End If
    ActiveCell.Offset(1, 0).Select
Loop

Range("AP2").Select
Do Until ActiveCell.Offset(0, -40).Value = ""
    If ActiveCell.Value = "" Then
    ActiveCell.FormulaR1C1 = DateDiff("d", (ActiveCell.Offset(0, -2)), (ActiveCell.Offset(0, -1)))
    End If
    ActiveCell.Offset(1, 0).Select
Loop

Range("BA2").Select
Do Until ActiveCell.Offset(0, -51).Value = ""
    If ActiveCell.Value = "" Then
    ActiveCell.FormulaR1C1 = DateDiff("d", (ActiveCell.Offset(0, -2)), (ActiveCell.Offset(0, -1)))
    End If
    ActiveCell.Offset(1, 0).Select
Loop


End Sub

Upvotes: 1

Views: 5176

Answers (2)

Chilangosta
Chilangosta

Reputation: 101

Just use the =DAYS(end_date,start_date) formula, and then divide by 365 to get the number of years; no need to use VBA. If you are doing it as part of a larger VBA macro, you can just use WorksheetFunction.Days(end_date,start_date).

EDIT: Even easier: just use =YEARFRAC(). It'll do it all for you.

You should note that simply dividing by 365 does not account for leap years, which introduces some error into the answer. While this error is trivial in this case, you may avoid it simply by using =YEARFRAC(start_date, end_date, [basis]), and using 1 as the basis parameter. This will force the use of actual calendar days, which will account for leap year correctly. Otherwise, YEARFRAC assumes a standard 30-day month for every month, and your answer will be increasingly inexact.

Upvotes: 3

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Or you can use Datediff function like this...

DateDiff("yyyy", StartDate, EndDate)

Sample code:

Sub NumberOfYears()
Dim StartDate As Date
Dim EndDate As Date
Dim Years As Integer

StartDate = #1/1/2015#
EndDate = #12/31/2017#

Years = DateDiff("yyyy", StartDate, EndDate)
MsgBox Years
End Sub

Upvotes: 3

Related Questions