# Calculating Half Days in Attendance

1. ## Calculating Half Days in Attendance

Hi

I am trying to count Half Days & Full Days in Attendance but have problems doing that. SL/P is Sick Leave Half Day, EL/P is Earned Leave Half Day & CO/P is Compensation Off Half Day. I tried the below formula

=(COUNTIF(I123:AM123,"P")+(COUNTIF(I123:AM123,{"SL/P","EL/P","CO/P"})/2))

(P = Present Days)

Here SL/P is in first place, EL/P is in second place & CO/P is in third place.

Imagine if CO/P leave is mentioned in the row, but this formula does not recognise it and counts only present days. If i bring CO/P in first position in the formula only then it recognises and counts the present days & the half day. Every time i have to change the position of the Half Day to first in order to be recognised & counted.

Real Situation Example :
1.

=(COUNTIF(I123:AM123,"P")+(COUNTIF(I123:AM123,{"EL/P","SL/P","CO/P"})/2))

In this scenario if a person is taken all the Half Day leaves only EL/P is recongised

2.
=(COUNTIF(I123:AM123,"P")+(COUNTIF(I123:AM123,{"CO/P","SL/P","EL/P"})/2))

In this scenario if a person is taken all the Half Day leaves only CO/P is recongised

3.
=(COUNTIF(I123:AM123,"P")+(COUNTIF(I123:AM123,{"SL/P","CO/P","EL/P"})/2))

In this scenario if a person is taken all the Half Day leaves only SL/P is recongised

Can Any one Help ?  Register To Reply

2. ## Re: Calculating Half Days in Attendance

Add a Sum(), because Array in Criteria needs some in front of Countif =COUNTIF(I123:AM123,"P")+(SUM(COUNTIF(I123:AM123,{"SL/P","EL/P","CO/P"}))/2)  Register To Reply

3. ## Re: Calculating Half Days in Attendance

=COUNTIF(I123:AM123,"P")+COUNTIF(I123:AM123,"*/*")/2
Try this  Register To Reply

4. ## Re: Calculating Half Days in Attendance

For range A1:H1 ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: Calculating Half Days in Attendance

Thank you Sixthsense i missed out SUM in the formula. Thanks.  Register To Reply

6. ## Re: Calculating Half Days in Attendance

Thank you nflsales this also worked.  Register To Reply

7. ## Re: Calculating Half Days in Attendance

I have a situation here. The above formulas to calculate the half days in the attendance is absolutely working fyn but i also have lowercase " p " which is denoting as half day for few cases. As the present days are already showing as P (upper case P) how can i make this lower case p to be counted as half day.

Can anyone help me with a formula for this?  Register To Reply

8. ## Re: Calculating Half Days in Attendance

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic  Register To Reply

9. ## Re: Calculating Half Days in Attendance

=SUMPRODUCT(((CODE(LEFT(I123:AM123&"-",1))=80)+(CODE(LEFT(I123:AM123&"-",1))=112)/2))+COUNTIF(I123:AM123,"*/*")/2
try this  Register To Reply

10. ## Re: Calculating Half Days in Attendance

Dear Sixthsense

Please find the sample work book i have detailed what the current formula is & what i want the formula to be like, with an example.

Suggest a formula to meet my requirement.  Register To Reply

11. ## Re: Calculating Half Days in Attendance

Mr.nflsales

Amazing ! that is working. Thank you

Please explain me this formula for i dont have sound knowledge about excel, this will help me in future if any such issues arise.  Register To Reply

12. ## Re: Calculating Half Days in Attendance

If you have P or p in the cell
=CODE(I123:AM123) will give it will give 80 or 112
if you have an empty cell it will give "#VALUE!"
so that I added "-" to the cell
to get the only one letter from cell I used left function

for more details select formula from toolbar
then select the Evaluate Formula then click on evaluate keep on click on evaluate, here you will find how the formula is working  Register To Reply

13. ## Re: Calculating Half Days in Attendance

Or

You can use Exact() function as an alternative to Code()   Register To Reply

14. ## Re: Calculating Half Days in Attendance

I was just checking how good does this work, i copied the attendance data & the formula from just a single line & pasted it on a blank excel sheet, i tried adding lower case "p" to each cell but the count is taken only after typing in two cells, but when i type "p" in third cell total count still shows same count as previous.  Register To Reply

15. ## Re: Calculating Half Days in Attendance

You want me to take the formula as
=SUMPRODUCT(((EXACT(LEFT(I123:AM123&"-",1))=80)+(EXACT(LEFT(I123:AM123&"-",1))=112)/2))+COUNTIF(I123:AM123,"*/*")/2 ??  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1