Number of days in between given two dates treating number of days in month as fixed 30 days

0
votes

I need to calculate difference between two dates with a fixed 30 days a month logic. The example is shown below

  1. Start date = 10/4/2018
  2. End date = 28/10/2018
  3. Expected number of days between = 199
    (Excel calculation of difference of days => 28/10/2018 - 10/4/2018 = 201 which is not what I need) The basis for calculation of difference between two dates is, it should consider number of days in a month as 30 days irrespective of the month. So all months in between the start & end dates with 31 days should be treated as 30 days. If there is Feb in between, it should also be taken as 30 days month.

Procedure to calculate number of days in between two given dates:

  1. Fraction of days in the starting month = 30/4/2018 - 10/4/2018 = 21 days
  2. Months in between 1/5/2018 to 30/9/2018 = 5 months = 5 x 30 = 150 days
  3. Fraction of days in the last month = 28/10/2018 - 1/10/2018 = 28 days

Total days = 21 + 150 + 28 = 199 days.

If A1 is start date cell, B1 = End date cell, please suggest how to do it in excel.

5
exceldate
What if the start or end dates fall on the 31st? ie., how many days is Jan 31 to March 31 , and how does that compare to Jan 30 to April 1 or Jan 29 to April 2? – ashleedawg
If the start date is 31st Jan, it should consider as 1 day for January month. The days in between Jan 31 and Mar 31 are => 1 day for Jan, 30 days for Feb, 30 days for Mar => 1 + 30 + 30 = 61 days – Suma
Number of days between 30/4/2018 and 10/4/2018 are 21 days. The days are inclusive. So + 1 is to be added to the difference. Thank you for quick response. – Suma
is there any chance you're excluding other days from the calculation afterwards, like weekends? I've never seen any forms of accounting that exclude 8 days a year. – ashleedawg
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A2&":"&B2)))<>31)) and I will let you figure out Feb's, Jan 31 and leap year adjustments. – user4039065

5 Answers

1
votes

I've broken the formula into the three components as in your example: screen shot

The formulas in D2:G2 are:

D2: =IF(AND(YEAR(A2)=YEAR(B2),MONTH(A2)=MONTH(B2)),MIN(DATE(YEAR(A2),MONTH(A2),30),B2),DATE(YEAR(A2),MONTH(A2),30))-MIN(DATE(YEAR(A2),MONTH(A2),30),A2)+1
E2: =MAX(((YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-1)*30,0)
F2: =MIN(IF(AND(YEAR(B2)=YEAR(A2),MONTH(B2)=MONTH(A2)),0,B2-DATE(YEAR(B2),MONTH(B2),1)+1),30)
G2: =SUM(D2:F2)

or, all in one:

=IF(AND(YEAR(A2)=YEAR(B2),MONTH(A2)=MONTH(B2)),MIN(DATE(YEAR(A2),MONTH(A2),30),B2),DATE(YEAR(A2),MONTH(A2),30))-MIN(DATE(YEAR(A2),MONTH(A2),30),A2)+1+MAX(((YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-1)*30,0)+MIN(IF(AND(YEAR(B2)=YEAR(A2),MONTH(B2)=MONTH(A2)),0,B2-DATE(YEAR(B2),MONTH(B2),1)+1),30)

I like the elegance of @Jeeped 's code, but this might be easier to follow.

0
votes

Try this,

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A2&":"&B2)))<>31))+
(DAY(A2)=31)+
 SIGN(SUMPRODUCT((MONTH(ROW(INDIRECT(A2&":"&B2)))=2)*(DAY(ROW(INDIRECT(A2&":"&B2)))=28)))*2
0
votes

It seems to me that your examples are a little "wonky", like how the 31st counts sometimes (like if it's the start/end date) but not others.

Anyway, this function ain't pretty but it does the trick:

Function No31st(startDate As Date, endDate As Date) As Long
    Dim d As Date
    For d = startDate To endDate 'iterate each day in range
        If Day(d) <= 30 Then 'count days up to the 30th
            No31st = No31st + 1
            'if it's Feb 28, add 2 more days
            If (Month(d) = 2 And Day(d) = 28) Then
                No31st = No31st + 2 ' + Feb 29 + Feb 30
                If Day(d + 1) <> 1 Then d = d + 1 'leap year
            End If
        End If
    Next d
    'since the 31st counts if it's the last day:
    If Day(startDate) = 31 Or Day(endDate) = 31 Then No31st = No31st + 1
End Function

Sample Output:

StartDate   endDate    result
10/4/2018   28/10/2018  199
31/1/2018   31/3/2018    61
28/2/2018   1/3/2018      4
28/2/2000   1/3/2000      4
31/1/2018   1/2/2018      2
0
votes

The DAYS360 formula is based on the 12 30-day month logic.

0
votes

Simple & Easy Ways:

(1)

=YEARFRAC(start_date,end_date,4)*360

(2)

=DAYS360(start_date,end_date)