How to Use Excel Formulas to Count Full-Day and Half-Day Leave
Question details
The user needs to calculate staff absence totals by assigning specific numerical values to leave codes (such as full days and half days) using an Excel formula.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating total employee absences or leave days across a daily or monthly attendance schedule.
- Observed behavior
- Needs to convert text codes like 'FD' (Full Day), 'HD' (Half Day), and 'UL' (Unpaid Leave) into numerical values (1, 0.5, and 1 respectively) and sum them up.
Ensure your employee leave data is organized in a clear column or row format without merged cells, and that the leave codes (e.g., FD, HD) are typed consistently without trailing spaces.
Use the COUNTIF Function to Tally Leave Codes
The most straightforward method to calculate totals by counting each code separately and applying its specific value.
The COUNTIF function allows you to count the number of cells that meet a single criterion. By adding multiple COUNTIF functions together and dividing the half-day count by 2, you can calculate the exact number of absence days.
Click on the empty cell at the bottom of your daily leave column (e.g., cell D11 if Monday's attendance data is in D2:D10).
Input the formula: =COUNTIF(D2:D10,"FD")+COUNTIF(D2:D10,"HD")/2+COUNTIF(D2:D10,"UL").
Press Enter to calculate the total absence value for that day.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) across adjacent columns to instantly apply the formula to the rest of the week or month.

Use the SUMPRODUCT Function for a Single Array Calculation
A powerful alternative that assigns values and multiplies them in a single operation without requiring multiple COUNTIF statements.
Use the SUM and SWITCH Functions (Newer Excel Versions)
If you are using Excel 2019, Excel 365, or a compatible modern spreadsheet, the SWITCH function offers a clean and highly readable way to assign values to text codes.
Calculate Leave Totals Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like COUNTIF, SUMPRODUCT, and modern dynamic arrays. You can seamlessly manage your HR or attendance sheets and calculate leave days with exactly the same formulas used in Microsoft Excel.
- 1. Open your attendance sheet: Launch WPS Spreadsheet and open your employee leave tracking document.
- 2. Select the total cell: Click the cell at the bottom of a daily column or the end of an employee's row where the total should appear.
- 3. Enter the formula: Type your preferred formula, such as =COUNTIF(D2:D10,"FD")+COUNTIF(D2:D10,"HD")/2+COUNTIF(D2:D10,"UL").
- 4. Drag to fill: Press Enter, then click the small square in the corner of the cell and drag it to apply the calculation to all other columns or rows.

Frequently Asked Questions
How do I exclude 'Rest Days' (RD) from my leave count?
Because 'Rest Days' are assigned a value of 0 in this scenario, you simply don't include them in the COUNTIF or SUMPRODUCT formulas. If you are using the SWITCH formula, setting the default fallback value to 0 automatically ignores 'RD' or any blank cells.
Why is my formula returning 0 or an incorrect count?
The most common reason is trailing spaces in your cells (e.g., typing 'FD ' instead of 'FD'). You can use the TRIM function to clean your data, or use Find and Replace to remove accidental spaces. Also, ensure the ranges in your formula correctly match your data.
Can I conditionally format the cells based on the leave codes?
Yes. Select your data range, navigate to the Home tab, and click Conditional Formatting > Highlight Cells Rules > Text that Contains. You can assign different background colors to 'FD', 'HD', and 'UL' for better visual tracking.
How do I calculate the total leave for each employee over a month instead of daily totals?
Instead of placing the formula at the bottom of a daily column, place it at the end of the employee's row. Change the vertical column range (e.g., D2:D10) in your formula to the horizontal row range (e.g., B2:AE2) that represents the days of the month.




