1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with Excel.. counting consecutive days worked

Discussion in 'Business Applications' started by csweezy, Sep 15, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    I am tracking employees consecutive days worked. One cant work more than 13 consecutive days. Formula should count consecutive days worked, tough part is.... needs to reset count when employee doesn't work. Also, formula should base count on current date, which will change each day. Times in cells are clock in times, empty cells are days employee did not work. Hope I explained it ok.... I sincerely appreciate your patience and help. Thanks you.
     

    Attached Files:

  2. petef56

    petef56

    Joined:
    Mar 27, 2006
    Messages:
    59
    Here's one solution. Let's see if anyone else comes up with something better.

    1.) Use copy & paste to make a duplicate chart, including the names and all the data just below the original. Leave a couple of blank rows between them.

    2.) In the bottom set, in the cell for "Sweezy, Chris" / "9/5" Enter formula below:
    Code:
    =IF(COUNT(D2:P2)=13;"!!!!!"; "")
    3.) In the bottom set, Copy & Paste that formula to each cell for each Employee. Set format of all those cells to be "Centered"

    That's it!
    Simply look at the bottom set and anywhere you see "!!!!!", there are 13 consecutive days worked.

    A cleaner more complicated way to do it is to Write a Macro, but I only know VBA macro language in Excel.

    --pete--
     
  3. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    petef56, thanks for your help. A formula was suggested to me, but I can't get it to work.. Would you mind taking a look? This formula would go in the "Days Worked" cell.
    =MAX((FREQUENCY(IF(E2:U2=1,COLUMN(E2:U2)),IF(E2:U2<>1,COLUMN(E2:U2)))))
     
  4. petef56

    petef56

    Joined:
    Mar 27, 2006
    Messages:
    59
    I'll take a look, but just looking at this formula, I don't see anything related to the 13 limit. Am I missing something?

    --pete--
     
  5. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    I'm not sure... I wasn't familiar with using Column or Frequency, don't understand how it all goes together.
     
  6. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    petef56,
    looking back at my notes.. that formula returns the number of days consecutive days worked(restarts when day missed), but one would have to delete each column as each day passes(keeping number of past days at 13, in relation to present day worked)
     
  7. petef56

    petef56

    Joined:
    Mar 27, 2006
    Messages:
    59
    I found out what part of the formula is getting the error.
    Code:
    =IF(E2:U2<>1,COLUMN(E2:U2))
    More specifically, it's the first ":" causing the error.
    E2:U2<>1 makes no sense to me!
    I have no idea what the purpose is of that part of the formula.

    The COLUMN(E2:U2) alone results in.. 5
    So I'm assuming that part is ok.

    Can you get a better explanation of that part of the formula from your source? Otherwise I'm stuck.

    --pete--
     
  8. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    It is saying E2 thru U2 less than or greater than 1...? the formula is supposed to count consecutive 1's(days worked) and restart when encountering either 14th day or 0
     
  9. petef56

    petef56

    Joined:
    Mar 27, 2006
    Messages:
    59
    The formula itself makes no sense. It's missing something. That's what's causing the error.
     
  10. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    Thanks for your help. I have no idea, that's why I came here... lol
     
  11. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    Hello Csweezy,

    I have put some thought into this using the Count and CountIF functions; unfortunately, I cannot envision a formula that would meet your requirements of resetting to zero at a Null value, or counting cells backward until a Null value is reached. Therefore, I reached out to one of the other forum members who is pretty good with VBA. Hopefully that individual can lend a hand after receiving my message.

    Good Luck
     
  12. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    Thank you so much.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Is this still an issue?
    VBA should be able to do this by looping through the columns and setting a start and end point while also counting the number of columns processed. When a blank column occurs the "start point" is reset and the end point & count tells how many columns were processed.
     
  14. csweezy

    csweezy Thread Starter

    Joined:
    Sep 15, 2018
    Messages:
    8
    I haven't found a solution. I know nothing about or how to use VBA.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will take a look at your example and get back to you.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1216574

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice