Formula Help with overlapping times

Y

yessir

Board Regular
Joined
Jun 7, 2019
Messages
103
Office Version
  1. 2021
Platform
  1. MacOS
I am trying to find a formula that would help me find how many cars overlap and are in the lot at the same time but only when column D is False. I would then want to count how many cars are in the lot at the same time. The start times are located in B2:B7 and end times are in C2:C7. I've tried using a SUMPRODUCT function but was wondering if there was an easier way to go about this and include the function to count the number of cars that overlap.
A B C D
[TABLE="width: 567"]
<tbody>[TR]
[TD]Arvl Lot Location[/TD]
[TD] Arvl Time[/TD]
[TD] Dept Time[/TD]
[TD]Is Overnight[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]15:25[/TD]
[TD]16:10[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]21:10[/TD]
[TD]22:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]8:21[/TD]
[TD]9:19[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]11:08[/TD]
[TD]11:45[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]14:29[/TD]
[TD]15:15[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]19:55[/TD]
[TD]21:20[/TD]
[TD]FALSE[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Sort by date Sort by votes
yessir said:
I want to SUM (count) the TRUE statements in E but only when the valu along the same row in column D is FALSE. I understand that in the table abv Column D is all false however for some of my other data there are some True statements in Column D.

Sorry for the confusion, appreciate your help.
Click to expand...

The condition in red is superfluous because whenever D is TRUE then E is FALSE (see formula in post 31)

This should work if the values in column E are logical TRUE, not text "TRUE"
F2 copied down
=IF(A2<>A3,SUMPRODUCT(--(A$2:A2=A2),--E$2:E2),"")

M.
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Marcelo Branco said:
The condition in red is superfluous because whenever D is TRUE then E is FALSE (see formula in post 31)

This should work if the values in column E are logical TRUE, not text "TRUE"
F2 copied down
=IF(A2<>A3,SUMPRODUCT(--(A$2:A2=A2),--E$2:E2),"")

M.
Click to expand...

I was able to figure out the previous problem myself. Thank you for your help. But now I do have another question for you what formula can I use if I want to Sum the total number of TRUE Statements in Coloumn B but only when Coloumn A is the same. And then when Coloumn A changes to another term I want to start from zero and count only the TRUE values in Coloumn B

[TABLE="width: 96"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Outbound Arvl Sta[/TD]
[TD="width: 64, bgcolor: transparent"]T/F Ovlp[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxx[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyy[/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

For example, total number of TRUE Statements in Column B when Column A is xxx is 2. Then When xxx changes to yyy i want the formula to start again from zero and count the number of TRUE statements when the rows in Column A are yyy. It would be ideal if the total was displayed at the bottom of column C when its the last row being counted. (similar to the formula we had done eariler.)
 
Last edited by a moderator:
Upvote 0
=IF(A2=A3,"",COUNTIFS(A:A,A2,B:B,TRUE))



[TABLE="width: 238"]
<tbody>[TR]
[TD]Outbound Arvl Sta[/TD]
[TD]T/F Ovlp[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yyy[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Upvote 0
Marcelo Branco said:
Try this

E2 copied down
=IF(D2=FALSE,SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A2)),--(A$2:A$400=A2),--(A$2:A$400<>""),--(D$2:D$400=FALSE),1-((B2>C$2:C$400)+(B$2:B$400>C2))),0)

M.
Click to expand...

Hello, If you remeber back in post #18 & #19 when we disscussed why the formula was returning negative values and the above formula was posted as a solution. I still seem to be getting negative values returned when the new formula was incorporated into my sheet.
 
Upvote 0
yessir said:
Hello, If you remeber back in post #18 & #19 when we disscussed why the formula was returning negative values and the above formula was posted as a solution. I still seem to be getting negative values returned when the new formula was incorporated into my sheet.
Click to expand...

The solution suggested by Twollaston in post 43 should work.

M.
 
Upvote 0
Marcelo Branco said:
The solution suggested by Twollaston in post 43 should work.

M.
Click to expand...

His post in #43 was disscussing how to Count TRUE or FALSE statements. The numbers i'm having trouble with were when we were first discussing counting the number of overlapping cars.
 
Upvote 0
Are you referring to this data setup and formula?

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Arvl Place​
[/TD]
[TD]
Arvl Time​
[/TD]
[TD]
Dept Time​
[/TD]
[TD]
Is Overnight​
[/TD]
[TD]
Num of Overlaps​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
zzz​
[/TD]
[TD]
21:47​
[/TD]
[TD]
08:43​
[/TD]
[TD]
TRUE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
zzz​
[/TD]
[TD]
19:35​
[/TD]
[TD]
20:36​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
zzz​
[/TD]
[TD]
11:01​
[/TD]
[TD]
11:56​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
zzz​
[/TD]
[TD]
14:29​
[/TD]
[TD]
15:24​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
zzz​
[/TD]
[TD]
17:15​
[/TD]
[TD]
18:04​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
zzz​
[/TD]
[TD]
21:00​
[/TD]
[TD]
22:14​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
zzz​
[/TD]
[TD]
12:35​
[/TD]
[TD]
13:20​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
zzz​
[/TD]
[TD]
15:23​
[/TD]
[TD]
16:10​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
8​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
zzz​
[/TD]
[TD]
06:44​
[/TD]
[TD]
07:34​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
zzz​
[/TD]
[TD]
13:30​
[/TD]
[TD]
14:15​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
zzz​
[/TD]
[TD]
19:00​
[/TD]
[TD]
20:00​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD]
zzz​
[/TD]
[TD]
16:17​
[/TD]
[TD]
16:57​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD]
zzz​
[/TD]
[TD]
07:38​
[/TD]
[TD]
08:45​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD]
zzz​
[/TD]
[TD]
11:36​
[/TD]
[TD]
12:34​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD]
zzz​
[/TD]
[TD]
19:11​
[/TD]
[TD]
20:49​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD]
zzz​
[/TD]
[TD]
09:17​
[/TD]
[TD]
10:19​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD]
zzz​
[/TD]
[TD]
16:04​
[/TD]
[TD]
16:59​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD]
zzz​
[/TD]
[TD]
17:41​
[/TD]
[TD]
18:52​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD]
zzz​
[/TD]
[TD]
06:11​
[/TD]
[TD]
07:09​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD]
zzz​
[/TD]
[TD]
14:50​
[/TD]
[TD]
15:40​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD]
zzz​
[/TD]
[TD]
14:47​
[/TD]
[TD]
15:42​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD]
zzz​
[/TD]
[TD]
20:50​
[/TD]
[TD]
22:25​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD]
zzz​
[/TD]
[TD]
13:49​
[/TD]
[TD]
14:34​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD]
zzz​
[/TD]
[TD]
16:43​
[/TD]
[TD]
17:29​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
26​
[/TD]
[TD]
zzz​
[/TD]
[TD]
19:39​
[/TD]
[TD]
20:26​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
27​
[/TD]
[TD]
zzz​
[/TD]
[TD]
11:59​
[/TD]
[TD]
13:00​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
28​
[/TD]
[TD]
zzz​
[/TD]
[TD]
09:00​
[/TD]
[TD]
09:40​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
29​
[/TD]
[TD]
zzz​
[/TD]
[TD]
11:30​
[/TD]
[TD]
12:50​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD]
zzz​
[/TD]
[TD]
08:57​
[/TD]
[TD]
09:37​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
31​
[/TD]
[TD]
zzz​
[/TD]
[TD]
22:25​
[/TD]
[TD]
06:30​
[/TD]
[TD]
TRUE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
32​
[/TD]
[TD]
zzz​
[/TD]
[TD]
19:17​
[/TD]
[TD]
20:49​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
33​
[/TD]
[TD]
zzz​
[/TD]
[TD]
20:44​
[/TD]
[TD]
21:46​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
34​
[/TD]
[TD]
zzz​
[/TD]
[TD]
15:54​
[/TD]
[TD]
17:08​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
35​
[/TD]
[TD]
zzz​
[/TD]
[TD]
14:50​
[/TD]
[TD]
15:52​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
36​
[/TD]
[TD]
zzz​
[/TD]
[TD]
14:20​
[/TD]
[TD]
15:30​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
37​
[/TD]
[TD]
zzz​
[/TD]
[TD]
18:13​
[/TD]
[TD]
19:00​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
38​
[/TD]
[TD]
zzz​
[/TD]
[TD]
15:44​
[/TD]
[TD]
17:00​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
7​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
39​
[/TD]
[TD]
zzz​
[/TD]
[TD]
16:59​
[/TD]
[TD]
18:15​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
7​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
40​
[/TD]
[TD]
zzz​
[/TD]
[TD]
11:23​
[/TD]
[TD]
12:30​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
41​
[/TD]
[TD]
zzz​
[/TD]
[TD]
10:00​
[/TD]
[TD]
11:05​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
42​
[/TD]
[TD]
zzz​
[/TD]
[TD]
18:53​
[/TD]
[TD]
20:40​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
43​
[/TD]
[TD]
zzz​
[/TD]
[TD]
19:53​
[/TD]
[TD]
09:00​
[/TD]
[TD]
TRUE​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
44​
[/TD]
[TD]
zzz​
[/TD]
[TD]
08:45​
[/TD]
[TD]
10:10​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
45​
[/TD]
[TD]
zzz​
[/TD]
[TD]
12:10​
[/TD]
[TD]
13:25​
[/TD]
[TD]
FALSE​
[/TD]
[TD]
5​
[/TD]
[/TR]
</tbody>[/TABLE]


E2 copied down
=IF(D2=FALSE,SUMPRODUCT(--(ROW(A$2:A$400)<>ROW(A2)),--(A$2:A$400=A2),--(A$2:A$400<>""),--(D$2:D$400=FALSE),1-((B2>C$2:C$400)+(B$2:B$400>C2))),0)

If so, what exactly do you want?

M.
 
Last edited by a moderator:
Upvote 0
Marcelo Branco said:
If so, what exactly do you want?

M.
Click to expand...

In some cases the car stays in the lot until 1:15AM but the "Is overnight" (column D) is False. When using the above formula it returns a negative number.

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 98, bgcolor: transparent"]A[/TD]
[TD="width: 112, bgcolor: transparent"]B[/TD]
[TD="width: 136, bgcolor: transparent"]C[/TD]
[TD="width: 128, bgcolor: transparent"]D[/TD]
[TD="width: 122, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]20:09[/TD]
[TD="bgcolor: transparent"]6:33[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]11:05[/TD]
[TD="bgcolor: transparent"]12:03[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]9:26[/TD]
[TD="bgcolor: transparent"]10:16[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]23:52[/TD]
[TD="bgcolor: transparent"]1:15[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]-21[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]15:13[/TD]
[TD="bgcolor: transparent"]16:03[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]22:02[/TD]
[TD="bgcolor: transparent"]5:00[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]18:26[/TD]
[TD="bgcolor: transparent"]6:45[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]20:06[/TD]
[TD="bgcolor: transparent"]6:00[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]15:51[/TD]
[TD="bgcolor: transparent"]16:41[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]11:52[/TD]
[TD="bgcolor: transparent"]12:48[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]8:03[/TD]
[TD="bgcolor: transparent"]8:53[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]-1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]13:30[/TD]
[TD="bgcolor: transparent"]14:20[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]17:52[/TD]
[TD="bgcolor: transparent"]18:42[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]10:02[/TD]
[TD="bgcolor: transparent"]10:47[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]18:28[/TD]
[TD="bgcolor: transparent"]19:40[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]22:55[/TD]
[TD="bgcolor: transparent"]6:00[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]13:39[/TD]
[TD="bgcolor: transparent"]14:21[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]19:03[/TD]
[TD="bgcolor: transparent"]19:45[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]9:31[/TD]
[TD="bgcolor: transparent"]10:21[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]22:28[/TD]
[TD="bgcolor: transparent"]23:24[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]-1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]22:27[/TD]
[TD="bgcolor: transparent"]12:28[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]13:33[/TD]
[TD="bgcolor: transparent"]14:18[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]15:59[/TD]
[TD="bgcolor: transparent"]17:20[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]11:48[/TD]
[TD="bgcolor: transparent"]12:28[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]10:47[/TD]
[TD="bgcolor: transparent"]11:37[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]13:59[/TD]
[TD="bgcolor: transparent"]15:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]16:38[/TD]
[TD="bgcolor: transparent"]18:00[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]22:04[/TD]
[TD="bgcolor: transparent"]7:55[/TD]
[TD="bgcolor: transparent"]TRUE[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jjj[/TD]
[TD="bgcolor: transparent"]11:13[/TD]
[TD="bgcolor: transparent"]11:58[/TD]
[TD="bgcolor: transparent"]FALSE[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
J
  • Question Question
How to Find Time Overlaps
2
Replies
10
Views
767
JK_NMS
J
S
  • Question Question
Using VBA to Flag and Calculate Overlapping Shift Times
Replies
0
Views
486
Snort
S
ibmy
  • Question Question
How to structure the formula properly?
Replies
2
Views
247
ibmy
ibmy
M
  • Solved
How to merge files?
Replies
2
Views
389
mrdigitalis
M

Forum statistics

Threads
1,224,825
Messages
6,181,189
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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