Hi ,
is it possible to have a formula in the third column to identify overlapping instances and ahve htem numbered in the order they occur ?
so if meeting 2 overlaps with meeting 3 ,
then meeting 2 would have 1 next to it ,
and meeting 3 would have 2 next to it
Any any other meetings that are not overlapping would have 0 next to them
also is it possible to calculate the gap between them , or perhaps even retrun the end date of the 2 nd meeting, basically as a suggested time to be used instead, to prevent overlap
is it possible to have a formula in the third column to identify overlapping instances and ahve htem numbered in the order they occur ?
so if meeting 2 overlaps with meeting 3 ,
then meeting 2 would have 1 next to it ,
and meeting 3 would have 2 next to it
Any any other meetings that are not overlapping would have 0 next to them
also is it possible to calculate the gap between them , or perhaps even retrun the end date of the 2 nd meeting, basically as a suggested time to be used instead, to prevent overlap
| Date and time overlap 2.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 5 | start date | end date | over lap | Over lap duration | ||
| 6 | 11/20/2021 10:00 | 11/20/2021 12:00 | 0 | 0:00 | ||
| 7 | 11/20/2021 12:00 | 11/20/2021 13:30 | 1 | 1:30 | ||
| 8 | 11/20/2021 11:00 | 11/20/2021 13:00 | 2 | 1:30 | ||
| 9 | 11/20/2021 13:00 | 11/20/2021 15:00 | 0 | 0:00 | ||
| 10 | 11/20/2021 15:00 | 11/20/2021 17:00 | 0 | 0:00 | ||
| 11 | 11/20/2021 17:00 | 11/20/2021 19:00 | 0 | 0:00 | ||
| 12 | 11/20/2021 19:00 | 11/20/2021 19:30 | 0 | 0:00 | ||
Sheet3 | ||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| C6:D12 | Expression | =$I$4=call | text | NO |
| C5:D5 | Expression | =$I$4=call | text | NO |
| A5:B12 | Expression | =$I$4=call | text | NO |
