If A without B, then C / Employees currently on break based on break list schedule.

[Olisthoughts]

Hi,

I'm making a break list at my new job for my team.

Because the list has multiple entries we are forced to concentrate unnecessarily to calculate how many agents are on break. It would be helpful for my colleagues to see how many agents are on break right now, since we have a rule of no more than 3 agents on break at the same time.

As you'll see in the screenshot, the agents can double-click on a cell for time input under Start time, and double-click on actual return time when they're back.

I'm assuming the way to do it is count the number of agents (rows) that have a value in start time with no value in return time, as that would be an indicator they're on break.

But no matter how I tried I couldn't figure it out. The closest I got was a formula that in pseudo code, basically said If start time + End time equals 1, then 1 agent on break, else nothing. However when more than 1 person was on break it would show 11 instead of 2.

For context, I will attach the codes I'm using (that are working), a screenshot or two, and also, for information, after an agent enters a value in start time or end time, those cells are locked. The estimated return time and actual break time are alway

s locked. And there are actually 15 agents on the team, I've only added 4 to work with.

Codes:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if updated cells in watched range
Set rng = Intersect(Target, Range("c:u"))

' Loop through cells in watched range
If Not rng Is Nothing Then
For Each cell In rng
If cell <> "" Then
ActiveSheet.Unprotect Password:="Break"
cell.Locked = True
ActiveSheet.Protect Password:="Break"
End If
Next cell
End If

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("c:u")) Is Nothing Then
Cancel = True
Target.Formula = Time
End If
End Sub

 

[Olisthoughts]

P.S. Wayne, regarding the colors - I'm trying that in the locally installed Excel, it's still not working.

 

[etaf]

so that counts 1 and you dont have an actual return time on 1st name and so the count should be 1
as shown

 

[etaf]

conditional formatting
can you copy and past the actual formula you are using
make sure the
the number of characters between the "" are exact
so if you have a space after the :

="Currently on Break: "
you need to make sure its the same on conditional formatting
mine above has 3 spaces after the :

 

[Olisthoughts]

I think I just got it to work.

I've used =A20="Currently on Break: "&1
on the A20 cell.

Previously I tried only A20 = Currently on break: 1
And =C19="Currently on Break: "&1 which of course wasn't gonna work.

 

[Olisthoughts]

I was able to add the helper column, that works fine.
However I couldn't get the Validation column to work properly.
It was showing me "FALSE" when two start times were introduced, so the math was in place, but I was still able to introduce two start times without end times.
Any idea how I can identify the problem and fix it?

 

[etaf]

you need to add the validation to a validation rule
Ignore the formula in a column - jut an example

Data
Validation
Custom
ADD as a formula
=(COUNTIFS($C4:$C4,"<>"&"",$E4:$E4,"")+COUNTIFS($H4:$H4,"<>"&"",$J4:$J4,"")+COUNTIFS($M4:$M4,"<>"&"",$O4:$O4,""))<2
then a message
see my dropbox sample added earlier in a post #16

 

[Olisthoughts]

I still can't figure it out.

In the cell that says TRUE, I tried adding the formula through Data Validation, I tried adding it as a cell formula, I tried both, and I tried adding the formula through data validation after selecting both all the cells in row 4, and only the cells with start time. It still didn't work.

Do I need to add the formula elsewhere besides Data validation?

 

[etaf]

no just data validation
BUT the formula probably needs changing based on your data exact columns and rows used

Assuming it starts in row 4 , as previous images and column C

Select C4 to U18
then goto

Data
Validation
Custom
in the box that appears
Formula
add
=(COUNTIFS($C4:$C4,"<>"&"",$E4:$E4,"")+COUNTIFS($H4:$H4,"<>"&"",$J4:$J4,"")+COUNTIFS($M4:$M4,"<>"&"",$O4:$O4,"") +COUNTIFS($R4:$R4,"<>"&"",$T4:$T4,""))<2

Error Alert Tab
you could say, whatever you like
Please enter a time for end break


see here, hopefully match your rows

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Olisthoughts]

I checked the code previously, and I double-checked it again, the row and column numbers are matching.
I selected the cells C4 to U18, copy-pasted the exact formula you wrote (checked it), and it still didn't work. The only change is that the error message is appearing when hovered over any one cell.
I'm unable to open your shared documents.

 

[Olisthoughts]

But I am still able to enter data in a Start time cell, while there's another start time cell with data and end time cell with no data.
Now, since like I said I'm new to this, I'm not sure of the exact calculations the formula does, but I see there are some COUNTIFS, and then at the end smaller than 2, can we try to add a "Then block cells" after smaller than 2 section?