Calculate the period between two dates to get months and days

[YasserKhalil]

Hello everyone
I am trying to calculate the period between two dates and return the result in months and days. I want to include specific months of the year not all the months. The included months are from September to March.
Here's my try but the output not correct

Code: Select all

Function MonthDayDiff(start_date As Date, end_date As Date) As String
    Dim months As Long, days As Long
    Dim start_month As Long, end_month As Long
    Dim i As Long, included_months As Variant
    
    included_months = Array(9, 10, 11, 12, 1, 2, 3)
    start_month = Month(start_date)
    end_month = Month(end_date)
    
    If start_month > end_month Then
        months = end_month + 12 - start_month
    Else
        months = end_month - start_month
    End If
    
    If IsError(Application.Match(end_month, included_months, 0)) Then
        months = months - 1
        days = Day(DateSerial(Year(end_date), end_month, 0)) - Day(end_date) + 1
    ElseIf IsError(Application.Match(start_month, included_months, 0)) Then
        months = months - 1
        days = Day(start_date)
    Else
        days = Day(end_date) - Day(start_date) + 1
    End If
    
    MonthDayDiff = months & " months, " & days & " days"
End Function

Example: let's say the two dates are 1/1/2000 & 2/10/2000
January is included. February is the same. March is the same. September is included too. The two days in October are included. so the expected output would be [4 months & 2 days]

[SpeakEasy]

Will the dates ever be more than a year apart?

[HansV]

Code: Select all

Function MonthDayDiff(start_date As Date, end_date As Date) As String
    Dim d As Date, d1 As Date
    Dim months As Long
    Dim days As Long
    d = start_date
    Do
        Select Case Month(d)
            Case 1, 2, 3, 9, 10, 11, 12
                months = months + 1
        End Select
        d1 = DateAdd("m", 1, d)
        If d1 > end_date Then Exit Do
        d = d1
    Loop
    days = end_date - d
    MonthDayDiff = months & " month(s), " & days & " day(s)"
End Function

[p45cal]

Power Query solution in the attached.
While this agrees with your 4 months and 2 days for row 2 of the sheet, and it will also cover multiple years, if you provide dates as in row 5 from 2nd Jan 2000 to 29th Oct 2000 (I deliberately chose dates which include most of the start month and end month) which, as it stands, gives a result of 3 months and 59 days, which is because the three months counted as whole are the whole months Feb, March and Sept, then 30 days from the first month January, plus 29 days from the end month October.

2023-02-28_235809.png

In the attached I've included HansV's function in column I

[SpeakEasy]

I think we need tighter requirements from the OP, since my understanding (and function) of the requirements gives slightly different results:

loungedatediff.png

[YasserKhalil]

Thanks a lot for all your replies.
@SpeakEasy Yes, the years will be more than a year.
@Hans
I got 5 months & 1 days as for the example of dates I posted. I expect 4 months & 2 days

@p54cal thanks a lot but I am not a big fan of power query. and I need a UDF as this is part of another code.

[HansV]

What should the result be if start date = 4 January 2000 and end date = 4 October 2000?
And if start date = 5 January 2000 and end date = 4 October 2000?

[YasserKhalil]

The first example 4 January 2000 and the end date = 4 October 2000
28 days in January - Feb - March - Sep - 4 days in Oct
So it should be 4 months & 2 days

The second example would be 4 months & 1 day

[HansV]

I don't understand that, so I cannot offer further help, sorry.

[SpeakEasy]

I'm, with HansV on this. Can't make any logical sense of your answer to his question, especially when compared to previous example in your original post

[YasserKhalil]

Look at the results in the power query file, the results are correct as for me.

[SpeakEasy]

Please explain how:

"4 January 2000 and the end date = 4 October 2000

28 days in January - Feb - March - Sep - 4 days in Oct
So it should be 4 months & 2 days"

When, in your original post 1/1/2000 & 2/10/2000 also comes out as 4 months & 2 days

I cannot currently see how both of these can be good results

[YasserKhalil]

The months included are from 1 to 3 and from 9 to 12 only.
As for the second example 1/1/2000 & 2/10/2000
January is included - Feb - March - Sept - 2 days in Oct
so the result would be 4 months & 2 days.

[HansV]

How can you reconcile that with

"The first example 4 January 2000 and the end date = 4 October 2000
28 days in January - Feb - March - Sep - 4 days in Oct
So it should be 4 months & 2 days"

[YasserKhalil]

I suppose the month equals to 30 (365/12) so the 3 months + 28 + 4 = 4 months & 2 days.

[SpeakEasy]

Yep. I could see that. But you are applying different logic for the 1/1/2000 & 2/10/2000 instance. If you were to apply the same logic, then

1/1/2000 & 2/10/2000
31 days in Jan - Feb - Mar - Sep - 2 days in Oct
So it should be 4 months and 3 days

If you cannot nail down and specify your requirements, we can't help!

[YasserKhalil]

I am confused in fact when dealing with dates in general. Simply I need the difference period between the two dates and return the result in days and months (including the specific months I explained). I am not sure how to do that exactly and what could the logic should be.
Please guide me to the correct approach.

[HansV]

From your previous description: you want calendar months to be counted regardless of whether they have 28, 29, 30 or 31 days, but for the days calculation you want to count each month as 30 days. that will lead to weird results.

Code: Select all

Function MonthDayDiff(start_date As Date, end_date As Date) As String
    Dim d As Date, d1 As Date
    Dim months As Long
    Dim days As Long
    If start_date - Day(start_date) = end_date - Day(end_date) Then
        If Day(start_date) = 1 And end_date = DateSerial(Year(end_date), Month(end_date) + 1, 0) Then
            months = 1
        Else
            days = Day(end_date) - Day(start_date) + 1
        End If
    Else
        d = start_date
        Select Case Month(d)
            Case 1, 2, 3, 9, 10, 11, 12
                If Day(d) = 1 Then
                    months = 1
                Else
                    days = DateSerial(Year(d), Month(d) + 1, 1) - d
                End If
        End Select
        Do
            d1 = DateAdd("m", 1, d)
            If d1 - Day(d1) = end_date - Day(end_date) Then Exit Do
            d = d1
            Select Case Month(d)
                Case 1, 2, 3, 9, 10, 11, 12
                    months = months + 1
            End Select
        Loop
        Select Case Month(end_date)
            Case 1, 2, 3, 9, 10, 11, 12
                If end_date = DateSerial(Year(end_date), Month(end_date) + 1, 0) Then
                    months = months + 1
                Else
                    days = days + Day(end_date)
                    Do While days >= 30
                        months = months + 1
                        days = days - 30
                    Loop
                End If
        End Select
    End If
    MonthDayDiff = months & " month(s), " & days & " day(s)"
End Function

From 30 January 2000 to 30 March 2000:
January: 2 days, February: 1 whole month, March: 30 days. 30+2 = 32 days = 1 month and 2 days. Total: 2 months and 2 days.

From 30 January 2000 to 31 March 2000:
January: 2 days, February and March: 2 whole months. Total: 2 months and 2 days.

[YasserKhalil]

Thank you very much, my tutor. That's perfect.
How can I get the result in two cells by the same UDF, One cell for the months and the other for the days?

[HansV]

New function:

Code: Select all

Function MonthDayDiff(start_date As Date, end_date As Date)
    Dim d As Date, d1 As Date
    Dim months As Long
    Dim days As Long
    If start_date - Day(start_date) = end_date - Day(end_date) Then
        If Day(start_date) = 1 And end_date = DateSerial(Year(end_date), Month(end_date) + 1, 0) Then
            months = 1
        Else
            days = Day(end_date) - Day(start_date) + 1
        End If
    Else
        d = start_date
        Select Case Month(d)
            Case 1, 2, 3, 9, 10, 11, 12
                If Day(d) = 1 Then
                    months = 1
                Else
                    days = DateSerial(Year(d), Month(d) + 1, 1) - d
                End If
        End Select
        Do
            d1 = DateAdd("m", 1, d)
            If d1 - Day(d1) = end_date - Day(end_date) Then Exit Do
            d = d1
            Select Case Month(d)
                Case 1, 2, 3, 9, 10, 11, 12
                    months = months + 1
            End Select
        Loop
        Select Case Month(end_date)
            Case 1, 2, 3, 9, 10, 11, 12
                If end_date = DateSerial(Year(end_date), Month(end_date) + 1, 0) Then
                    months = months + 1
                Else
                    days = days + Day(end_date)
                    Do While days >= 30
                        months = months + 1
                        days = days - 30
                    Loop
                End If
        End Select
    End If
    MonthDayDiff = Array(months, days)
End Function

To use the function, select two cells next to each other in the same row, enter the formula as previously, but confirm it by pressing Ctrl+Shift+Enter to turn it into an array formula.