Help! If statements with date

Closed

Gingerlmp - Oct 2, 2009 at 01:41 AM
Trowa - Oct 5, 2009 at 08:45 AM

Hello,
I am putting together an attendance sheet with grades. Each students get 5 points per day being in class, they they are not in class then they recieve 0 if they are tardy they get 2.5 points.

=IF((B5="A"),0,IF((B5="T"),2.5,IF((B5=""),5,"")))
this is the formula I have been using, only one issue. Class hasn't started and they all have 100 points for class. Which when telling students their grade at a glance I need to subtract the attendance the haven't really recieved yet and then get a percentage.

It is a normal grade book with the dates listed at the top of the column, I would like it to be blank until the day of the class unless there is a T or an A placed in the box they will on that date get the five points.

I don't want the TA to have to daily type in P for each student in order to get a grade. Can you help?

Thanks

1 response

Answer 1 / 1

Trowa
Oct 2, 2009 at 09:04 AM

I don't understand.

You are using an A for being absence a T for being tardy and nothing for being present, correct?

They receive points but you want to distract them?

What does the percentage stand for?

What/who is TA?
What does P stand for?

I don't know how a normal grade book looks like?

Can you give an example how your sheet looks like?

Best regards,
Trowa

Gingerlmp
Oct 2, 2009 at 10:10 PM

Here is the formula I am using =IF((M5="A"),0,IF((M5="T"),2.5,IF((M5=""),5,""))). What I want to do is include a date. I want the 5 points not to show up on the grade sheet until the day of the class. Currently with the formula I have there is a 5 present before the class has even started. Is that any clearer?
Thanks for your help.

Trowa > Gingerlmp
Oct 5, 2009 at 08:45 AM

Why don't you use a P (or other letter) for present?

Otherwise you could fill in N/A for empty spaces, so the IF function won't find empty cells.

If you want to use dates put the date of today in a cell and compare it with the date of the class using another IF function.
Let's put the date of today in A1 and the date of the class in A2.

=IF(M5="A",0,IF(M5="T",2.5,IF(M5="",IF(A2>=A1,5,""),"")))

So if M5 contains A, result is 0.
If not check if M5 contains T, if so result is 2.5.
If not check if M5 contains "", if so check if the date of the class is greater or equal to the current date.
If that is true as well result is 5, if not result will be "", if M5 doesn't contain A,T or "" result will be "".

Note that there is a function to get today's date.
Also note that using that function als include time.

Hopefully this will help you out.

Best regards,
Trowa

Similar discussions

Excel - IF function with multiple words

Using IF function with dates

Dates from an IF statement

Excel "IF" function w/ date in test cell

If Statement Excel

Similar discussions

Subscribe To Our Newsletter!