For a row of attendance codes in B2:F2, where P means present and H means half day, use =COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"H")*0.5. It returns attendance in day-units: for example, two full days and one half day equal 2.5 days.
How the half-day formula works
The formula counts cells marked P, then adds half a day for every cell marked H. COUNTIF counts cells that match a specified value; multiplying the H count by 0.5 converts half-day marks into half-day units. A marks cell is counted as zero by this formula, and blank cells are ignored. You can replace P and H with your own codes, but the text inside the formula must match the entries in your worksheet.
For example, if B2:F2 contains P, H, P, A, and a blank, the calculation is two P marks plus one H mark multiplied by 0.5, which gives 2.5. The A for absent and the blank contribute nothing. This total is useful for a report that expresses attendance as days attended rather than simply counting the number of dates on which someone appeared.
Excel does not have a special built-in half-day attendance function. The formula works by assigning a numeric value to each attendance category. That means the same idea can be adapted for other labels, such as Present and Half, or for numeric entries such as 1 and 0.5.
Set up the worksheet clearly
A simple monthly layout puts one employee on each row and one scheduled workday in each date column. Put the dates in B1:F1, for example, and the corresponding attendance marks for the first employee in B2:F2. Put the employee’s total in G2. Use only scheduled working days in the date columns, or be explicit about how weekends, holidays, and non-working days are represented.
Write down the meaning of every code near the table or on a separate legend sheet. A common scheme is P for a full day, H for a half day, A for an absence, and L for approved leave. Decide in advance whether approved leave counts toward the attendance measure. The basic count formula treats L just like A: it adds no attendance. That may be right for days attended but not for a policy-based attendance percentage where approved leave is excluded.
- Keep the same attendance codes throughout the range. Mixing “H,” “Half,” and “1/2” makes formulas harder to maintain.
- Use one cell per person per scheduled date so there is a clear record behind each total.
- Keep names, dates, and attendance marks in separate cells rather than typing several values into one cell.
- Make the reporting period clear, such as a month or a payroll period, and avoid including unscheduled dates in the denominator of a percentage.
In Microsoft 365 for Windows, the basic formula can be entered in G2 and copied down for other employees. Select G2, then drag the fill handle at the lower-right corner of the cell down the column, or copy and paste it into the other total cells. Excel adjusts the row references as the formula is copied.
Choose between text codes and numeric values
Text codes are easier to read in a register: a manager can see P, H, or A without interpreting decimals. Their trade-off is that formula criteria must match the chosen codes. If the worksheet is mainly for calculations, numeric values can be simpler: enter 1 for a full day, 0.5 for a half day, and 0 for no attendance. Then a sum directly gives attended day-units.
With numeric attendance values in B2:F2, where a full day is 1, a half day is 0.5, and absence is 0, use =SUM(B2:F2). If the cells contain 1, 0.5, 1, 0, and 1, the result is 3.5 days. This design is concise, but users may accidentally type an unsupported value such as 0.25 or enter a number as text. Add a legend and consider restricting entries if several people update the workbook.
A third option is to record hours instead of day fractions. For instance, a half day might be four hours in a workplace whose standard day is eight hours. Hours provide more detail when shifts differ, but the definition of a “half day” then needs to be agreed and documented. Do not assume four hours is universally a half day; schedules and organizational rules vary. Choose the representation that matches the attendance policy and the report you need.
Calculate an attendance percentage
A day total and an attendance percentage answer different questions. A total such as 3.5 means 3.5 attended day-units. A percentage compares attended day-units with the scheduled days in the period. For a straightforward percentage, use a date header for every scheduled day and ensure every day has a completed mark, including absences. Then the denominator is the number of scheduled date columns.
With scheduled dates in B1:F1 and completed P, H, or A marks in B2:F2, use =(COUNTIF(B2:F2,"P")+COUNTIF(B2:F2,"H")*0.5)/COUNTA(B1:F1). If the marks are P, H, P, A, P and all five date headers contain dates, the numerator is 3.5 and the denominator is 5, so the result is 70%. Apply a percentage format to the result cell using Home > Number, then select Percentage; adjust the decimal places if needed.
This calculation assumes each date column is one scheduled day and every date in the header belongs in the denominator. Do not include weekends or holidays in that range unless they are genuinely scheduled workdays. Also, do not treat a blank as an absence without deciding that this is your policy: a blank may mean the record has not been entered yet. If the month is still in progress, a percentage using all dates in the month can make current attendance look lower than it is. Calculate only through completed scheduled days, or clearly label the result as a period-to-date figure.
Some workplaces exclude approved leave from the denominator, while others report it separately. The formula above does not make that policy decision; it counts the listed scheduled days. Define whether leave is attended, excluded, or shown as a separate category before publishing a percentage, and keep the formula consistent with that definition.
Extend the calculation to a team or a month
For a small register with one row per employee, place each person’s day total in the same total column and fill the formula down. If the date columns stay fixed, Excel adjusts the attendance row automatically. For example, a formula entered in G2 and copied to G3 will refer to row 3’s attendance marks. Review the first few copied formulas to confirm that the ranges line up with the correct employee.
With employee totals in G2:G20, use =SUM(G2:G20) in G21 to calculate the team’s combined attended day-units. This adds the individual totals; it is not a team attendance percentage. A team percentage needs an appropriate combined denominator, such as the total scheduled employee-days, and the chosen treatment of leave must be consistent. Simply averaging individual percentages can produce a different result from dividing combined attendance by combined scheduled days, especially when employees have different numbers of scheduled days.
For a year-long workbook, you might keep each month on a separate sheet or use a single table with one record per employee per date. Separate monthly sheets are familiar but can make cross-month reporting cumbersome. A single table is easier to filter and summarize when it is consistently structured, with columns for employee, date, and attendance code. Either approach can work; the important point is to avoid changing the meaning of codes or the denominator from one period to another.
Reduce entry mistakes with validation
Data validation can help prevent typos in a code-based register. Select the attendance cells, then use Data > Data Validation. In the dialog, set Allow to List and enter the approved options in Source, such as P,H,A,L. Users can then choose from the permitted entries instead of typing a new variation. Check the options in the dialog before applying them, and make sure the list includes every code your policy uses.
Validation helps with consistency but does not determine what a code means. It also cannot guarantee that a completed attendance record is correct. Protect the code legend and explain who should enter or revise marks if several people share the workbook. A quick visual check of unusual entries is still worthwhile, particularly before totals are used in a formal report.
You can also make the register easier to scan by applying conditional formatting to highlight codes such as H or A. In Excel for Microsoft 365 on Windows, select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Choose the text to highlight and a format. Conditional formatting changes the display, not the underlying value or the attendance calculation.
Common problems and how to fix them
- The total is too low: Check that the entered half-day code matches the formula criterion exactly. Extra spaces, a different abbreviation, or a spelling variation can keep COUNTIF from recognizing a mark.
- The result is a count of days, not day-units: Confirm that the half-day count is multiplied by 0.5. Counting H marks without that multiplier treats each half day as a full day.
- The percentage looks unexpectedly small: Check whether the denominator includes weekends, holidays, future dates, or blank date headers. The denominator should reflect scheduled days included in the report.
- A blank is being interpreted incorrectly: The basic formula ignores blank marks in the numerator. For a percentage, however, the denominator may still include the date if its header is populated. Finish data entry or report incomplete periods clearly.
- A formula displays as text: Check that the cell is not formatted as Text, and that the entry begins with an equals sign. Change the format to General if appropriate, then re-enter the formula.
- Excel rejects the formula’s separators: The examples use commas between function arguments, as specified for standard English-language formula syntax. Some regional settings use semicolons instead; follow the separators Excel expects on that computer.
- The total changes after copying: Inspect the formula bar in the copied cell. Excel normally updates relative row references, but a copied formula can still point to the wrong range if the layout is irregular.
A frequent misconception is that a half day should be counted as one attendance occurrence because the person came in that day. That is valid for a different metric—days with any attendance—but it does not measure attended day-equivalents. Choose the metric first. If you need both, report both values separately rather than using one number for two meanings.
Frequently Asked Questions
How do I count a half day as 0.5 in Excel?
Use a code such as H and multiply its COUNTIF result by 0.5, then add the count of full-day codes. Alternatively, enter 0.5 directly in a numeric attendance cell and sum the row.
Does a half day count as one day or half a day?
It depends on the measure. Count it as one if you are counting dates with any attendance; count it as 0.5 if you are calculating attended day-units. State which measure a report uses.
Can I use P, H, and A as my attendance codes?
Yes. The letters are labels, not special Excel values. Use the same codes in the cells and formula, and define them in a visible legend so users know what each one means.
How do I calculate half-day attendance as a percentage?
Convert each full day to 1 and each half day to 0.5, add those units, and divide by the scheduled days included in the period. Format the result as a percentage, and decide how leave and incomplete records affect the denominator.
Can I use this formula in Excel for Mac or Excel for the web?
Yes. COUNTIF, SUM, and the formulas shown are available in current Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon placement and dialog appearance can vary slightly by platform, but the calculation does not change.
Related
- Free Excel Comparison Template: Build One That Fits
- Free Excel Meal Plan Template: Build a Weekly Planner
- Fixing Broken Links in Excel: Find and Repair Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel VLOOKUP Between Two Dates: Find the Matching RangeTo use VLOOKUP for a date that falls between a start date and an end date, sort the…
- Clear Excel Sort: Remove Sorting or Restore Original OrderTo clear a sort in Excel for Microsoft 365 on Windows, select a cell in the sorted data…
- Excel Sum by Category: Formulas and PivotTablesTo sum values by category in Excel, use SUMIF: with category names in A2:A100, amounts in B2:B100, and…
- Excel Import CSV: Formula or the Right Import MethodExcel does not have a built-in worksheet formula that imports a CSV file from your computer. In Microsoft…
- Excel Power Query Table Filter: How to Apply and RefreshTo filter an Excel table with Power Query in Microsoft 365 for Windows, select a cell in the…
- Excel Formula: Days Between Two Dates and Today’s DateTo calculate days between two dates in Excel, subtract the earlier date from the later one: with a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.