Excel Formula Help: How to Accurately Calculate Half-Day CL Totals?

Hi Friends, I am working on payroll data. Now, if a person has taken a half-day on three separate days as CL, then what formula should I use in Excel so that the sum of all the CL taken during the month arrives at 1.5 days? Please share inputs.

Regards,
Gia

Thank you for responding. I am attaching a sample sheet. I want to plug in a formula to calculate the sum of Half Day (HD) as per leave type (CL/SL, PL). The total leaves should appear in columns AJ to AM. I am a little stuck here. Your inputs will help me.

Regards,
Gia

Hi Gia, use the formula below in your "Leave (CL)" column. It will give you the correct figure:

=((COUNTIF(F5:AH5, "CL HD"))/2)+((COUNTIF(F5:AH5, "CL")))

Verify and let me know if it comes out to be correct.

Similarly, you can replace CL with PL and SL to calculate the total number of leaves (either full day or half day).