How do insert more codes in this formula?

[Kulraj Singh]

This is the formula-
=IF(OR(TRIM(C2)="NCNS",TRIM(C2)="ABS",AND(OR(TRIM(C2)="TAR",TRIM(C2)="EO"),D2>240)),1,0.5)

I dont want none of the codes that are in there e.g NCNS, ABS, TAR EO but This is what I want to add or this is my requirement:
AB which is absent and would have value of 1
Si which is Sick and would have value of 1
Li which is late in would be value of 1/3 unless it goes over 240 min then it will be 1
Le which is left early would have value of 1/3 unless it goes over 240 min then it will be 1
Mip and Mop would have value of 1/3

Any help will be greatly appreciated. Also attaching a file where this will be used if that makes it more clear for you.

Thanks,

 

[Eloise T]

I don't see the formula:
=IF(OR(TRIM(C2)="NCNS",TRIM(C2)="ABS",AND(OR(TRIM(C2)="TAR",TRIM(C2)="EO"),D2>240)),1,0.5)
in your spreadsheet.

Can you clarify what you need?

Also, what is Mip and Mop?

 

[Kulraj Singh]

Hi there, the formula is on Occurrence page and shows up in Occurrence Value F2. Mip means missing in punch and mop is missing out punch. Thank you for your help.

 

[Kulraj Singh]

Let me clarify-
We are looking to track employee Attendance and Occurrences which are as stated previously would have a point assigned depending on what it is such as Ab or Li or LE.
I found the worksheet here under different thread but it is exactly what we need but with just different codes. I am not sure how to add them.

 

[NARAYANK991]

Hi ,

Use the following formula :

=IF(OR(TRIM(C2)="AB",TRIM(C2)="SI",AND(OR(TRIM(C2)="LI",TRIM(C2)="LE"),D2>240)),1,IF(OR(TRIM(C2)="LI",TRIM(C2)="LE",TRIM(C2)="MIP",TRIM(C2)="MOP"),1/3))

Narayan

 

[Kulraj Singh]

Thank you very much )), That worked, makes life much easier. Great!