# Help with Excel.. counting consecutive days worked

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

Not open for further replies.

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.

File size:
10.5 KB
Views:
10
2. ### 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--

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

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

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.

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

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

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

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

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

11. ### 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

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

13. ### 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.

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

15. ### OBP

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

As Seen On