Using If Statement for multiple date criterias

Hello all,

I need some help in writing two formulas that is getting more difficult with each additional exception that has been asked to be added. This is an exception report that we have outside our regular packaged rates. In the past we had two different rates now they have requested for 4 different rates.

In my calculations I have to provide daily revenue totals as well as figure out total expected revenue from each passenger. Sometimes two or three people are using the system charged to main passenger therefore I also have a line to multiply the # to calculate daily totals/expected total. Also the managers can go back and forth by changing the dates to see the revenue realized therefore cannot use the today formula to calculate the daily revenue totals instead have to run the formula based on the today rate.

The current system that we have included an exception formula for only 2 situations. There are different group of passengers each day, with some passengers who use the system regularly.

For example
20 December 2012 $20
21 December 2012 $7.50
22 December 2012 $12.50
23 December 2012 $8
24 December 2012 $20
25 December 2012 $20
26 December 2012 $0

In addition prior to these dates they were given a packaged rate prior to these dates so we keep a record of each passenger for the number of days they have requested to use the system which runs off another report therefore shows a zero in this report. Now I have to build this report with four new exceptions for each month.

Attached spreadsheet has the formulas for you to review which existed for the previous report. I want to simplify my formula as well if possible. Can someone assist with this? I cannot use macros due to other limitations and have to write a formula for this. Formulas that need to be updated are under daily totals & expected total.

Thanks for the help in advance,
Adam