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?