Assignment overDUE PLEASE PLEASE HELP!!

Hey all,
I have an assignment due about an hour ago, ive been wokring on it for quite sometime and i just cant seem to figure it out... The Microsoft help function was my best freind untill it stared putting out errors....
This is probably really simple stuff to some of you guys, so please take a look and help me out cuz i need to submit this by midnight

Part 1:
This spreadsheet will list a number of television shows and determine whether they are currently playing or about to play.

The Shows
You're going to create a list of television shows (real or imaginary) in your spreadsheet.
Your list should have the same headings as shown below, in bold, and surrounded in a thick border (more headings are going to be added).
Below the headings, list at least ten television shows, along with the TV channel, the weekday (1 for Sunday, 2 for Monday, 3 for Tuesday... etc), the start time, and the end time.
You DON'T have to use real television shows if you don't want to.
You can make them up.
You DON'T have to use the CSI: Miami example shown in the image.
You can use your own television shows.
Use a different time-slot for each entry, so that no two shows overlap.
To simplify the formulas that you will need to create, avoid using television shows that start before midnight and end after midnight.
The start and end times should actually be time values in the spreadsheet (i.e. NOT just text you've typed that looks like a time value - Quattro Pro or Excel should "know" the values are times), and should use the display format shown below (hours and minutes separated by colon, followed by "AM" or "PM"). Both Quattro Pro and Excel will allow you to display time values in that format.
The Show column data and heading should be left-justified.
The Channel and Weekday column data and headings should be centered.
The Start and End column data and headings should be right-justified.
Make sure your list and headings start at least five rows down from the top of the sheet - you will need a bit of space above the list to add things later on.



Your list should have these headings and this format.


Duration
Add a new column to the right of End, with the heading: Duration.
The column data and heading should be right-justified.
Use a formula in that column to calculate the duration of your television shows
(note: a simple subtraction formula will do it - remember: time values are just numbers).
The duration time value should be displayed in the same time value format as the start and end data, but without "AM" or "PM", since those suffixes don't apply to duration times. Both Quattro Pro and Excel allow you to display time values in that format.
Example:

The CSI: Miami show, above, has a duration of one hour (8pm to 9pm), which would be displayed as "1:00".

User Prompts
Above your list of television shows (in those few rows of space you left blank at the top of the sheet), have a message to the user which prompts "Please enter a date:" followed by a cell in which the user can enter a date. The date cell should use the date format shown in the images below. The user can enter a date by typing the date in the proper format. After you've set the cell to display with the proper format, try entering a few dates yourself so you can see how it works. If you enter the date properly, the cell will stay right-justified and Quattro Pro or Excel will "know" that what you've entered is a date.
Below the prompt for a date, there should be another message prompting "Please enter a time:" followed by a cell in which the user can enter a time (formatted as a time value as shown below, with the "AM" or "PM" suffix). Try entering a few time values on your own as well, to make sure you understand how to type the time in the right format (for example: if you forget the space before the "AM" or "PM" it may not understand what you've typed). You'll need to be able to do that in order to test your spreadsheet before you submit it.
Have the date and time formatted like this...



These are the formats for Quattro Pro users (notice the 2-digit day of the month).



These are the formats for Excel users (notice the single-digit day of the month).


Bold the prompts, as shown above, and surround the four cells with a thick border as shown above.
The date and time that the user enters in these cells will be used in the formulas described below. The TAs will test your spreadsheet by trying different dates a times and seeing how they cause different outcomes from the formulas described below.
Playing
Add a new column to the right of Duration, with the heading: Playing.
The column data and heading should be centered.
Create a formula for the Playing column, which displays "YES" if the show is playing at the date and time entered by the user, and "NO" otherwise.

For example:

See the "CSI: Miami" example shown in the first image of Part 1.
It plays from 8pm to 9pm on Thursdays (Thursday is weekday 5).
If the user were to enter the date "March 30, 2006" and enter the time "8:47 PM" then the Playing cell for the "CSI: Miami" row should read "YES" because that day is a Thursday and that time falls between the show's start and end times.
If the user were to then change the time to "9:03 PM" then the Playing entry would automatically change to "NO". You need to come up with a formula that will behave this way.

Hints:

For this formula you will need to check whether the weekday for the user's date matches the weekday for the show, and then determine if the user's time is between the start and end times for the show. The only functions you should need are IF and WEEKDAY, (Excel users may need AND() and OR() as well, which aren't technically functions in Quattro Pro) but you may use any functions you wish - try to keep your formula as simple as you can. Don't forget that you can compare time values with < (less than), > (greater than), <= (less than or equal), >= (greater than or equal), and = (equal) inside IF conditions.

Soon
Add a new column to the right of Playing, with the heading: Soon.
The column data and heading should be centered.
Create a formula for the Soon column, which displays "YES" if the show isn't playing but it will start within 30 minutes (within 30 minutes of the date and time entered by the user, of course).

For example:

In the case of the "CSI: Miami" show that start at 8pm, if the user were to enter a date which is a Thurday, and the time "7:38 PM" then the Soon entry should display "YES" because the show would be starting within 30 minutes of that time - otherwise it would display "NO".

Hints:

time values are really just numbers which the spreadsheet program is displaying in a fancy way to look like a time.
If you add 1 to such a number, it's like adding 24 hours (1 day).
If you add (1 / 24), it's like adding 1 hour (one 24th of 1 day).
If you add (1 / 24 / 60), it's like adding 1 minute (one 60th of 1 hour).
If you add (1 / 24 / 60 / 60), it's like adding 1 second (one 60th of 1 minute).
And so on.
You can use this knowledge to help you create a formula that determines if the show is starting with 30 minutes of a given time. This formula will only be slightly more complicated than the Playing formula. The two formulas will be very similar.

Attention
Add a new column to the right of Soon, with the heading: Attention.
The column data and heading should be left-justified.
Create a formula for the Attention column, which works as follows...
If the show is about to start (i.e. the Soon column shows "YES"), display the sentence "xxxx is starting soon on channel yyyy" but with xxxx replaced by the name of the show, and yyyy replaced by the channel number.
If the show is playing (i.e. the Playing column shows "YES"), display the sentence "xxxx is now playing on channel yyyy" but with xxxx and yyyy replaced, as above.
If the show is neither about to start nor currently playing, the formula shouldn't display anything at all (i.e. just display the empty string: "").

Important:

Don't put show names, channels, ..etc directly into the formulas - just refer to the show names, channels, ..etc in with cell references. In other words, you should create a formula for your first television show, and just copy & paste that formula down the list for the other shows, without needing to make any changes (same for the Duration, Playing, and Soon formulas too, of course!).

Hint:

The CONCATENATE function can be used to construct those sentences from their individual pieces. Also, you have already created two columns that indicate for you whether or not a show is about to start or is currently playing - it is perfectly acceptable for the Attention formula to make use of those columns' results. In fact, your Attention formula will be simpler if you do.

CONCATENATE example:

If A1 contains "hi", B1 contains " there ", and C1 contains "dude", then CONCATENATE(A1,B1,C1) will produce "hi there dude". CONCATENATE joins its parameters together into a single string/sentence.

Some More Formatting
Once finished, make sure the list headings are all in bold, with a thick border, and justified as per the instructions, above.
Surround the entire list of shows with a single thick-bordered rectangle as well.
Colour the cell backgrounds for the column headings. Use any colour other than white (don't use black either, since it would make the headings impossible to read).
Colour the cell backgrounds for the date and time prompts as well.
Make sure every column in your spreadsheet is wide enough to hold its longest entry.


Save your spreadsheet file.


THANKS!

I am stuck at the playing formula ... im not sure wether to use AND or OR of IF.

without creating your entire spreadsheet and posting it here, it's difficult to specify exactly which cell belongs in each portion of the formula, plus if this is an assignment, the purpose is probably to learn!
It seems that the following formula, populated with the correct cell ref should work.
If you understand the basic IF Function, it's easy to see how nesting an IF function within another IF function allows you to narrow down the results by additional tests

=IF(show weekday cell = entered weekday cell, IF (show start time >= entered time, IF (show end time < entered time,"YES","NO"),"NO"),"NO")

these are nested IF functions
what this formula says is:

IF the weekdays match then go one with the second test and if not say "NO" (this is the last NO in the formula)

the second test says IF the show start is > or = the entered time then go one with the third test and if not say "NO" (this is the second to the last NO in the formula)

the third test says IF the show end time is < the entered time then you have a show that is playing so say "YES", and if not say "NO" (this is the first NO in the formula)

Hope this helps

it might be easier to do each IF statement separately, (in the WRONG cell!)
then in the CORRECT cell copy by highlighting and pasting all the other cells
(without the = sign)
then when finished pasting add the = sign to the beginning of the formula.
This allows you to build the compound formula without Excel telling you the formula is wrong when you're only half way finished writing it.
Also, the highlighting and pasting without the = sign stops the cell references from changing as they normally do when copying from one location to another.