Formula Help (times)

[yessir]

Is there a formula that can be ran on these values to then return something similar to the sample table provided below. The objective of this formula wold be to break down minute by minute is there are 0,1,2, or more times overlapping.

Times
12:45	13:01
12:55	13:05

<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"><colgroup><col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"><tbody>
</tbody>

From this ^ to this (below)

12:43	12:44	12:45	12:46	12:47	12:48	12:49	12:50	12:51	12:52	12:53	12:54	12:55	12:56	12:57	12:58	12:59	13:00	13:01	13:02
0	0	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	1

<colgroup><col width="64" style="width: 48pt;" span="20"> <tbody> </tbody>

 

[Marcelo Branco]

Try

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

1​

Times​

2​

12:45​

13:01​

3​

12:55​

13:05​

4​

5​

6​

12:43​

12:44​

12:45​

12:46​

12:47​

12:48​

12:49​

12:50​

12:51​

12:52​

12:53​

12:54​

12:55​

12:56​

12:57​

12:58​

12:59​

13:00​

13:01​

13:02​

7​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

1​

Formula in A7 copied across
=COUNTIFS($A$2:$A$3,"<="&A$6,$B$2:$B$3,">="&A$6)

M.

 

[yessir]

Try

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

1​

Times​

2​

12:45​

13:01​

3​

12:55​

13:05​

4​

5​

6​

12:43​

12:44​

12:45​

12:46​

12:47​

12:48​

12:49​

12:50​

12:51​

12:52​

12:53​

12:54​

12:55​

12:56​

12:57​

12:58​

12:59​

13:00​

13:01​

13:02​

7​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

1​

<tbody>
</tbody>

Formula in A7 copied across
=COUNTIFS($A$2:$A$3,"<="&A$6,$B$2:$B$3,">="&A$6)

M.

That formula did work. Is ther a way to edit it to where it produces something like this when there are more times and places? Examples below.

Times
OOO	12:45	13:01
OOO	12:55	13:05
PPP	12:57	13:02
PPP	12:44	12:54
PPP	12:47	13:00
TTT	12:53	13:02
TTT	12:55	13:00
TTT	12:57	13:00

<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <tbody> </tbody>

	12:43	12:44	12:45	12:46	12:47	12:48	12:49	12:50	12:51	12:52	12:53	12:54	12:55	12:56	12:57	12:58	12:59	13:00	13:01	13:02
OOO	0	0	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	1
PPP	0	1	1	1	2	2	2	2	2	2	2	2	1	1	2	2	2	2	1	1
TTT	0	0	0	0	0	0	0	0	0	0	1	1	2	2	3	3	3	3	2	2

<colgroup><col width="64" style="width: 48pt;" span="2"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="17"> <tbody> </tbody>

 

[Marcelo Branco]

All you have to do is to add a new condition

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

U​

1​

Times​

2​

OOO​

12:45​

13:01​

3​

OOO​

12:55​

13:05​

4​

PPP​

12:57​

13:02​

5​

PPP​

12:44​

12:54​

6​

PPP​

12:47​

13:00​

7​

TTT​

12:53​

13:02​

8​

TTT​

12:55​

13:00​

9​

TTT​

12:57​

13:00​

10​

11​

12​

13​

12:43​

12:44​

12:45​

12:46​

12:47​

12:48​

12:49​

12:50​

12:51​

12:52​

12:53​

12:54​

12:55​

12:56​

12:57​

12:58​

12:59​

13:00​

13:01​

13:02​

14​

OOO​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

1​

15​

PPP​

0​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

2​

1​

1​

2​

2​

2​

2​

1​

1​

16​

TTT​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

2​

2​

3​

3​

3​

3​

2​

1

<tbody>
</tbody>

Formula in B14 copied across and down
=COUNTIFS($A$2:$A$9,$A14,$B$2:$B$9,"<="&B$13,$C$2:$C$9,">="&B$13)

M.

 

[yessir]

All you have to do is to add a new condition

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

U​

1​

Times​

2​

OOO​

12:45​

13:01​

3​

OOO​

12:55​

13:05​

4​

PPP​

12:57​

13:02​

5​

PPP​

12:44​

12:54​

6​

PPP​

12:47​

13:00​

7​

TTT​

12:53​

13:02​

8​

TTT​

12:55​

13:00​

9​

TTT​

12:57​

13:00​

10​

11​

12​

13​

12:43​

12:44​

12:45​

12:46​

12:47​

12:48​

12:49​

12:50​

12:51​

12:52​

12:53​

12:54​

12:55​

12:56​

12:57​

12:58​

12:59​

13:00​

13:01​

13:02​

14​

OOO​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

1​

15​

PPP​

0​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

2​

1​

1​

2​

2​

2​

2​

1​

1​

16​

TTT​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

2​

2​

3​

3​

3​

3​

2​

1

<tbody>
</tbody>

Formula in B14 copied across and down
=COUNTIFS($A$2:$A$9,$A14,$B$2:$B$9,"<="&B$13,$C$2:$C$9,">="&B$13)

M.

When using the values and times in the below table the formula doesn't seem to be assigning value to when times overlap

OOO	23:49	5:31
OOO	17:49	18:20
OOO	11:03	11:35
OOO	14:39	15:04
OOO	22:04	6:15
OOO	16:01	16:28
OOO	9:12	9:37
PPP	16:08	16:33
PPP	10:01	10:26
PPP	11:57	12:22
TTT	23:35	8:00
TTT	20:03	6:37
TTT	15:49	16:34
TTT	11:59	12:44
TTT	9:48	10:34
TTT	13:57	14:44
TTT	23:59	6:00
TTT	12:37	13:15
TTT	20:52	21:22
TTT	22:58	7:00
TTT	12:57	13:27
TTT	8:12	8:45
TTT	23:53	8:45
TTT	11:46	12:30
TTT	18:11	18:50
TTT	14:29	15:07

<colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" span="3"> <tbody> </tbody>

 

[Marcelo Branco]

Yessir,

You are always changing the data and/or the requirements. It is difficult to help ...
Now in the new table there are rows where EndTime is less than StartTime.

See the answer I suggested in your other thread. You must adjust the values in column C.
https://www.mrexcel.com/forum/excel...verlapping-times-post5291821.html#post5291821

Try to find a solution yourself.

M.

 

[yessir]

Yessir,

You are always changing the data and/or the requirements. It is difficult to help ...
Now in the new table there are rows where EndTime is less than StartTime.

See the answer I suggested in your other thread. You must adjust the values in column C.
https://www.mrexcel.com/forum/excel...verlapping-times-post5291821.html#post5291821

Try to find a solution yourself.

M.

Thanks for your help, sorry for changing values..

 

[Marcelo Branco]

You are welcome.

M.