Create an Attendance Points Formula in WPS Spreadsheets
Question details
Create a formula that assigns attendance points based on employee status values and leaves unrecognized statuses blank.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to score attendance entries for Absence, Tardy, Left Early, FMLA, and LOA in an attendance worksheet, optionally excluding records from one year ago or earlier.
- Observed behavior
- Absent should return 1; Tardy and Left Early should return 0.33; FMLA and LOA should return 0; all other status entries should remain blank.
Confirm that the attendance status is in column B and uses consistent text such as Absent, Tardy, Left Early, FMLA, and LOA. If you use the date exclusion, make sure the corresponding date cells in column A contain real spreadsheet dates rather than text.
Use an IF formula to assign attendance points by status
Use this method when each attendance status is entered in a cell and you need a numeric point value for reporting or totals.
This nested IF formula checks the status in B4. It returns the required point value for recognized statuses and displays a blank cell for any other entry.
In WPS Spreadsheets, click the cell where you want the attendance score to appear, such as C4.
Type or paste the following formula into the formula bar or directly into C4: =IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,"")))
Press Enter, then drag the fill handle from the lower-right corner of C4 down the column to apply the formula to additional attendance records.
Exclude attendance records dated one year ago or earlier
Use this version when dates are stored in column A and older records should not receive an attendance point value.
Calculate Attendance Points with WPS Spreadsheets
WPS Spreadsheets lets you build nested IF and OR formulas to convert attendance statuses into points, then copy the formula down for an entire employee attendance list. You can also add a date condition to leave older records blank automatically.
- 1. Open the attendance worksheet: Open your attendance file in WPS Office and select WPS Spreadsheets from the workbook tabs.
- 2. Choose the results column: Click the first empty cell beside the attendance status, such as C4, where the points result should appear.
- 3. Insert the status formula: Enter =IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,""))) and press Enter.
- 4. Add the date filter if needed: For records that are at least one year old, use =IF(A4<=EDATE(TODAY(),-12),"",IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(OR(B4="FMLA",B4="LOA"),0,"")))).
- 5. Apply the formula to all rows: Drag the cell fill handle down the results column to calculate attendance points for each row.
Frequently Asked Questions
Why does the attendance formula return a blank cell?
The formula intentionally returns "" when the status in B4 is not Absent, Tardy, Left Early, FMLA, or LOA. Check the status spelling and remove extra spaces if a recognized status is returning blank.
How can I change the point value for Tardy or Left Early?
Edit the 0.33 value in the formula. For example, replace 0.33 with 0.5 in the OR(B4="Tardy",B4="Left Early") section to assign half a point.
Can I exclude records older than one year based on a different date column?
Yes. In the date-filtered formula, replace A4 with the cell that contains your record date. For example, use D4 if dates are stored in column D.
Does the formula distinguish uppercase and lowercase attendance statuses?
No. WPS Spreadsheets text comparisons in this formula are not case-sensitive, so absent and Absent are treated the same. The wording still needs to match the status name.
