VBA Function for returning year end date-CodePudding

so I'm working on a simple VBA function. So with many financial reports, reports are not always ending on DEC 31, sometimes they end in June, or March, or whatever. The function I'm trying to work on takes the year-end date and returns the number of days between the present date and then (forward-looking, so if the year-end date was yesterday then the function would be 364 days, rather than 1), then divided by 365. I have an attempt below, but for some reason, it's always returning 1. Through some tracing, I believe it has something to do with DateDiff, but from what I've seen I think I'm using the right syntax? I'm not sure what's wrong, can someone help?

Function dateCalc(ye As Date)
    Dim today As Date
    Dim x As Integer
    
    today = Date
    
    If ye < today Then
    x = (365 - DateDiff("d", ye, today)) / 365

    ElseIf ye > today Then
    x = DateDiff("d", ye, today) / 365
    Else
    x = 0
    
    End If
    
    dateCalc = x
End Function

CodePudding user response：

Using the below derivative of your code,

Function dateCalc(ByVal YE As Date) As Double
  If YE < Today Then
    dateCalc = (365 - DateDiff("d", YE, Date)) / 365
  ElseIf YE > Today Then
    dateCalc = DateDiff("d", YE, Date) / 365
  Else
    dateCalc = 0
  End If
End Function

On the date 6/14/2022, we see the following:

?dateCalc(#6/15/2022#) produces -0.00273972602739726
?dateCalc(#6/13/2022#) produces 0.997260273972603

Note that in the YE > Today case, the DateDiff is -1. If you wanted a positive, negate it. It wasn't clear from the question if this was the desired output, but the primary problem appears to be the use of Integer as opposed to Double.