Find the date difference

[prasaddn]

Hi All,
I need to find if the person is more than 65 years or not, on the date of service.

I have date of birth as say 06/12/1950 (mm/dd/yyyy) format. And the date of service is 01/16/2015.

When I use =DATEDIF("6/12/1950","1/16/2015","y") formula in excel sheet, it gives 64. which is correct.

When I use the same in VBA.datediff("yyyy", dob, dos), it returns 65.

Pls advise How to get this solved. As this is required in VBA macro, I do not want to use the formula method.

Below is my part of vba code. dob and dos are in string format as input from other process.

  Dim DOB, DOS As String
  DOB = "06/12/1950"
  DOS = "01/15/2015"
  Debug.Print VBA.DateDiff("yyyy", VBA.CDate(DOB), VBA.CDate(DOS))

Regards,
Prasad DN
PS: I tried web search, all pointers leads to datediff function only

 

[Hui]

How about this?

1. Setup a named Formula
MyDate: =DATEDIF(First date, Second date, "y")
eg: =DATEDIF(A1, A2, "y")

2. In VBA simply use [MyDate]

 

[prasaddn]

Hi Hui,

Thank you for your response, but first date and second date are variables in my vba. so cant include them in named formula.

Regards,
Prasad DN

 

[Hui]

Why not
Add two named ranges using vba
Set the values
Done

 

[NARAYANK991]

Hi Prasad ,

The following line of code :

Debug.Print VBA.DateDiff("yyyy", DOB, DOS)

where :

DOB is December 31 , 1950
DOS is January 1 , 1951

will return 1.

Similarly , where :

DOB is January 1 , 1950
DOS is December 31 , 1950

will return 0.

It is clear that the VBA.DateDiff function just looks at the year difference.

Use the code :

Debug.Print Int(VBA.DateDiff("m", (DOB), (DOS)) / 12)

Narayan

 

[prasaddn]

Hi Narayan,
I was trying with your method of taking months and dividing, but issue comes with days and dividing by 365 would mean leap year and other issues. so your solution is not working for me.

take example of two dates with just 1 day before dob scenario:

Sub test()
  Dim DOB, DOS As String
  DOB = "02/05/1950"
  DOS = "02/04/2015"
 
  ActiveWorkbook.Names.Add Name:="AgeOnDOS", RefersToR1C1:= _
  "=DATEDIF(" & VBA.CDate(DOB) & "," & VBA.CDate(DOS) & ",""Y"")"
 
  'below line output is 0 - incorrect
  Debug.Print [ageondos]
 
  'below line output is 65 - incorrect
  Debug.Print VBA.DateDiff("yyyy", VBA.CDate(DOB), VBA.CDate(DOS))
 
  'below line output is 65 - incorrect
  Debug.Print Int(VBA.DateDiff("m", (DOB), (DOS)) / 12)
 
End Sub

Hi Hui,
I tried your suggestion of adding named range but failing with syntax somewhere. I tried to see in defined names, and see that the named range is reading the data as string and not as date, even after using CDate function.

Pls suggest.

Regards,
Prasad DN

 

[prasaddn]

Hi Hui,

Got it!!

here is the changed code:

  ActiveWorkbook.Names.Add Name:="AgeOnDOS", RefersToR1C1:= _
  "=DATEDIF(""" & VBA.CDate(DOB) & """,""" & VBA.CDate(DOS) & """,""Y"")"

Regards,
Prasad DN

 

[NARAYANK991]

Hi Prasad ,

I ran your posted code , and the following line of code :

Debug.Print Int(VBA.DateDiff("m", (DOB), (DOS)) / 12)

with DOB set to 02/05/1950 , and DOS set to 02/04/2015 , gives me 64 , not 65 as in your comment in the macro.

Are you saying 64 is incorrect ?

Narayan

 

[prasaddn]

Hi Narayan,

for the above said dates, 64 is correct. I am not sure how it gives 64 at your end, when I checked it was giving me 65.

Anyways, I am currently good with named range option.

Regards,
Prasad DN