CODEWITHSUNDEEP
excelvbadatediff
BeckyW
BeckyW

Reputation: 47

My Excel VBA DateDiff calculation is truncating answer. How do I get more precise answer?

I am trying to calculate a date difference in weeks. I would actually like an answer to the nearest 10th decimal place (0.0). The calculation is truncating or rounding my result. How do I get a more precise answer? Code below

Option Explicit


Sub DateTesting()

Dim Date1 As Date

Dim Date2 As Date

Dim DaysDiffer As Long

Dim WeeksDiffer As Long



Date1 = "1/10/2021"

Date2 = "7/14/2021"


DaysDiffer = DateDiff("d", Date1, Date2)

MsgBox DaysDiffer               

'185 is the result


WeeksDiffer = DaysDiffer / 7        

' 185/7= 26.4285


MsgBox WeeksDiffer      

' 26 is the result

'  Why am I getting a truncated integer value for the calculation of Weeks???

End Sub

Upvotes: 1

Views: 159

Answers (1)

Gustav
Gustav

Reputation: 56006

Here is how to obtain your rounded decimal weeks:

Sub DateTesting()

    Dim Date1       As Date
    Dim Date2       As Date

    Dim DaysDiffer  As Long
    Dim WeeksDiffer As Double

    Date1 = #1/10/2021#
    Date2 = #7/14/2021#

    DaysDiffer = DateDiff("d", Date1, Date2)

    MsgBox DaysDiffer               
    ' 185 is the result

    WeeksDiffer = DaysDiffer / 7        
    ' 26.4285714285714 is the result

    MsgBox Format(WeeksDiffer, "0.0")
    ' 26.4 is the rounded result

End Sub

For all sorts of date handling, see VBA.Date.

For high precision rounding, see VBA.Round.

Upvotes: 2

Related Questions