exceldateexcel-formuladayofmonth

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

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?

Solution

  • 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:

    enter image description here

    =((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)