Lookup nearest billing cycle and count days

Hi;
I'm really having difficulties to come out with one template whereby to lookup what is the nearest billing cycle and count how many days from the previous month of previous month of same billing cycle date.

> Need to return what is the nearest billing cycle date of range of dates given
> Example: If product purchased on cell A1 : 3rd March 2012, cell B1 should return nearest billing cycle 7th March 2012
> Example on no of days in the month of BC range

1.****** Purchased on 1st March 2012, the nearest BC is 7th Mar 2012, the proration calculation is based on 7th Feb to 6th Mar, therefore is no. of days is 29.
2.****** Purchased on 24th Mar, the nearest BC is 25th Mar 2012, the proration calculation is based on 25th Feb to 24th Mar, therefore the no. of days is 29
3.****** Purchased on 26th Mar, the nearest BC is 7th Apr 2012, the proration calculation is based on 7th Mar to 6th April, therefore the no. of days is 31.


Attached is the excel file how it should work. Hope that someone can help me with it.

here's another option.
doesn't use an array and your cycle end dates must be sorted descending

Xl0000068.xls

you guys are awesome...i wish i could learn excel from you all..thank you very much...looking forward to learn more from you all..