Formula based on Date

[jsalotra]

Hello,

I need help to create a formula based on date. There are amounts which are received for 12 months period and only 1/24th of the amount is earned in one month. The amounts received are based on date.

For example the amount received in Jan 2017 is 2,500 and the amount earned in Jan -2017 is 1/24th, in Feb-2017 is 3/24th, in March 5/24th and so on until Dec 2017. I am manually looking at date and then copying the formula.

Is there any way that one formula can be used for any date. I have attached the spreadsheet for an example.

Thanks,
Jai

[oeldere]

c25=if($A25>C$4,"",if($A25=C$4,$B25-($B25/$A$1),MAX(0,if($A25<=C$4,B25-($B25/$A$1),"")))) and drag across.

See the attached file.

[jsalotra]

Hi,

Thanks for the replying but I am not getting the same results as I was getting with manual formulas.
There should be amount in last month. For example, January 2017 amount should end in December 2017 and February 2017 amount should end in January 2018.

Can you please take a look again and let me know.

Thanks again,
Jai

[oeldere]

c25=if($A25>C$4,"",if($A25=C$4,$B25-($B25/$A$1/2),MAX(0,if($A25<=C$4,B25-($B25/$A$1),"")))) and drag across.

[Richard Buttrey]

One way in C6 dragged across and down

Please Login or Register  to view this content.

[dflak]

On the new tab, I think I have the formula in the light green shaded area. The rest of the stuff on the page was used for QA and to help develop the formula and can be ignored or even removed. I started with your basic formula and made the 1, 3, 5, etc. based on the Month Number and then excluded months that should not be seen.

[jsalotra]

Thanks you so much ! Much Appreciated