+ Reply to Thread
Results 1 to 19 of 19

Count based on weekdays

  1. #1
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Count based on weekdays

    Hi everybody
    I'm working on an attendance sheet but I'm calculating the absence days by blank cells but the weekend is Sunday not for all
    I need to count if an employee checked in or out any 6 days pass the 7th day ( cells in red ) but if he checked in or out 5 days leave the 6th day as weekend and the 7th day as absent (cell in green)
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count based on weekdays

    I've looked at this twice... and don't understand what you want. So...

    What do you want? Where do you want it to appear? Show (and explain) some manually calculated results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    I created another sheet with the desired results & there is comments for all cells
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Count based on weekdays

    I agree with Glenn, you have so many comments there that is hard to see what you want, where.

    You add a comments that...
    "I need blank result in this cell" but based on what?
    or
    "I need This cell the same result" but the same as what?

    Maybe take this 1 step (cell) at a time and walk us though what you want to do?
    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

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count based on weekdays

    In S4, and fill down

    =IF(AND(D4="",L4=""),1,"")

    Then copy to the other Absence columns.

    edit:- ignore that, only works with first column.
    Last edited by jason.b75; 08-05-2018 at 01:30 PM.

  6. #6
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    My formula in K21 "=IF((D21="")*(G21=""),IF(WEEKDAY(D$2)=1,"",1),"")"
    looking for empty check in or check out for Employee 18 to make him absent "1" but exclude Sundays as weekend
    But in branch Alex Sunday is working day for some employees not all
    The employee that work in Sunday take another day off instead of Sunday
    so I counted workdays every week by week number in sheet "Log In-Out" the result is "employee 19" "ID=34" has worked 6 days in week number 27
    so I need the formula exclude the 7th day form being absent "1" as long as the employee has worked 6 days
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count based on weekdays

    An explanation !! I'll take another look in the morning.

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count based on weekdays

    Think I might have just cracked it taking advantage of the evil merged cells (3rd time this week, I'm starting to make a bad habit of this).

    In S4, fill down, then copy to the rest of the absence columns.

    =IF(COUNTIFS(INDEX($2:$2,0,MAX(4,COLUMN(S4)-55)):L$2,"<>",INDEX(4:4,0,MAX(4,COLUMN(S4)-55)):L4,"")>1,1,"")

    It should enter '1' wherever there is more than 1 check-in missing in the last 7 days. As there is no information relating to the end of the previous month, the formula will permit 1 missing check in anywhere in the first 7 days, which appears to match the highlighting in the sample file.

  9. #9
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    It's working but some results are wrong ( the blue cells )
    All blue cells must give a blank result because the check in or the check out is not empty in those days
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count based on weekdays

    This should fix that

    =IF(L4<>"","",IF(COUNTIFS(INDEX($2:$2,0,MAX(4,COLUMN(S4)-55)):L$2,"<>",INDEX(4:4,0,MAX(4,COLUMN(S4)-55)):L4,"")>1,1,""))

  11. #11
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    It didn't fix it I need to exclude Sundays and this formula didn't exclude Sundays
    the first formula was better
    Capture.PNG

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count based on weekdays

    That incorrect result in in K6, but there should be no formulas in column K, they should all be blank so the formula will serve no purpose there. The first one goes in S4.

  13. #13
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    I'm sorry my mistake
    It works fine but can I ask you if you can make the formula look for any of check in or check out
    because it give absence result if the check in is blank but the check out is not blank
    Capture 2.PNG
    Last edited by Acc.mero; 08-05-2018 at 05:37 PM.

  14. #14
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count based on weekdays

    Surely that is a mistake that needs correcting, not ignoring. How can an employee check out if they haven't checked in?

  15. #15
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    It happens as a result of glitch in the bio-metric login device
    so I need to check if one of "check in" or "check out" missing to consider the other one

  16. #16
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    Can I Ask what's 4 here "MAX(4,COLUMN(S4)-55)" stand for ?
    I'm trying to apply the formula in the main sheet and I got #Value
    so I'm trying to understand the formula
    Capture 3.PNG

  17. #17
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count based on weekdays

    That section calculates the starting point (first day) of the rolling 7 days.
    4 denotes the first check in column, in the case of the sample file, column D. If the first check in in your main sheet is in column H, as it appears from the screep capture, then change 4 to 8.

    S4 should refer to the cell holding the formula, so in the screen capture above, it should be T5.

    It looks like you have some extra columns in your main sheet, so 55 will need to be changed. In the old sheet, you had 8 columns per day, for 7 days 8 columns * 7 days = 56 columns total.

    Because the formula goes into the last column of those being counted, you need to subtract 1 from 56, which results in 55.

    Your main file screen capture looks like it has 13 columns per day, so 13 * 7 - 1 = 90, resulting in a revised formula section of MAX(8,COLUMN(T5)-90)

    Remember, the formulas need to start on the second day, not the first. Any formula in the first day will give incorrect results due to the lack of data from previous days.

    If you get the existing formula matched up to your main sheet, then we can look at expanding it to look at check in and check out.

  18. #18
    Registered User
    Join Date
    06-29-2018
    Location
    Cairo
    MS-Off Ver
    2016
    Posts
    27

    Re: Count based on weekdays

    Thanks for your help
    I appreciate your time and patience
    Now it works fine & I added check out

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Count based on weekdays

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. [SOLVED] want to count number of occurances on weekdays/weekends
    By phango13 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-14-2014, 08:07 PM
  2. How to count weekdays
    By felixpanganiban in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-31-2013, 02:30 PM
  3. Count Weekdays
    By AlinaH in forum Excel General
    Replies: 9
    Last Post: 08-02-2012, 04:16 PM
  4. Replies: 4
    Last Post: 11-19-2010, 12:42 AM
  5. Excel Count Weekdays Formula
    By Ziggy M in forum Excel General
    Replies: 9
    Last Post: 07-14-2006, 08:10 PM
  6. [SOLVED] How do i count number of weekdays between two dates?
    By Sanjay Shah in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2005, 12:06 PM
  7. [SOLVED] count weekdays in a date range
    By benb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-13-2005, 11:06 AM

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