Number of days in a year that fell between 2 dates

[Abraxus]

I've been pulling my hair out on this one and just can't seem to wrap my head around the necessary logic.

I have 2 dates and a year. 1/1/23 and 4/4/27 and 2024, for example.

I need to know how many days in 2024 fall between those 2 dates.

In this example it's 366, but if they year were 2027, it would only be 95.

Any pointers on how to make this function?

Code: Select all

Function GetNumberOfDays(dteOne as Date, dteTwo as Date, strYear as String) as Integer
   dim intToReturn as Integer
   NO IDEA WHAT GOES HERE...
GetNumberOfDays = intToReturn
End Function

[HansV]

Shouldn't it be 94 days in 2027?
31 in January + 28 in February + 31 in March + 4 in April = 94.

Code: Select all

Function GetNumberOfDays(ByVal dteOne As Date, ByVal dteTwo As Date, ByVal lngYear As Long) As Long
    Dim lngToReturn As Long
    If dteOne < DateSerial(lngYear, 1, 1) Then
       dteOne = DateSerial(lngYear, 1, 1)
    End If
    If dteTwo > DateSerial(lngYear, 12, 31) Then
        dteTwo = DateSerial(lngYear, 12, 31)
    End If
    lngToReturn = dteTwo - dteOne + 1
    If lngToReturn < 0 Then
        lngToReturn = 0
    End If
    GetNumberOfDays = lngToReturn
End Function

[Abraxus]

31 in January + 28 in February + 31 in March + 4 in April = 94.

I had written down the number of days using 2024, which is a leap year, and forgot to change it for 2027 which is not. :-)

Thank you!

[p45cal]

In the attached, in column C, a plain formula:
=MAX(0,MIN(DATE(A5+1,1,0),$B$2)-MAX($A$2,DATE(A5,1,1))+1)
in column D, HansV's function
in column E, a lambda function (DaysOverlap) based on the plain formula in column C

2023-11-02_013843.jpg

[snb]

Alternative UDF

In F5: =F_snb($A$2:$B$2;$A5)

Code: Select all

Function F_snb(sn, n)
  For j = sn(1, 1) To sn(1, 2)
    If Year(j) = n Then F_snb = F_snb + 1
  Next
End Function

[SpeakEasy]

We can shorten that a little more :

Code: Select all

Function F_snb(sn, n)
  For j = sn(1, 1) To sn(1, 2)
    F_snb = F_snb - (year(j) = n)
  Next
End Function

[snb]

No objection, your honour.