# Help with Excel.. counting consecutive days worked

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### csweezy

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.

#### Attachments

• 10.5 KB Views: 16

#### petef56

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

#### csweezy

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

#### petef56

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

#### csweezy

I'm not sure... I wasn't familiar with using Column or Frequency, don't understand how it all goes together.

#### csweezy

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)

#### petef56

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

#### csweezy

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

#### petef56

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
The formula itself makes no sense. It's missing something. That's what's causing the error.

#### csweezy

Thanks for your help. I have no idea, that's why I came here... lol

#### Chawbacon

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

#### csweezy

Thank you so much.

#### OBP

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.

#### csweezy

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.
I haven't found a solution. I know nothing about or how to use VBA.

#### OBP

I will take a look at your example and get back to you.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.

over 807,865 other people just like you!