I'm trying to create a sheet that will calculate attendance points based on the day the point(s) were issued and todays date. One point will drop off every 60 days and the max points given in a day is 4. I have it mostly figured out except one of my reference cell values is a formula pulling from a vlookup referencing a drop-down and seems to break the points formula. I'll try to explain best I can.
Cell M1 has the formula =TODAY() to give me the current date
Cell D2 has the formula =IF(C2<>"",VLOOKUP(C2,$K$1:$L$10,2,),"") which is simply providing the number of points given for a specific infraction in a drop-down list.
Cell A2 has the date the points were issued.
Cell F2 is where I am trying to calculate the point balance based off of the number that was issued and current date using the formula below. If I put a number in D2, the formula works but with the formula in D2 it does not.
=
IF((AND($M$1-A2>60,$M$1-A2<120,D2>=1)),D2-1,
IF((AND($M$1-A2>=120,$M$1-A2<180,D2>=2)),D2-2,
IF((AND($M$1-A2>=180,$M$1-A2<240,D2>=3)),D2-3,
IF((AND($M$1-A2>=240,D2>=4)),D2-4,
D2))))
Any assistance would be greatly appreciated.
D
Bookmarks