Date-Related Formula

[rbahbah]

Hello...I need some help with date-related formula:

I signed a 3-month contract with a client in the amount of $300 effectrive from January 15 to April 15. I would like a formula that returns the revenue each month as follows:

January: $50 (pro-rated since the contract started January 15)
February: $100
March: $100
April: $50 (pro-rated since the contract ended April 15).

See example:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Amount[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Period (Mths)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$300[/TD]
[TD]01/15/2018[/TD]
[TD]04/15/2018[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Any idea?

Thanks!!

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Amount[/td][td]Start Date[/td][td]End Date[/td][td]Period (Mths)[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

$300​

[/td][td]

1/15/2018​

[/td][td]

4/15/2018​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

Jan 2018​

[/td][td]

Feb 2018​

[/td][td]

Mar 2018​

[/td][td]

Apr 2018​

[/td][td]

May 2018​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

$56.67​

[/td][td]

$93.33​

[/td][td]

$103.33​

[/td][td]

$46.67​

[/td][td][/td][td][/td][td]

$300.00​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet24[/td][/tr][/table]

I managed to build a formula that distributes the amount across days, not exactly what you are looking for.

Formula in cell A5:
=((ABS(MEDIAN(A4,DATE(YEAR(A4),MONTH(A4)+1,1),$C$2)-MEDIAN(A4,DATE(YEAR(A4),MONTH(A4)+1,1),$B$2)))/($C$2-$B$2))*$A$2

Perhaps someone else can build a better formula based on my formula.