Extensive IFS

[robatthe2A]

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!

 

[robatthe2A]

GP Available_112034*	1/2/2025 1:45:25 PM	1/2/2025 1:49:30 PM	0:04:05
IDLE	1/2/2025 1:47:23 PM	1/2/2025 1:48:34 PM	0:01:11
GP Phone_112034*	1/2/2025 1:49:30 PM	1/2/2025 1:59:53 PM	0:10:23
GP Available_112034*	1/2/2025 1:59:53 PM	1/2/2025 1:59:56 PM	0:00:03
GP NR Follow Up Work_112034*	1/2/2025 1:59:56 PM	1/2/2025 2:06:55 PM	0:06:59
GP Available_112034*	1/2/2025 2:06:55 PM	1/2/2025 2:12:21 PM	0:05:26
GP Phone_112034*	1/2/2025 2:12:21 PM	1/2/2025 2:23:15 PM	0:10:54
GP ACW_112034*	1/2/2025 2:23:15 PM	1/2/2025 2:23:22 PM	0:00:07
GP NR Callbacks_112034*	1/2/2025 2:23:22 PM	1/2/2025 2:23:30 PM	0:00:08
GP Phone_112034*	1/2/2025 2:23:30 PM	1/2/2025 2:24:12 PM	0:00:42
GP ACW_112034*	1/2/2025 2:24:12 PM	1/2/2025 2:24:24 PM	0:00:12

 

[robatthe2A]

Any ideas on how I can extract just the idle that is in Available for instance? I can't seem to come up with a formula that pulls correctly. (Needs to sum all of the idle inside of Available for instance)

 

[zero269]

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!

It's best to share your sample data using the forums Excel Add-In... located here: XL2BB - Excel Range to BBCode
It makes it easier for others to help you.
The more seasoned members (not me) would also refer you to the forum rules as well.
Welcome to the forum and best regards,

 

[robatthe2A]

It's best to share your sample data using the forums Excel Add-In... located here: XL2BB - Excel Range to BBCode
It makes it easier for others to help you.
The more seasoned members (not me) would also refer you to the forum rules as well.
Welcome to the forum and best regards,

Unfortunately, I can't get the add ins on this computer. I did read the board rules again thinking I must have missed something- if I've violated one- can you let me know which one?

Thank you.

 

[Cubist]

Needs to sum all of the idle inside of Available for instance

No idea what this means. Show some expected (manual) results.

 

[robatthe2A]

No idea what this means. Show some expected (manual) results.

I do not have a working formula that does what I need it to do- essentially I need to extract idle time that is within available time. I’m played with SUMIFS, XLOOKUP, and various IFS/AND formulas.

Any ideas on how I can pull only the idle durations that are within available durations?

 

[Cubist]

Something like this?

Book1
	A	B	C	D	E	F	G	H	I
1
2	GP Available_112034*	1/2/25 13:45	1/2/25 13:49	0:04:05		IDLE	1/2/25 13:47	1/2/25 13:48	0:01:11
3	IDLE	1/2/25 13:47	1/2/25 13:48	0:01:11
4	GP Phone_112034*	1/2/25 13:49	1/2/25 13:59	0:10:23
5	GP Available_112034*	1/2/25 13:59	1/2/25 13:59	0:00:03
6	GP NR Follow Up Work_112034*	1/2/25 13:59	1/2/25 14:06	0:06:59
7	GP Available_112034*	1/2/25 14:06	1/2/25 14:12	0:05:26
8	GP Phone_112034*	1/2/25 14:12	1/2/25 14:23	0:10:54
9	GP ACW_112034*	1/2/25 14:23	1/2/25 14:23	0:00:07
10	GP NR Callbacks_112034*	1/2/25 14:23	1/2/25 14:23	0:00:08
11	GP Phone_112034*	1/2/25 14:23	1/2/25 14:24	0:00:42
12	GP ACW_112034*	1/2/25 14:24	1/2/25 14:24	0:00:12
Sheet3

Cell Formulas
Range	Formula
F2:I2	F2	=FILTER(A2:D12,A2:A12="IDLE")
Dynamic array formulas.

 

[robatthe2A]

Something like this?

Book1
	A	B	C	D	E	F	G	H	I
1
2	GP Available_112034*	1/2/25 13:45	1/2/25 13:49	0:04:05		IDLE	1/2/25 13:47	1/2/25 13:48	0:01:11
3	IDLE	1/2/25 13:47	1/2/25 13:48	0:01:11
4	GP Phone_112034*	1/2/25 13:49	1/2/25 13:59	0:10:23
5	GP Available_112034*	1/2/25 13:59	1/2/25 13:59	0:00:03
6	GP NR Follow Up Work_112034*	1/2/25 13:59	1/2/25 14:06	0:06:59
7	GP Available_112034*	1/2/25 14:06	1/2/25 14:12	0:05:26
8	GP Phone_112034*	1/2/25 14:12	1/2/25 14:23	0:10:54
9	GP ACW_112034*	1/2/25 14:23	1/2/25 14:23	0:00:07
10	GP NR Callbacks_112034*	1/2/25 14:23	1/2/25 14:23	0:00:08
11	GP Phone_112034*	1/2/25 14:23	1/2/25 14:24	0:00:42
12	GP ACW_112034*	1/2/25 14:24	1/2/25 14:24	0:00:12
Sheet3

Cell Formulas
Range	Formula
F2:I2	F2	=FILTER(A2:D12,A2:A12="IDLE")
Dynamic array formulas.

 

[robatthe2A]

I'm so sorry for the delay- stepped away for dinner- back now. So, it looks like that formula when entered and adjusted to my sheet pulls all idle. Is there a way to pull just the idle that is within Available's start and stop time?