Custom function that is more accurate than worksheet function DateDif for calculating work experi.

%3CLINGO-SUB%20id%3D%22lingo-sub-1512999%22%20slang%3D%22en-US%22%3ECustom%20function%20that%20is%20more%20accurate%20than%20worksheet%20function%20DateDif%20for%20calculating%20work%20experi.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512999%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERecently%20I%20needed%20to%20calculate%20work%20experience%20for%20some%20workers%20I%20tried%20to%20use%20DateDif%20function%20but%20I%20noticed%20it%20isn't%20100%25%20accurate.%20For%20example%20If%20I%20have%20start%20date%20as%20Jan%201st%202019%20and%20end%20date%20as%20Dec%2031st%202019%20I%20get%20result%3A%200%20y%2C%2011%20m%20and%2030%20days.%20When%20I%20add%201%20day%20I%20still%20get%200%20y%2C%2011%20m%20and%2031%20days%2C%20but%20I%20should%20get%201%20y%2C%200%20m%20and%200%20days.%20Also%20if%20start%20date%20is%20Jan%201st%202020%20and%20final%20date%20is%20Feb%2029th%202020%20I%20get%20result%20(added%201%20day%20when%20no%20of%20day%20is%20calculated)%3A%200%20y%20%2C%201%20m%20and%2029%20days%2C%20but%20I%20should%20get%20only%202%20months.%3C%2FP%3E%3CP%3EI%20was%20looking%20all%20over%20WWW%20but%20I%20couldn't%20find%20precise%20function%2C%20so%20I%20wrote%20custom%20function%20by%20myself.%20I%20hope%20it%20will%20help%20somebody.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20work_experiance(StartDate%20As%20Double%2C%20EndDate%20As%20Double%2C%20n%20As%20String)%20As%20Integer%3C%2FP%3E%3CP%3EDim%20nYears%20As%20Integer%2C%20nMonths%20As%20Integer%2C%20nDays%20As%20Integer%3CBR%20%2F%3EDim%20x%20As%20Double%2C%20z%20As%20Double%3CBR%20%2F%3EDim%20a%20As%20Integer%2C%20b%20As%20Integer%3C%2FP%3E%3CP%3Ez%20%3D%20Application.WorksheetFunction.%20_%3CBR%20%2F%3EDays360(StartDate%2C%20EndDate%2C%20True)%3CBR%20%2F%3E'calculates%20total%20number%20of%20date%20based%20on%20360%20days%20(EU%20Method)%3CBR%20%2F%3Ex%20%3D%20z%20%2F%2030%3CBR%20%2F%3EnYears%20%3D%20Int(x%20%2F%2012)%20'number%20of%20years%3CBR%20%2F%3EnMonths%20%3D%20Int(z%20%2F%2030)%20-%20nYears%20*%2012%20'number%20of%20months%3C%2FP%3E%3CP%3EIf%20Day(EndDate)%20%26gt%3B%3D%20Day(StartDate)%20Then%3CBR%20%2F%3EnDays%20%3D%20Day(EndDate)%20-%20Day(StartDate)%20%2B%201%3CBR%20%2F%3EElse%3CBR%20%2F%3Ea%20%3D%20Day(Application.WorksheetFunction.EoMonth(EndDate%2C%20-1))%20-%20_%3CBR%20%2F%3EDay(StartDate)%20%2B%201%3CBR%20%2F%3Eb%20%3D%20EndDate%20-%20Application.WorksheetFunction.EoMonth(EndDate%2C%20-1)%3CBR%20%2F%3EnDays%20%3D%20a%20%2B%20b%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EIf%20nDays%20%3D%2031%20Then%3CBR%20%2F%3EnDays%20%3D%200%3CBR%20%2F%3EnMonths%20%3D%20nMonths%20%2B%201%3CBR%20%2F%3EIf%20nMonths%20%3D%2012%20Then%3CBR%20%2F%3EnMonths%20%3D%200%3CBR%20%2F%3EnYears%20%3D%20nYears%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EIf%20Year(EndDate)%20Mod%204%20%3D%200%20And%20Month(EndDate)%20%3D%202%20And%20Day(EndDate)%20%3D%2029%20And%20nDays%20%3D%2029%20Then%3CBR%20%2F%3EnDays%20%3D%200%3CBR%20%2F%3EnMonths%20%3D%20nMonths%20%2B%201%3CBR%20%2F%3EIf%20nMonths%20%3D%2012%20Then%3CBR%20%2F%3EnMonths%20%3D%200%3CBR%20%2F%3EnYears%20%3D%20nYears%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EIf%20Year(EndDate)%20Mod%204%20%26lt%3B%26gt%3B%200%20And%20Month(EndDate)%20%3D%202%20And%20Day(EndDate)%20%3D%2028%20And%20nDays%20%3D%2028%20Then%3CBR%20%2F%3EnDays%20%3D%200%3CBR%20%2F%3EnMonths%20%3D%20nMonths%20%2B%201%3CBR%20%2F%3EIf%20nMonths%20%3D%2012%20Then%3CBR%20%2F%3EnMonths%20%3D%200%3CBR%20%2F%3EnYears%20%3D%20nYears%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3ESelect%20Case%20n%3CBR%20%2F%3ECase%20%22y%22%3CBR%20%2F%3Ework_experiance%20%3D%20nYears%3CBR%20%2F%3ECase%20%22m%22%3CBR%20%2F%3Ework_experiance%20%3D%20nMonths%3CBR%20%2F%3ECase%20%22d%22%3CBR%20%2F%3Ework_experiance%20%3D%20nDays%3CBR%20%2F%3EEnd%20Select%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513019%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20function%20that%20is%20more%20accurate%20than%20worksheet%20function%20DateDif%20for%20calculating%20work%20exper%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713768%22%20target%3D%22_blank%22%3E%40Zarko_Tripunovic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFWIW%2C%20reading%20your%20post%20reminded%20me%20of%20my%20days%20in%20the%20early%201970s%2C%20working%20in%20HR%2C%20using%20an%20IBM%20360%20%5BI%20was%20working%20for%20Big%20Blue%20at%20the%20time%5D.....for%20accuracy%20on%20multi-year%20queries%20and%20reports%20using%20SQL%2C%20we'd%20specify%20the%20length%20of%20a%20year%20as%20365.25%20days.%20Obviously%20that%20was%20to%20account%20for%20Leap%20Years.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20just%20now%20I%20did%20the%20current%20thing%20to%20verify....I%20asked%20Google.%20Here's%20the%20screen%20grab.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1594328965105.png%22%20style%3D%22width%3A%20678px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204482i03F1B38E54302676%2Fimage-dimensions%2F678x261%3Fv%3D1.0%22%20width%3D%22678%22%20height%3D%22261%22%20title%3D%22mathetes_0-1594328965105.png%22%20alt%3D%22mathetes_0-1594328965105.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIt%20does%20make%20me%20wonder%20how%20Excel%20factors%20this%20kind%20of%20precision%20into%20their%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnybody%20know%20for%20sure%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

1 Reply
Highlighted

@Zarko_Tripunovic 

 

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.

mathetes_0-1594328965105.png

It does make me wonder how Excel factors this kind of precision into their functions.

 

Anybody know for sure?