Calculate Hours From Coded Calendar Entries in Excel
Question details
Calculate total hours from calendar entries that use full-day and half-day text codes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The calendar uses codes such as W, V, SP, A, H, and 1/2V in separate worksheet ranges. Full-day entries count as 8 hours, while 1/2V counts as 4 hours.
- Observed behavior
- The user needs formulas that count each calendar code and convert the count into the correct total number of hours.
Confirm that each calendar entry is typed consistently, especially the half-day code "1/2V". Identify every calendar range that should be included in the total, such as P4:BN20 and P36:BN50.
Use COUNTIF to calculate hours for each calendar code
Use this method when you need a separate total for each code and the codes have fixed hour values.
COUNTIF counts matching text entries in each calendar range. Multiply the result by 8 for a full-day code or by 4 for the 1/2V half-day vacation code.
Select the total cell and enter =8*(COUNTIF(P4:BN20,"W")+COUNTIF(P36:BN50,"W")). Press Enter to return the total hours for W entries.
In another total cell, enter =4*(COUNTIF(P4:BN20,"1/2V")+COUNTIF(P36:BN50,"1/2V")). Press Enter to calculate hours for 1/2V entries.
Copy the W formula to other total cells and replace "W" with "V", "SP", "A", or "H". Keep the multiplier as 8 if each of those codes represents a full day.

Use a lookup table and SUMPRODUCT for scalable code totals
Use this method when you have many codes or may need to change the hours assigned to a code later.
Calculate Calendar Code Hours with WPS Spreadsheets
WPS Spreadsheets supports COUNTIF and SUMPRODUCT formulas for turning coded calendar entries into total work or leave hours. Open the workbook, enter the formula in a totals cell, and use a code-to-hours table when you need a flexible calculation setup.
- 1. Open the calendar workbook: Open your .xlsx or .xls file in WPS Office and select the worksheet containing the calendar codes.
- 2. Choose a totals cell: Click an empty cell where the total hours should appear, such as a cell below or beside the calendar.
- 3. Enter a COUNTIF formula: Type =8*(COUNTIF(P4:BN20,"W")+COUNTIF(P36:BN50,"W")) to total full-day W entries, then press Enter.
- 4. Add the half-day formula: In another totals cell, type =4*(COUNTIF(P4:BN20,"1/2V")+COUNTIF(P36:BN50,"1/2V")) and press Enter.

Frequently Asked Questions
How do I count several full-day calendar codes in one formula?
If W, V, SP, A, and H all equal 8 hours, use =8*(SUM(COUNTIF(P4:BN20,{"W","V","SP","A","H"}))+SUM(COUNTIF(P36:BN50,{"W","V","SP","A","H"}))).
Why is my 1/2V formula returning zero?
Check that the calendar cells contain exactly "1/2V" with no extra spaces or different characters. COUNTIF requires an exact text match.
Can COUNTIF calculate totals from non-adjacent calendar ranges?
Yes. Add a COUNTIF for each range, such as COUNTIF(P4:BN20,"W")+COUNTIF(P36:BN50,"W"), then multiply by the hours for that code.
What formula should I use if code hours may change later?
Create a code list and hour-value list, then use SUMPRODUCT(COUNTIF(calendar_range,code_list),hour_list) for each calendar range. Updating the hour-value list changes the total automatically.




