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

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.
 

Attachments

Joined
Mar 27, 2006
Messages
64
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

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)))))
 
Joined
Mar 27, 2006
Messages
64
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

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.
 

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)
 
Joined
Mar 27, 2006
Messages
64
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

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
 
Joined
Mar 27, 2006
Messages
64
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.
 

Chawbacon

Jack
Joined
Jul 9, 2018
Messages
565
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
 

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.
 

csweezy

Thread Starter
Joined
Sep 15, 2018
Messages
8
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.
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top