Answered by:
DateDiff("d"..) problem
Question

(Been a while since I’ve done any programming …)
What’s wrong below with the TotalDays computation?
Dim StartDate As Date
Dim CurrentDate As Date
Dim WholeYears As Integer
Dim WholeDays As Integer
Dim TotalDays As Integer
StartDate = #7/3/2012#
CurrentDate = Date ‘ correctly returns 4/4/2016
WholeYears = DateDiff(“yyyy”, StartDate, CurrentDate) ‘ correctly returns 4
WholeDays = WholeYears * 365 ‘ correctly returns 1460
TotalDays = DateDiff(“d”, StartDate, CurrentDate) ‘ INCORRECTLY (I think) returns 1372
Thank you.
Mark
Tuesday, April 5, 2016 4:54 PM
Answers

The number of days between StartDate and CurrentDate is roughly 3 months or 90 days less than 4 whole years since StartDate is in July and CurrentDate in April. So it's about 1460  90 ~ 1370 days.
The result of your TotalDays calculation is correct!
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by Peter N Roth, Author of Ten Commandments of VBA Tuesday, April 5, 2016 6:46 PM
 Marked as answer by mdavisfps Tuesday, April 5, 2016 6:48 PM
Tuesday, April 5, 2016 5:23 PM 
DateDiff with the "d" argument counts the exact number of days between the dates. Since your start date is July 3rd, 2012 and the CurrentDate is April 5th, 2016, there are about 3 months less than 4 years between the dates. This accounts for the difference of about 90 in your results. I leave it as an exercise to you to count the days exactly...
Matthias Kläy, Kläy Computing AG
 Marked as answer by mdavisfps Tuesday, April 5, 2016 6:48 PM
Tuesday, April 5, 2016 5:23 PM
All replies

The number of days between StartDate and CurrentDate is roughly 3 months or 90 days less than 4 whole years since StartDate is in July and CurrentDate in April. So it's about 1460  90 ~ 1370 days.
The result of your TotalDays calculation is correct!
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by Peter N Roth, Author of Ten Commandments of VBA Tuesday, April 5, 2016 6:46 PM
 Marked as answer by mdavisfps Tuesday, April 5, 2016 6:48 PM
Tuesday, April 5, 2016 5:23 PM 
DateDiff with the "d" argument counts the exact number of days between the dates. Since your start date is July 3rd, 2012 and the CurrentDate is April 5th, 2016, there are about 3 months less than 4 years between the dates. This accounts for the difference of about 90 in your results. I leave it as an exercise to you to count the days exactly...
Matthias Kläy, Kläy Computing AG
 Marked as answer by mdavisfps Tuesday, April 5, 2016 6:48 PM
Tuesday, April 5, 2016 5:23 PM 
Thanks, guys. I understand what you’re saying and I see the error on my part:
The comp DateDiff(“yyyy”, StartDate, CurrentDate) gives me the integer 4:
(1) 20122013
(2) 20132014
(3) 20142015
(4) 20152016
But WholeDays (4 * 365) is incorrect because 20152016 is not a full year, given CurrentDate = 4/5/2016. As you point out, WholeDays is overinflated.
I’ll fiddle around with this some more. Doing compound annual interest calculations, using 1.07 for annual growth and looking to use a fraction of that rate for the portion that is less than a full year.
Would be nice if DateDiff(“yyyy” …) could be a Double value, such as 3.532, and then parsed into 3 and .532. J
Thanks.
Mark
Tuesday, April 5, 2016 6:47 PM 
Wasn't too difficult once I came across a post of Marshall Barton's from 2012 ...
Dim aryParts As Variant
Dim RightSide As String
Dim LeftSide As String
Dim YearsWithDecimal As DoubleTotalDays = DateDiff("d", StartingDate, CurrentDate)
YearsWithDecimal = TotalDays / 365If IsNull(YearsWithDecimal) Then Exit Sub
aryParts = Split(CStr(YearsWithDecimal) & ".", ".") 'make sure there is a dot
If aryParts(0) = "" Then
LeftSide = "0"
Else
LeftSide = aryParts(0)
End If
If aryParts(1) = "" Then
RightSide = ".00"
Else
RightSide = "." & aryParts(1)
End If
LeftSide = CInt(LeftSide)
RightSide = CDbl(RightSide)Thanks, guys, for getting me past that initial error of mine.
Mark
Tuesday, April 5, 2016 8:25 PM