+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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,344

    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 2003; 2007
    Posts
    6,704

    Re: Calculating Half Days in Attendance

    =COUNTIF(I123:AM123,"P")+COUNTIF(I123:AM123,"*/*")/2
    Try this
    Click *Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) If you are satisfied with the answer add reputation for the one who helps you

    Regards

    Samba

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

    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,344

    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 2003; 2007
    Posts
    6,704

    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 2003; 2007
    Posts
    6,704

    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,344

    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.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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