Creating a Prorate Calculator Tool

[AndyTampa]

I've created a tool where I can put in a monthly fee and an end date for that fee to calculate how much of that fee should be charged for the days used in that month. If there is a mid-month rate change, there isn't really an easy way to get the actual rate for that month. I have to note the amount due for the first rate elsewhere and recalculate. But to use this tool, I can use the same date, but I have to remember to use the "amount not due" to see what to charge for the remainder of the month. Then I have to add them together.

I'm trying to get it to automatically calculate a total for the month based on two different rates and two different dates. To that end, I've duplicated the tool but calculating the days effective is done differently depending on if it's the start date or end date of the change. I'm looking for a formula that will display totals and messages differently based on certain criteria and I'm finding it difficult. If I could at least get the formula for the messages I want to display, I'm sure I can use it to calculate the sums. There are a few outcomes I am looking for:

IF both dates are blank, I want the message that "Totals for the month are not available".
IF the change is not in the same month, I want the same message as above.
IF the end date of the first rate is equal or greater than the start date of the second rate, then I want to produce a message that there is an overlap.

DATE 1	DATE 2	CALCULATE TOTAL	MESSAGE DISPLAYED
BLANK	BLANK	NO	Total for a single month is not available
BLANK	2/15/15	YES	Total due for FEBRUARY 2015
2/14/15	BLANK	YES	Total due for FEBRUARY 2015
2/14/15	2/15/15	YES	Total due for FEBRUARY 2015
2/15/15	2/14/15	NO	DATES OVERLAP
2/14/15	3/5/15	NO	Total for a single month is not available

<tbody>
</tbody>

I'm experimenting with using formulas to create variables in a hidden column, but that is proving just as difficult. Other problems I keep encountering is trying to use math on dates. Blank cells show up as having a date of January 0, 1900.

It would also be a bonus if the words "DATES OVERLAP" could be formatted to be bold, large, and red.

I just realized that if one date field is blank, the corresponding fee field will also be blank, so calculations will have to allow for that as well.

Does anyone have any advice to help me with this?

 

[AndyTampa]

Seriously, any help on any part of this to get me going in the right direction would be helpful.

Can I format text in a formula?
What function would be best?
Can I get 3 different results from one function?

Any help please.