# 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

• 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)

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

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

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 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

Tuesday, April 5, 2016 8:25 PM