Calculate days of each month in a range date in Excel Formula

Question

I have sets of date range that I need to calculate total number of days. but usually the start of the range and the end is during the middle of the month. So how do I calculate number of days of each month.

Then for these dates, I need to calculate the amount of money they receive, prorated by number of days in the month. ($300/days of the month)*days include

Example:

Date 19/02/2024 - 16/06/2024

Days I need to calculate:

(Feb : 11 days)+(Mar: 31 days)+(Apr: 30 days)+(May: 31 days)+(Jun: 16 days)

Total days: 119 days

Amount I need to calculate:

(Feb : ($300/29days)*11days)+(Mar: ($300/31days)*31days)+(Apr: ($300/30days)*30days)+(May: ($300/31days)*31days)+(Jun: ($300/30days)*16days)

Total amount paid: $1,173.80

How do I calculate this in Excel, and is it even possible to have in one cell?

Answer 1

This formula takes three parts of the date range. The start, the end, and the in between monthes. Works on older versions too.

=SUM((EOMONTH(B44,0)-B44+1)*300/(EOMONTH(B44,0)-EOMONTH(B44,-1)),
 IF((YEAR(C44)-YEAR(B44))*12+MONTH(C44)-MONTH(B44)>1,300*((YEAR(C44)-YEAR(B44))*12+MONTH(C44)-MONTH(B44)-1)),
(C44-EOMONTH(C44,-1))*300/(EOMONTH(C44,0)-EOMONTH(C44,-1)))

The IF checks if there is any full month in the given range and if so, then calculates the value for it with a 300$/month rate.

Answer 2

Notice you can factour out your expression: the amount is a common factor so you just need to multiply 300 by the number of complete months and then add the proportional months (feb and June in your example) multiplied by 300.

Knowing this a shorter formula would be:

=((DATEDIF(EOMONTH(B1,0)+1,DATE(YEAR(C1),MONTH(C1),1),"m"))*A1)+((((DAY(EOMONTH(B1,0)))-DAY(B1)+1)/(DAY(EOMONTH(B1,0))))*A1)+((DAY(C1)/DAY(EOMONTH(C1,0)))*A1)

Answer 3

If you have 365 and each date is in a separate cell named first and last, and the monthly amouunt is in a cell named amt, then:

=LET(
    fullMonths, DATEDIF(
        DATE(YEAR(first), MONTH(first), 1),
        DATE(YEAR(last), MONTH(last) - 1, 1),
        "m"
    ),
    firstFx, (DAY(EOMONTH(first, 0)) - DAY(first) + 1) /
        DAY(EOMONTH(first, 0)),
    lastFx, DAY(last) / DAY(EOMONTH(last, 0)),
    SUM(fullMonths, firstFx, lastFx) * amt
)

If your date range is in the same cell, you can incorporate the TEXTSPLIT function to parse out the pieces

If you have an earlier version of Excel, then you can use the same algorithm with this harder to understand formula:

=(
    DATEDIF(
        DATE(YEAR(first), MONTH(first), 1),
        DATE(YEAR(last), MONTH(last) - 1, 1),
        "m"
    ) +
        (DAY(EOMONTH(first, 0)) - DAY(first) + 1) /
            DAY(EOMONTH(first, 0)) +
        DAY(last) / DAY(EOMONTH(last, 0))
) * 300

Answer 4

I am not sure is this what you are asking for to get the total amount paid :

---

• Formula used in cell B2

=LET(
     _StartDate, TEXTBEFORE(A2," - "),
     _EndDate, TEXTAFTER(A2," - "),
     _FirstDay, EOMONTH(_StartDate,-1)+1,
     _Dates, TEXT(HSTACK(SEQUENCE(_EndDate-_StartDate+1,,_StartDate),
     SEQUENCE(EOMONTH(_EndDate,0)-_FirstDay,,_FirstDay)),"emmm"),
     _Uniq, UNIQUE(TOCOL(_Dates,2)),
     _Output, MAKEARRAY(ROWS(_Uniq),1, LAMBDA(r,c, LET(b, INDEX(_Uniq,r),
    INDEX(SUM(N(b=TOCOL(TAKE(_Dates,,1),2)))*(300/SUM(N(b=TAKE(_Dates,,-1)))),c)))),
     ROUND(SUM(_Output),2))

---

Using GROUPBY() if applicable then:

=LET(
     _StartDate, TEXTBEFORE(A2," - "),
     _EndDate, TEXTAFTER(A2," - "),
     _FirstDay, EOMONTH(_StartDate,-1)+1,
     _Dates, TEXT(SEQUENCE(_EndDate-_StartDate+1,,_StartDate),"emmm"),
     _ActualDates, TEXT(SEQUENCE(EOMONTH(_EndDate,0)-_FirstDay,,_FirstDay),"emmm"),
     ROUND(SUM(DROP(GROUPBY(_Dates,_Dates,ROWS,,0)*
     (300/GROUPBY(_ActualDates,_ActualDates,ROWS,,0)),,1)),2))

---

Answer 5

Use DATEDIF() function.

=DATEDIF(A1,B1,"D")+1