I originally asked this on the Microsoft Community Forums this morning, but it never got looked at past me checking on it.
I have a spreadsheet to track attendance instances for my team at work. Our attendance policy uses a points system to determine disciplinary actions and allow some flexibility for unforeseen absences. The policy indicates that if you do not have two occurrences within 30 days of each other, the first rolls off after 60 days, but if you do have a second occurrence within 30 days, it stays in place for 6 months. I need a formula that will calculate this properly.
My current sheet and formulas looks like this:
A B C D E F G H 1 Date Absence Code Points Accrued Total Points Roll-Off Date Days to Rolloff Action Required Notes 2 =IF(B2="","",VLOOKUP(B2,Guidelines!$B$4:$C$10,2,FALSE)) =IF(C2="","",C2) =IF(OR(AND(A2>0,C3=""),AND(A2>0,C3=0,C4=0),AND(A2>0,C4="")),A2+60,IF(OR(AND(C3=0,C4>0),AND(A2>0,C3>0)),A2+182,"")) =IF(E2="","",IF(AND(A2>0,C2>0),IF(E2-TODAY()>0,E2-TODAY(),0),"")) =IF(D2="","",IF(D2=0,"N/A",VLOOKUP(D2,Guidelines!$E$4:$F$23,2,FALSE))) 3 =IF(B3="","",VLOOKUP(B3,Guidelines!$B$4:$C$10,2,FALSE)) =IF(C3="",D2,D2+C3) =IF(OR(AND(A3>0,C4=""),AND(A3>0,C4=0,C5=0),AND(A3>0,C5="")),A3+60,IF(OR(AND(C4=0,C5>0),AND(A3>0,C4>0)),A3+182,"")) =IF(E3="","",IF(AND(A3>0,C3>0),IF(E3-TODAY()>0,E3-TODAY(),0),"")) =IF(D3="","",IF(D3=0,"N/A",VLOOKUP(D3,Guidelines!$E$4:$F$23,2,FALSE)))
The Absence Code is a Data Validation list. The formulas in Column E are the ones that I am looking to change. They need to be based on the dates in Column A, and only change if the Points Accrued is greater than 0 in Column C of the same Row identified in Column A.
Bookmarks