Forum Discussion
Solved
Trying to create attendance spreadsheet, stuck on a formula
I am trying to create a workbook to track staff attendance. I've tried a few formulas:
IF(OR(B4="Absent","1",""),IF(OR(B4="Tardy","0.33",""),IF(OR(B4="Left Early","0.33",""),IF(OR(B4="FMLA","0","")IF(OR(B4="LOA","0",""))))),
IF(OR(B5=Absent)"1",""),IF(OR(B5=Tardy)"0.33",""),IF(OR(B5=Left Early)"0.33",""))), and =IF(B6=Absent,"1",IF(B6=Tardy,"0.33",IF(B6=Left Early,"0.33",""))) but they have all failed. I will also need to add an addition to the formula that will remove or replace the occurrence if the date is greater than or equal to 1 year. (I did try to switch out the OR with AND as well as using And/or formula, still did not work)
Any help is appreciated!!
=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,"")))
=IF(B5="Absent",1,IF(OR(B5="Tardy",B5="Left Early"),0.33,""))
=IF(B6="Absent",1,IF(OR(B6="Tardy",B6="Left Early"),0.33,""))
If these formulas return the intended result it should be possible to add criteria.
=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,"")))
=IF(B5="Absent",1,IF(OR(B5="Tardy",B5="Left Early"),0.33,""))
=IF(B6="Absent",1,IF(OR(B6="Tardy",B6="Left Early"),0.33,""))
If these formulas return the intended result it should be possible to add criteria.
OliverScheurich THANK YOU!!! I used the first formula and it worked! You've saved me hours of frustration
alternatively use SWITCH or LOOKUP:
=SWITCH(B4, "Absent", 1, "Tardy", 0.33, "Left Early", 0.33, "FMLA", 0, "LOA", 0, "")
=XLOOKUP(B4, {"Absent", "Tardy", "Left Early", "FMLA", "LOA"}, {1, 0.33, 0.33, 0, 0}, "")and better yet create a table for the IN-OUT and use LOOKUP on that table
=XLOOKUP(B4, [category range], [out value range], "")
