IF formula testing three dates to get one price

[Davidap]

Hello

I am trying to develop a registration form for an upcoming event. The event is not until August, but want to have preregistration happening. I will therefore have three different dates to test for and each date time frame will have a different cost associated with it. I have half of what I need figured out, but one spot has me stumped.

Basic layout.

Registration fee Before May 15 (amount will be $100.00) Before July 15 (amount will be $150.) Onsite (this will be from Aug 10-14th and amount will be $200.00). Three spots show the price and last column gives the total price.

This is what I have for my formula right now: IF(TODAY()>=DATE(2014,8,10),"$200.00",IF(TODAY()<=DATE(2014,5,15),"$100.00","$150.00"))

However, there are few things this does not do that I can not figure out what is needed to test for them.

The first is the "total price" column must start out showing $0.

Then there are two different test I am trying to do.

The first is the registration fee. The person just has to check if the non-waived fee is their registration type. I am thinking of telling them put an "X" in the column. Then using an if formula to test for the date and that dates pricing to have it show up in the "total" column.

That is one formula.

Then there are the options to purchase tickets for events that are being held. Again there are the three different dates and pricing for those dates. For this formula I have to test both for the the current date and that pricing option, but they have the option to pick a quantity of tickets. (One kicker they might throw at me is limiting the number of tickets that can be purchase, but that I might just put in writing under the ticket description.) This also must start out with the "total" column at $0.

This would be the other needed formula.

So, how can I test for the current date. Use the pricing associated for that date range and give a unique total amount, where I am test for a text item in one formula and number quantity in another formula.

Oh, and just so I know this will work with might be the majority of versions I save as a 97-2003 version.

[rcm]

try it now

[Davidap]

I played around with this a little bit.

I see why testing $C$9 the First Name column. Does help set the totals for zero. I can use that for all the ticket/events no matter how many I put on the spreadsheet.

However, there does crop up a couple of issues.

If the $D$20 has an something in it the total on that line does stay at zero, and that is what looking for. However, the ticket total does not produce a total now. It stays at zero.

I still have to see if changing the date on my computer will test for those unique pricing.

Is a great start for my issue. Thanks

[rcm]

the ticket total needs a quantity , some might not purchase this option. I could change the default to 1

[Davidap]

Yes, if someone does not want that purchase option the amount should always stay at zero.

However, when the person picks Lifetime that is the issue. (See image LT QT.jpg) The quantity does not come into play in the Total column.

I also played with the changing my computer date and it does not function properly with the different date ranges listed.

LT QT.JPG

[Davidap]

Do believe fixed the pricing change issue had with changing dates: Altered formula to this: IF($C$9<>"",IF($D$20="",IF(TODAY()>DATE(2014,8,9),H21,IF(TODAY()<DATE(2014,5,14),F21,G21)),0),0)

But now how do I get it so a Lifetimer can purchase tickets and get the correct total (quantity picked) for whatever date it happens to be on their computer?

[rcm]

1. I took the condition of being waived from the ticket total.

2. In order to test the formulas, I refer to cell B30 (you can later put =now() in it)

3. I suggest to use B30 as the cutting date if you want to input applications yourself a few days later and still give the early bird rate.
If it is going out to the members, then it is better to replace b30 with now()

[Davidap]

Thank you very much for your assistance.

The form is working so far. Just have to see what happens when members utilize it...The hard test.

Again thanks.

David

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[Davidap]

Appreciate the information.

I was not totally finished. Was looking at other options and would have bounced those off here to see which seemed a better option.

One thing I have found out using excel is that there is not just one way of doing something...some more complex and others simple and to the point...neither always the best way.

David