Complex Conditional Formatting IF Statement with Time Calculation (Digital Sign In/Out Spreadsheet)

[PNeil]

I have been down the rabbit hole for the past few hours on this one... Here is my predicament. I have a spreadsheet the Signs In/Out individuals via barcode scanner (Macro underneath to population times in columns to the right of name). The users of this spreadsheet would like for the name of individuals who have been "Out" for >1hour to highlight. Since individuals leave and return many times during the day, it would only apply to values in the "Out" columns with a blank "In" column to the right. I have imbedded the current time to use in the calculations in cell a cell on the spreadsheet. So, my latest attempt looked like this:
="IF((E27=0), =SUM((D237-D27)*24>1), =SUM((D237-D27)*24<1)" ***I have tried ISBLANK(E27) as well, and E27=""
Essentially IF(("Cell is Blank"), (True if this calculation), (False if this calculation))

I know that doesn't do it as the =SUM calculations should be further True/False statements I believe.

Below is a snip of the spreadsheet as it is now.

Name		Out(1)	In(1)	Out(2)	In(2)
NAME		7:02​	7:28​	8:36​	​

So, looking to highlight the name IF F2 is blank, AND the Sum of the Current Time minus E2 is>1hr

I feel like I am missing something here, I have tried SUMIF, COUNTIF, IF... so many variations and recombinations to get to this point.

Any help would be great- I have conditional formatting to identify relationships between the Out/In time >1 hour, but unable to figure out how to add the condition of a blank cell AND current time.

Thank you

Paul

 

[awoohaw]

Try this:
It does require that the date also be part of the time stamp (this probably good for wrapping around mid-night.
I don't understand why your rows are 200 rows apart in each calculation though.

Mr Excel 8.xlsm
	A	B	C	D	E	F	G
1	Name		Out(1)	In(1)	Out(2)	In(2)
2	NAME		07:02:00	07:28:00	00:15:00		TRUE
3
Sheet3

Cell Formulas
Range	Formula
G2	G2	=AND(ISBLANK(F2),(NOW()-INT(E2)<=E2+(1/24)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F2	Expression	=AND(ISBLANK($F2),(NOW()-INT($E2)<=$E2+1))	text	NO

 

[awoohaw]

^^^ I have a calculation error above. will fix in a moment

 

[awoohaw]

Here it is again, hopefully this works for you:
Again, if you span midnight this will work for you as well if you put in the date component.

Mr Excel 8.xlsm
	A	B	C	D	E	F	G
1	Name		Out(1)	In(1)	Out(2)	In(2)	2023-02-22 02:28
2	NAME		07:02:00	07:28:00	01:15:00		TRUE
3
Sheet3

Cell Formulas
Range	Formula
G2	G2	=AND(ISBLANK($F2),($G$1-INT($E2))>=($E2-INT($E2)+(1/24)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F2	Expression	=AND(ISBLANK($F2),($G$1-INT($E2))>=($E2-INT($E2)+(1/24)))	text	NO

 

[PNeil]

Awoohaw- that's brilliant! Worked like a charm. So, now I have one set of cells that operate as desired- Is there a way to duplicate this formatting across the columns and rows? This sheet is used to track 100+ individuals with 3-20 In/Out's documented.

Below is a snip of what I have going on. Existing formatting in the sheet consists of highlighting cells where the difference in checking out/in is >1hr (for ease of review by staff). Also, in A2 there is the formatting you provided the expression for (targeting the time in the C2/D2 pair) and highlighting the name/vs the span of cells. Is there a way to expand the expression to the cells to the right?

I apologize for not uploading a mini-sheet but I am on a Gov work computer which blocks such things...

Thank you so much for assisting in this.

 

[awoohaw]

yes. I highlighted the row with the time 2 out audited.
But, the cond format formula can be adjusted to look at other time out columns and only highlight the time out being audited.
I'm not sure if it will also highlight the name, though. I'll post in a bit.

 

[awoohaw]

Okay, here it is. You can probably highlight the name by doing some kind of concatenation.
Notice that I had to put a check to see if the "Out" time is not blank before any condition of the In Time is done.
Even though it looks like I did 4 cond format formulas, I only did the cond formatting for cell C2, then copied its formatting everywhere else.

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name		Out(1)	In(1)	Out(2)	In(2)	Out(3)	In(3)	Out(4)	In(4)	2023-02-22 10:26
2	Name 1		08:25:00	10:01:52	09:26:33	10:40:25	09:11:25	10:22:10	08:11:54	09:25:37
3	Name 2		09:16:13	09:38:16	09:13:42	10:27:25	08:37:02		09:12:17	09:38:16
4	Name 3		09:50:08	09:54:38	08:35:29	10:14:15	09:56:23		08:09:31	09:15:27
5	Name 4		08:30:13	09:34:04	09:25:52	10:20:32			09:22:00
6	Name 5		09:30:33	09:42:18	08:40:52	11:04:38			10:04:58
7	Name 6		08:46:29	10:07:22	09:56:43				08:55:26
8	Name 7		09:36:49	09:01:47	09:00:25				08:55:05	09:34:07
9	Name 8		09:19:27	09:42:24	09:39:42				08:55:20	09:45:51
10	Name 9		09:49:41						09:59:41
11	Name 10		09:26:44						08:26:29
12	Name 11		09:19:35						09:27:25	09:13:48
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I12	Expression	=IF(ISBLANK(I2)=FALSE,AND(ISBLANK(J2),($K$1-INT(I2))>=(I2-INT(I2)+(1/24))))	text	NO
G2:G12	Expression	=IF(ISBLANK(G2)=FALSE,AND(ISBLANK(H2),($K$1-INT(G2))>=(G2-INT(G2)+(1/24))))	text	NO
E2:E12	Expression	=IF(ISBLANK(E2)=FALSE,AND(ISBLANK(F2),($K$1-INT(E2))>=(E2-INT(E2)+(1/24))))	text	NO
C2:C12	Expression	=IF(ISBLANK(C2)=FALSE,AND(ISBLANK(D2),($K$1-INT(C2))>=(C2-INT(C2)+(1/24))))	text	NO

 

[PNeil]

Okay, here it is. You can probably highlight the name by doing some kind of concatenation.
Notice that I had to put a check to see if the "Out" time is not blank before any condition of the In Time is done.
Even though it looks like I did 4 cond format formulas, I only did the cond formatting for cell C2, then copied its formatting everywhere else.

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name		Out(1)	In(1)	Out(2)	In(2)	Out(3)	In(3)	Out(4)	In(4)	2023-02-22 10:26
2	Name 1		08:25:00	10:01:52	09:26:33	10:40:25	09:11:25	10:22:10	08:11:54	09:25:37
3	Name 2		09:16:13	09:38:16	09:13:42	10:27:25	08:37:02		09:12:17	09:38:16
4	Name 3		09:50:08	09:54:38	08:35:29	10:14:15	09:56:23		08:09:31	09:15:27
5	Name 4		08:30:13	09:34:04	09:25:52	10:20:32			09:22:00
6	Name 5		09:30:33	09:42:18	08:40:52	11:04:38			10:04:58
7	Name 6		08:46:29	10:07:22	09:56:43				08:55:26
8	Name 7		09:36:49	09:01:47	09:00:25				08:55:05	09:34:07
9	Name 8		09:19:27	09:42:24	09:39:42				08:55:20	09:45:51
10	Name 9		09:49:41						09:59:41
11	Name 10		09:26:44						08:26:29
12	Name 11		09:19:35						09:27:25	09:13:48
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I12	Expression	=IF(ISBLANK(I2)=FALSE,AND(ISBLANK(J2),($K$1-INT(I2))>=(I2-INT(I2)+(1/24))))	text	NO
G2:G12	Expression	=IF(ISBLANK(G2)=FALSE,AND(ISBLANK(H2),($K$1-INT(G2))>=(G2-INT(G2)+(1/24))))	text	NO
E2:E12	Expression	=IF(ISBLANK(E2)=FALSE,AND(ISBLANK(F2),($K$1-INT(E2))>=(E2-INT(E2)+(1/24))))	text	NO
C2:C12	Expression	=IF(ISBLANK(C2)=FALSE,AND(ISBLANK(D2),($K$1-INT(C2))>=(C2-INT(C2)+(1/24))))	text	NO

Great, thank you- let me play with it.... Works really well, should be able to build Row2 and copy down for the other Rows.

Thank you soooo much! Super helpful!

 

[awoohaw]

Copying rows down will work, if you include copy formatting as part of the paste.
Happy to help!