Extensive IFS

robatthe2A

New Member
Joined
Dec 27, 2024
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I’ll post the mini-sheet when I’m back home but here’s the just of it…

Column A: Representative Name
Column B: Activity Name
Column C: Start Time (12/12/2024 12:12:12PM)
Column D: End Time
Column E: Duration (HH:MM)
Column F: Notes

What I currently have: Conditional formatting that colors all “idle” yellow. Filters are applied to all columns to I can easily sort oldest to newest “Start Time” then a to z on “Representative Name” so that each representatives activities are in chronological order by start time. I also have a conditional formatting formula built in to color green an activity name that has an activity name following it with a start time in between its start and end times.

What I’m trying to accomplish: each representatives activities to sort or filter some how to show only when “idle” is in an activity. Secondly, to share (could be conditional) if the representative is over or under their break to lunch by more than 1 minute (some reps have 10 minute breaks and some have 15 minute breaks). Lastly, to determine if they started their shift over or under the same 1 minute threshold (schedules vary).

Im open to suggestions! Thank you!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Something like this?
Book1
ABCDEFGHI
1
2GP Available_112034*1/2/25 13:451/2/25 13:490:04:05IDLE1/2/25 13:471/2/25 13:480:01:11
3IDLE1/2/25 13:471/2/25 13:480:01:11
4GP Phone_112034*1/2/25 13:491/2/25 13:590:10:23
5GP Available_112034*1/2/25 13:591/2/25 13:590:00:03
6GP NR Follow Up Work_112034*1/2/25 13:591/2/25 14:060:06:59
7GP Available_112034*1/2/25 14:061/2/25 14:120:05:26
8GP Phone_112034*1/2/25 14:121/2/25 14:230:10:54
9GP ACW_112034*1/2/25 14:231/2/25 14:230:00:07
10GP NR Callbacks_112034*1/2/25 14:231/2/25 14:230:00:08
11GP Phone_112034*1/2/25 14:231/2/25 14:240:00:42
12GP ACW_112034*1/2/25 14:241/2/25 14:240:00:12
Sheet3
Cell Formulas
RangeFormula
F2:I2F2=FILTER(A2:D12,A2:A12="IDLE")
Dynamic array formulas.
I'm not sure if I was supposed to copy all of the code?

Here is a formula that is partially working for what I'm trying to accomplish but it's still not pulling correctly...
=IFS(AND(B25="IDLE",OR(C25<D24,C25<D23,C25<D22,C25<D21,C25<D20,C25<D19,C25<D18,C25<D17,C25<D16,C25<D15,C25<D14,C25<D13,C25<D12,C25<D11,C25<D10,B24="GP Available_112034*",B23="GP Available_112034*",B22="GP Available_112034*",B21="GP Available_112034*",B20="GP Available_112034*",B19="GP Available_112034*",B18="GP Available_112034*",B17="GP Available_112034*",B16="GP Available_112034*",B15="GP Available_112034*",B14="GP Available_112034*",B13="GP Available_112034*",B12="GP Available_112034*",B11="GP Available_112034*",B10="GP Available_112034*")),E25)
 
Upvote 0
Book1
ABCDEFGHI
1
2GP Available_112034*1/2/25 13:451/2/25 13:490:04:05Start Time1/2/25 13:45
3IDLE1/2/25 13:471/2/25 13:480:01:11End Time1/2/25 14:23
4GP Phone_112034*1/2/25 13:491/2/25 13:590:10:23
5GP Available_112034*1/2/25 13:591/2/25 13:590:00:03IDLE1/2/25 13:471/2/25 13:480:01:11
6GP NR Follow Up Work_112034*1/2/25 13:591/2/25 14:060:06:59
7GP Available_112034*1/2/25 14:061/2/25 14:120:05:26
8GP Phone_112034*1/2/25 14:121/2/25 14:230:10:54
9GP ACW_112034*1/2/25 14:231/2/25 14:230:00:07
10GP NR Callbacks_112034*1/2/25 14:231/2/25 14:230:00:08
11GP Phone_112034*1/2/25 14:231/2/25 14:240:00:42
12GP ACW_112034*1/2/25 14:241/2/25 14:240:00:12
Sheet3
Cell Formulas
RangeFormula
G2G2=B2
G3G3=B10
F5:I5F5=FILTER(A2:D12,(A2:A12="idle")*(B2:B12>=G2)*(C2:C12<=G3))
Dynamic array formulas.
 
Upvote 0
Book1
ABCDEFGHI
1
2GP Available_112034*1/2/25 13:451/2/25 13:490:04:05Start Time1/2/25 13:45
3IDLE1/2/25 13:471/2/25 13:480:01:11End Time1/2/25 14:23
4GP Phone_112034*1/2/25 13:491/2/25 13:590:10:23
5GP Available_112034*1/2/25 13:591/2/25 13:590:00:03IDLE1/2/25 13:471/2/25 13:480:01:11
6GP NR Follow Up Work_112034*1/2/25 13:591/2/25 14:060:06:59
7GP Available_112034*1/2/25 14:061/2/25 14:120:05:26
8GP Phone_112034*1/2/25 14:121/2/25 14:230:10:54
9GP ACW_112034*1/2/25 14:231/2/25 14:230:00:07
10GP NR Callbacks_112034*1/2/25 14:231/2/25 14:230:00:08
11GP Phone_112034*1/2/25 14:231/2/25 14:240:00:42
12GP ACW_112034*1/2/25 14:241/2/25 14:240:00:12
Sheet3
Cell Formulas
RangeFormula
G2G2=B2
G3G3=B10
F5:I5F5=FILTER(A2:D12,(A2:A12="idle")*(B2:B12>=G2)*(C2:C12<=G3))
Dynamic array formulas.
Similar to the last one in results- null. The complexity is finding a way to extract the idle from the available times. That's the hang up.
 
Upvote 0
Similar to the last one in results- null. The complexity is finding a way to extract the idle from the available times. That's the hang up.
You're not making much sense and refused to demonstrate an expected result. Can't help you further.
 
Upvote 0
You're not making much sense and refused to demonstrate an expected result. Can't help you further.
I haven't refused to demonstrate an expected result- I'm not sure how to demonstrate something I can't formulate. I'm not sure what is not making sense- there are various activity titles- for instance, Phone or Available (simplifying) I would like to try and find a way to pull the duration of Idle that are within Available only. Your prior suggested formulas pulled all Idle. I can't upload the mini-sheet driver on here or I would do that. Also- I'm about a week into posting threads here... I'm not used to this in any capacity... I have however, appreciated your assistance thus far and the knowledge I've learned prior from other threads.
 
Upvote 0
I can't upload the mini-sheet driver on here or I would do that.
Are you using a work managed computer or personal computer?
The reason I ask is that the Excel Add-in doesn't require Admin rights to install. They also provide alternative installation methods if needed on the Add-In page.
Personally, if it were me, I'd look at the following two options:

1. Get with your Manager and have IT get it installed if Group Polices are blocking all Add-Ins... which would be strange to say the least.
Just remind your manager you're looking for a solution for a work assignment/project; unless they got someone in house that specializes in VBA programming...?
2. Open the Excel Workbook on my own computer to take advantage of the benefits the Add-In offers.

Best regards,
 
Last edited:
Upvote 0
I haven't refused to demonstrate an expected result- I'm not sure how to demonstrate something I can't formulate.
I'm not asking you to formulate the solution. Just asking what is your expected result is.
 
Upvote 0
I'm not asking you to formulate the solution. Just asking what is your expected result is.
And I believe I’ve done that- extract the idle that is within a specific activity. Such as, idle within available time. The problem is- the formulas attempted cannot seem to filter that data out. I’m not sure if there’s a way around this or not.
 
Upvote 0

Forum statistics

Threads
1,225,481
Messages
6,185,249
Members
453,283
Latest member
Shortm88

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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