Trying to create a formula for prorating

[cswilki]

I need a formula for figuring out the number of days in the month and then figuring the prorated amount.

EX:
Contract Start: 9/7/23
All contracts based on 30 days/month
Monthly amount: $2,000
Prorated amount based on # of days in effect: ?

So I need:
30 days - the date of the month = #of days in effect
(30-7=23 +1)
($2,000/30 = $66.67/daily
(24 * 66.67 = $1600.08

I would also like to add - if it's less than 15 days just the prorated amount, if it's greater than 15 days then the prorated amount plus the monthly amount
So: 9/7/23 would bring the above total and 9/23/23 would be $2,000 + the prorated amount.

Hope I made that understandable.

 

[RoryA]

if it's less than 15 days just the prorated amount, if it's greater than 15 days then the prorated amount plus the monthly amount
So: 9/7/23 would bring the above total and 9/23/23 would be $2,000 + the prorated amount.

That is the wrong way round isn't it?

I think it is:

=(IF(DAY(A1)>15,1,0)+(30-MIN(30,DAY(A1))+1)/30)*A2

 

[C Moore]

EOMONTH and DAYS are the pieces missing I think.
=EOMONTH(dateofmonth,0) gives last day of month.
=EOMONTH(dateofmonth,-1)+1 gives first day of month.
I think RoryA was right, the prorate would be =DAYS(dateofmonth,EOMONTH(dateofmonth,-1)+1)+1 would give days in effect.

 

[cswilki]

So this is my spreadsheet right now, figuring manually. How would I put these formulas in?

Prorating Calculator.xlsx
	C
7
Prorating Calculator

 

[C Moore]

So this is my spreadsheet right now, figuring manually. How would I put these formulas in?

Prorating Calculator.xlsx
	C
7
Prorating Calculator

empty

 

[Joe4]

When posting your mini-sheet, you need to select the range of cells you want to copy over/display.