Status
Not open for further replies.

RandyD123

Board Regular
What I need is a formula that will work in column LP for each row.

Info that may help:

Each person has two rows because they can be in 1 of 2 location each day. Some days they may spend a few hours in each location.

My range is from F3 to LO3 for each person. All columns below row 2 are manual entries, except column LP!!

I only need to track consecutive zeros but not include RDO's

The date in the LP column will change to todays date using =TODAY()

Once any row for a person hits 14, meaning 14 consecutive zero's, not including RDO's their name has to change to yellow.

If a person gets any number on any given day, other than zero or RDO the count has to start over. Basically RDO's should be completely ignored, and only zero's get counted

In the example you can see what it should look like and what should happen.

On 6/29 column LL will get a manual input of either a whole number in quarter hour increments or get a zero or get another RDO.

LP3 counted 10 consecutive zero's and on 6/29 if they get a any number other than zero that current value of 10 has to change to a 0. If they get a zero on 6/29 that current value of 10 has to change to 11

LP4 counted 1 zero and on 6/29 it will either change to a 2 or if they get a number it has to change to 0

LP7 has to change to either 0 or 14, depending on what gets entered on 6/29. If it changes to 14 their name has to highlight in yellow.

LP10 has a 14 which means that their name should have highlighted in yellow on 6/28. If they get a number on 6/29, that value of 14 has to change to 0 and their name will lose the highlight.

I have a sheet on GDrive. Any help would be very much appreciated. Thank You.

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Duplicate to: Counting consecutive Zeros

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.

Status
Not open for further replies.

Replies
12
Views
812
Replies
2
Views
106
Replies
9
Views
119
Replies
12
Views
526
Replies
3
Views
434

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back