+ Reply to Thread
Results 1 to 19 of 19

Calculating Half Days in Attendance

  1. #1
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Question 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 ?

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,638

    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)


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,519

    Re: Calculating Half Days in Attendance

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

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,869

    Re: Calculating Half Days in Attendance

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

  5. #5
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Thumbs up Re: Calculating Half Days in Attendance

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

  6. #6
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Re: Calculating Half Days in Attendance

    Thank you nflsales this also worked.

  7. #7
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    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?

    Thanks in Advance.

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,638

    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

  9. #9
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,519

    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

  10. #10
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    Talking 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.


    Thanks in advance.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    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.

  12. #12
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,519

    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

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,638

    Re: Calculating Half Days in Attendance

    Or

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

  14. #14
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    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.

    Please advice me how to go about this.
    Last edited by EphraimD; 01-28-2015 at 04:55 AM.

  15. #15
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    8

    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 ??
    Last edited by EphraimD; 01-28-2015 at 05:09 AM.

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,638

    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"))

  17. #17
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    14

    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.
    Last edited by Felix_I; 12-06-2018 at 11:52 AM. Reason: Adding info

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,245

    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.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new 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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  19. #19
    Registered User
    Join Date
    12-06-2018
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    14

    Re: Calculating Half Days in Attendance

    I apologize, I will start a new thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Number of Workers per Half Hour
    By vioravis in forum Excel General
    Replies: 1
    Last Post: 10-14-2015, 09:55 PM
  2. counting half days
    By chanman008 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-11-2014, 03:23 PM
  3. 30 Days Perfect Attendance
    By ForumShy in forum Excel General
    Replies: 6
    Last Post: 02-07-2012, 06:28 PM
  4. Excel Sick Days / Half Days
    By Pixie_1 in forum Excel General
    Replies: 6
    Last Post: 02-28-2007, 08:28 AM
  5. [SOLVED] How do I track half days in the attendance tracking template?
    By Business Manager in forum Excel General
    Replies: 1
    Last Post: 10-20-2005, 03:05 PM

Bookmarks

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