Conversation Re: Excel overlapping day of week and times in Excel
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810171#M38465
<P>Thank you so much for replying Ingeborg!</P><P>This is my first time on a forum so I really have no idea how this all works and greatly appreciate you responding. I am not sure when you say post a small sample file “here”, if you mean in this email or online so I am replying both to this email as well as online on the Microsoft site. I did not add a screenshot into my post, I copied the Excel file into the post. When I look at my post it shows as a table, not a screen shot. I will try again so you can work with the data easily.</P><P> </P><P>Here is the revised table. I would like the last column to indicate all the lectures and seminars that fall on the exact same day/time and also the ones where the times overlap on the same day. In my sample, where times overlap on the same day or the lecture/seminar is at the exact same time/day as another, these are marked as TRUE.</P><P>I don’t need it to be TRUE or FALSE. It can be indicated by a 1 or whatever, as long as the ones that conflict are indicated somehow.</P><P>LING 1F25 SEM 7 and LING 5P07 LEC both fall on Thursdays (R) from 1400-1700.</P><P>LING 1F25 LEC on Mondays from 1500-1700 overlaps with 5P05 and 5P08 which fall on Mondays from 1400-1700.</P><P> </P><TABLE><TBODY><TR><TD>Subj</TD><TD>Num</TD><TD>DU</TD><TD>CType</TD><TD>Sec</TD><TD>Days</TD><TD>Start Time</TD><TD>Finish Time</TD><TD>Conflict</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1500</TD><TD>1700</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 1</TD><TD>1</TD><TD>T</TD><TD>1600</TD><TD>1700</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 2</TD><TD>1</TD><TD>W</TD><TD>1500</TD><TD>1600</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 3</TD><TD>1</TD><TD>F</TD><TD>1600</TD><TD>1700</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 4</TD><TD>1</TD><TD>F</TD><TD>1000</TD><TD>1100</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 5</TD><TD>1</TD><TD>F</TD><TD>900</TD><TD>1000</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 6</TD><TD>1</TD><TD>W</TD><TD>1600</TD><TD>1700</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 7</TD><TD>1</TD><TD>R</TD><TD>1400</TD><TD>1500</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P01</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>R</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P02</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>T</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P03</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>T</TD><TD>1200</TD><TD>1500</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P04</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>W</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P05</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1400</TD><TD>1700</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P07</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>R</TD><TD>1400</TD><TD>1500</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P08</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1400</TD><TD>1700</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P85</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>W</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR></TBODY></TABLE><P> </P><P> </P>Mon, 19 Aug 2019 02:04:33 GMTArlene10702019-08-19T02:04:33ZExcel overlapping day of week and times
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810070#M38458
<P>Hi there,</P><P>Where I am currently working we have spreadsheets listing course lectures and seminars. As some courses/seminars cannot conflict with others, I need to find a way to identify which courses/seminars conflict. A sample spreadsheet is below.</P><P>The day and time cannot conflict with another day/time that is the same or has overlapping times.</P><P>I need a column at the end to identify all the exact and overlapping courses and seminars, not just by the times but by the day as well. I have been able to come up with a calculation to find overlapping times and another to find the same days/times however I am lost at how to create one calculation to do both. I am using Office 16.</P><P> </P><TABLE><TBODY><TR><TD>Subj</TD><TD>Num</TD><TD>DU</TD><TD>CType</TD><TD>Sec</TD><TD>Days</TD><TD>Time</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1500-1700</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 1</TD><TD>1</TD><TD>T</TD><TD>1600-1700</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 2</TD><TD>1</TD><TD>W</TD><TD>1500-1600</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 3</TD><TD>1</TD><TD>F</TD><TD>1600-1700</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 4</TD><TD>1</TD><TD>F</TD><TD>1000-1100</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 5</TD><TD>1</TD><TD>F</TD><TD>900 -1000</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 6</TD><TD>1</TD><TD>W</TD><TD>1600-1700</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 7</TD><TD>1</TD><TD>R</TD><TD>1400-1500</TD></TR><TR><TD>LING</TD><TD>5P01</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>R</TD><TD>1100-1400</TD></TR><TR><TD>LING</TD><TD>5P02</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>T</TD><TD>1100-1400</TD></TR><TR><TD>LING</TD><TD>5P03</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>T</TD><TD>1200-1500</TD></TR><TR><TD>LING</TD><TD>5P04</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>W</TD><TD>1100-1400</TD></TR><TR><TD>LING</TD><TD>5P05</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1400-1700</TD></TR><TR><TD>LING</TD><TD>5P07</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>R</TD><TD>1400-1500</TD></TR><TR><TD>LING</TD><TD>5P08</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1400-1700</TD></TR><TR><TD>LING</TD><TD>5P85</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>W</TD><TD>1100-1400</TD></TR></TBODY></TABLE><P>Any help would be greatly appreciated.</P><P>Arlene 1070</P><P> </P><P> </P><P> </P><P> </P>Sun, 18 Aug 2019 20:57:42 GMThttps://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810070#M38458Arlene10702019-08-18T20:57:42ZRe: Excel overlapping day of week and times
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810111#M38463
<P>Hello <LI-USER uid="394234"></LI-USER> ,</P>
<P> </P>
<P>in order to use the time values in formulas, you will need to split them into two different columns, one for start, one for finish. When you've done that, can you post a small sample file here and manually mock up the desired result? That would be a lot easier than having to re-type data from your screenshot for testing.</P>Sun, 18 Aug 2019 22:28:26 GMThttps://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810111#M38463Ingeborg Hawighorst2019-08-18T22:28:26ZRe: Excel overlapping day of week and times
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810171#M38465
<P>Thank you so much for replying Ingeborg!</P><P>This is my first time on a forum so I really have no idea how this all works and greatly appreciate you responding. I am not sure when you say post a small sample file “here”, if you mean in this email or online so I am replying both to this email as well as online on the Microsoft site. I did not add a screenshot into my post, I copied the Excel file into the post. When I look at my post it shows as a table, not a screen shot. I will try again so you can work with the data easily.</P><P> </P><P>Here is the revised table. I would like the last column to indicate all the lectures and seminars that fall on the exact same day/time and also the ones where the times overlap on the same day. In my sample, where times overlap on the same day or the lecture/seminar is at the exact same time/day as another, these are marked as TRUE.</P><P>I don’t need it to be TRUE or FALSE. It can be indicated by a 1 or whatever, as long as the ones that conflict are indicated somehow.</P><P>LING 1F25 SEM 7 and LING 5P07 LEC both fall on Thursdays (R) from 1400-1700.</P><P>LING 1F25 LEC on Mondays from 1500-1700 overlaps with 5P05 and 5P08 which fall on Mondays from 1400-1700.</P><P> </P><TABLE><TBODY><TR><TD>Subj</TD><TD>Num</TD><TD>DU</TD><TD>CType</TD><TD>Sec</TD><TD>Days</TD><TD>Start Time</TD><TD>Finish Time</TD><TD>Conflict</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1500</TD><TD>1700</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 1</TD><TD>1</TD><TD>T</TD><TD>1600</TD><TD>1700</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 2</TD><TD>1</TD><TD>W</TD><TD>1500</TD><TD>1600</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 3</TD><TD>1</TD><TD>F</TD><TD>1600</TD><TD>1700</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 4</TD><TD>1</TD><TD>F</TD><TD>1000</TD><TD>1100</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 5</TD><TD>1</TD><TD>F</TD><TD>900</TD><TD>1000</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 6</TD><TD>1</TD><TD>W</TD><TD>1600</TD><TD>1700</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>1F25</TD><TD>1</TD><TD>SEM 7</TD><TD>1</TD><TD>R</TD><TD>1400</TD><TD>1500</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P01</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>R</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P02</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>T</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P03</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>T</TD><TD>1200</TD><TD>1500</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P04</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>W</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR><TR><TD>LING</TD><TD>5P05</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1400</TD><TD>1700</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P07</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>R</TD><TD>1400</TD><TD>1500</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P08</TD><TD>3</TD><TD>LEC </TD><TD>1</TD><TD>M</TD><TD>1400</TD><TD>1700</TD><TD>TRUE</TD></TR><TR><TD>LING</TD><TD>5P85</TD><TD>2</TD><TD>LEC </TD><TD>1</TD><TD>W</TD><TD>1100</TD><TD>1400</TD><TD>FALSE</TD></TR></TBODY></TABLE><P> </P><P> </P>Mon, 19 Aug 2019 02:04:33 GMThttps://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810171#M38465Arlene10702019-08-19T02:04:33ZRe: Excel overlapping day of week and times
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810653#M38487
<P><LI-USER uid="394234"></LI-USER> </P>
<P>You may attach your file clicking on this icon at the left bottom of the Reply window</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 354px;"><img src="https://gxcuf89792.i.lithium.com/t5/image/serverpage/image-id/127447iE66CB63E35D9E92A/image-size/large?v=1.0&px=999" title="image.png" alt="image.png" /></span></P>
<P>To show the conflicts you may use</P>
<LI-CODE lang="markup">=(COUNTIFS($G$5:$G$20,$G5,$H$5:$H$20,"<"&$I5,$I$5:$I$20,">"&$H5)>1)</LI-CODE>
<P>and, if you are on Office365 subscription or on Excel 2019, use TEXTJOIN to show conflicting courses</P>
<LI-CODE lang="markup">=SUBSTITUTE(TEXTJOIN(";",TRUE,IF(($G$5:$G$20=$G5)*($H$5:$H$20<$I5)*($I$5:$I$20>$H5),$B$5:$B$20&" "&$C$5:$C$20&" "&$E$5:$E$20,"")),$B5&" "&$C5&" "&$E5,"")</LI-CODE>
<P>The only, you show in your example what 5P04 and 5P85 are not in conflict, but both of them are scheduled on Wed from 1100 to 1400. Same for 5P02 and 5P04.</P>
<P>Please check attached.</P>Mon, 19 Aug 2019 11:32:02 GMThttps://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810653#M38487Sergei Baklan2019-08-19T11:32:02ZRe: Excel overlapping day of week and times
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810890#M38496
<P>Thank you so much<LI-USER uid="521"></LI-USER> !</P><P>Your solution worked great to find the conflicts. I could not use the TEXTJOIN function because of my version of Excel but I am so happy to be able to identify the conflicts now.<BR />This will be extremely useful and save me a lot of time when going through countless courses.</P><P>Much appreciated!</P><P>Arlene</P><P> </P>Mon, 19 Aug 2019 13:56:21 GMThttps://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810890#M38496Arlene10702019-08-19T13:56:21ZRe: Excel overlapping day of week and times
https://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810977#M38501
<P><LI-USER uid="394234"></LI-USER> </P>
<P>Glad to help Arlene, you are welcome</P>Mon, 19 Aug 2019 14:39:02 GMThttps://techcommunity.microsoft.com/t5/excel/excel-overlapping-day-of-week-and-times/m-p/810977#M38501Sergei Baklan2019-08-19T14:39:02Z