Jul 09 2020 02:03 PM - edited Jul 09 2020 02:05 PM
Hi folks,
Recently I needed to calculate work experience for some workers and I tried to use DateDif function but I noticed it isn't 100% accurate. For example If I have start date as Jan 1st 2019 and end date as Dec 31st 2019 I get result: 0 y, 11 m and 30 days. When I add 1 day I still get 0 y, 11 m and 31 days, but I should get 1 y, 0 m and 0 days. Also if start date is Jan 1st 2020 and final date is Feb 29th 2020 I get result (added 1 day when no. of day is calculated): 0 y , 1 m and 29 days, but I should get only 2 months.
I was looking all over WWW but I couldn't find precise function so I wrote custom function by myself. I hope it will help somebody.
Function work_experiance(StartDate As Double, EndDate As Double, n As String) As Integer
Dim nYears As Integer, nMonths As Integer, nDays As Integer
Dim x As Double, z As Double
Dim a As Integer, b As Integer
z = Application.WorksheetFunction. _
Days360(StartDate, EndDate, True)
'calculates total number of date based on 360 days (EU Method)
x = z / 30
nYears = Int(x / 12) 'number of years
nMonths = Int(z / 30) - nYears * 12 'number of months
If Day(EndDate) >= Day(StartDate) Then
nDays = Day(EndDate) - Day(StartDate) + 1
Else
a = Day(Application.WorksheetFunction.EoMonth(EndDate, -1)) - _
Day(StartDate) + 1
b = EndDate - Application.WorksheetFunction.EoMonth(EndDate, -1)
nDays = a + b
End If
If nDays = 31 Then
nDays = 0
nMonths = nMonths + 1
If nMonths = 12 Then
nMonths = 0
nYears = nYears + 1
End If
End If
If Year(EndDate) Mod 4 = 0 And Month(EndDate) = 2 And Day(EndDate) = 29 And nDays = 29 Then
nDays = 0
nMonths = nMonths + 1
If nMonths = 12 Then
nMonths = 0
nYears = nYears + 1
End If
End If
If Year(EndDate) Mod 4 <> 0 And Month(EndDate) = 2 And Day(EndDate) = 28 And nDays = 28 Then
nDays = 0
nMonths = nMonths + 1
If nMonths = 12 Then
nMonths = 0
nYears = nYears + 1
End If
End If
Select Case n
Case "y"
work_experiance = nYears
Case "m"
work_experiance = nMonths
Case "d"
work_experiance = nDays
End Select
End Function
Jul 09 2020 02:10 PM
FWIW, reading your post reminded me of my days in the early 1970s, working in HR, using an IBM 360 [I was working for Big Blue at the time].....for accuracy on multi-year queries and reports using SQL, we'd specify the length of a year as 365.25 days. Obviously that was to account for Leap Years.
So just now I did the current thing to verify....I asked Google. Here's the screen grab.
It does make me wonder how Excel factors this kind of precision into their functions.
Anybody know for sure?