PDA

View Full Version : [SOLVED] DATE DIFF function in years



mh2020
04-10-2019, 02:47 PM
Hello,
I am trying to use the date diff function to get the number of years between two dates as a decimal.
For example, I want the datediff function to return 16.353 when I get the difference between 12/31/2018 and 8/23/2002.
My date1 date, 12/31/2018, is saved as a string and returns "12/31/2018".
My date2 date, 8/23/2002, is saved as a string also but is saved as "20020823".
I have tried various formatting changed but I constantly get overflow and type mismatch errors.

I am fairly new to VBA so any help or tips would be greatly appreciated.

Thanks! :)

大灰狼1976
04-12-2019, 01:16 AM
Hello mh2020!
Can you tell me how 16.353 was calculated?

Rob342
04-12-2019, 01:35 AM
without see your workbook (16.353)????
try this i don't use date diff function
also format your columns to use dates


Private Sub CalDatediff()
With Worksheets("Sheet1")
.Range("E2").Value = CLng(CDate(.Range("A2"))) - CLng(CDate(.Range("B2")))
End With
End Sub

snb
04-12-2019, 03:17 AM
Sub M_snb()
MsgBox Int(DateDiff("m", "8/23/2002", "12/31/2018") / 12) + (DatePart("y", "12/31/2002") - DatePart("y", "8/23/2002")) / 365
End Sub

Paul_Hossler
04-12-2019, 05:15 AM
What about Leap Years ?? :devil2:

snb
04-12-2019, 01:53 PM
What about Leap Years ?? :devil2:

Rather evident:


Sub M_snb()
MsgBox Int(DateDiff("m", "8/23/2002", "12/31/2018") / 12) + (DatePart("y", "12/31/2002") - DatePart("y", "8/23/2002")) / DatePart("y", "12/31/2008")
End Sub

mh2020
04-15-2019, 07:23 AM
Thanks for the help everyone.

16.353 was calculated as the number of years in decimals between the two dates, 16.353 years between 08/23/2002 and 12/31/2018.

I used a formula similar to the one that was posted above.
However since my code was used on multiple lines of data I just saved the dates as strings and then used those values instead.

I will mark this as solved and have my code below in case anyone else runs into a similar problem.
Thanks!




TimeElapsed = ((ValYear - IssueYear) + ((ValMonth - IssueMonth) / 12) + ((ValDay - IssueDay) / 365))