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

Answer 1

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)

Answer 2

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

Answer 3

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) 2012-2013

(2) 2013-2014

(3) 2014-2015

(4) 2015-2016

 

But WholeDays (4 * 365) is incorrect because 2015-2016 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

Answer 4

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 Double

TotalDays = DateDiff("d", StartingDate, CurrentDate)
YearsWithDecimal = TotalDays / 365

    If 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