# 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

16. ## Re: Calculating Half Days in Attendance

Using Exact()

=SUM(SUMPRODUCT(--EXACT(C6:AG6,"p"))*0.5,COUNTIF(C6:AG6,"EL/P")*0.5,COUNTIF(C6:AG6,"P"))

Or

Using Find()

=SUM(SUMPRODUCT(--ISNUMBER(FIND("p",C6:AG6)))*0.5,COUNTIF(C6:AG6,"EL/P")*0.5,COUNTIF(C6:AG6,"P"))  Register To Reply

17. ## Re: Calculating Half Days in Attendance

Hello,

I know this thread is very old but it is the closest to my question.

I am also keeping an attendance and I have to total for Sick days. I have two Total lines:
1 Total line is recording the accumulated sick days: =COUNTIF(D9:D269,"S") and the other
1. Total line is recording the accumulated half of days sick =COUNTIF(D9:D269,".5S")

I want to only have one Total line, therefore, what formula do I have to use for the chart to add days in half days too? I tried =SUM(COUNTIFS(D9:D269,{"S",".5S"})) but this adds full days only.

Thank you.  Register To Reply

18. ## Re: Calculating Half Days in Attendance

Felix_I welcome to the forum Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.  Register To Reply

19. ## Re: Calculating Half Days in Attendance

I apologize, I will start a new thread.  Register To Reply