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

Help! If statements with date
How many if statements can you nest - Guide
If then statements in excel - Guide
Onlyfans statements - Guide
Vba case statement with string - Guide
Remove false from if statement ✓ - Excel Forum

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

Removing " false" value in "IF"

using multiple if functions with data validation

Multiple If in Data Validation

using a data validation AND an IF formula in the same cell

Removing "FALSE" from an IF statement with a VLOOKUP

Similar discussions

Subscribe To Our Newsletter!