If statement to select data - nested statement - assistance

Dear Members,

Thank you for your help in advance. I have attached a file with comments to explain what I am after.

Basically I have a list of students with a date access to a website

If they have accessed within 7 days from today - they should have a value of 10 + the value of Column D
If they have accessed within the last 14 days it should be a different value 40 + the value of Column D - it is a bigger score as they did not accessed within the last seven days
finally

If they have accessed within 28 days from today - they should have a value of 75 + the value of Column D

I am sure it is pretty simple but all I could do was to start with a simple if statement and got lost in the logic

Thanks
EIM tool.xlsx

Hello,

with only three different values, you could do a nested IF statement. With more values, you may want to use a lookup table approach. In any case, it would be easiest if you insert a column where the number of days is calculated. Then you can use this in many formulas without doing the calculation over and over again.

I'm not sure if the three columns with 0 and 1 are just for illustration. You say you want to return a Yes, but the formula has a "1". Just change that to a "Yes".

Insert a column between G and H and enter this formula

=TODAY()-G2

This gives you the number of days since the last access. This formula is now in column H.

In the (now) column L you can use

=IF(H2<=7,10,IF(H2<=14,40,75))+D2

cheers, teylyn

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Last 7 days in cell I2: =IF(H2<=7,"Yes","")
Last 14 days in cell J2: =IF(AND(H2>7,H2<=14),"Yes","")
Last 28 or more in cell K2 =IF(H2>14,"Yes","")

copy down.