VBA to analyze specific days of the week within date range?

Uncle! I give in and am calling for the gurus. I am having trouble trying to derive the true start and end dates.
I am provided a spreadsheet with all the classes with the days of the week and the start/end date ranges. Each class has a unique CRN number and sometimes classes can take up multiple rows. I need to determine the true start date/end date of each class based on the day of the week listed in the Class_Days column. The days of the week are defined below:
M=Monday
T=Tuesday
W=Wednesday
R=Thursday
F=Friday
S=Saturday
U=Sunday

For example CRN# 20525 has a start date of Monday January 11 and a class day “T”. Therefore the true start date should be Tuesday January 12. The end date is Friday March 18 with a class day of “T”. Therefore the true end date is Tuesday March 15.

Another example CRN# 20570 is actually correct. The “FSU” class days match up perfectly to the date range of Friday February 5-Sunday February 21. So the true start/end columns are just blank.

I am including a small sample workbook with an original data tab and a result tab that also shows the examples above.
This seems so simple yet I have created several “work arounds” to try to get this data automatically. I’ve not ever really got it to look like the result page. As you can see in the example, I bolded the dates that are correct.
Any ideas on how to accomplish this? My sincerest thanks for your help!

PS. If you want a laugh I’ll share my workaround workbook too. Oh the shame!

Wow! I haven't seen "UDF's" before and that is awesome! That is so powerful! Not that you really care---but I am totally
impressed over here.

I followed your instructions and man--its so close! I'll attach the sample with your creation so you can see.

Looks like 20792 is the trouble maker here. This class has two rows--which throws off the function. The true start date really is Monday January 11 since in row 6 it has a "M" class day. But since row 7 has class days "TR", the function interprets
the start date as Tuesday January 12.

What do you think? I wouldn't mind if there were dates in all four columns and I had to conditionally format to spot the differences. I appreciate your help so much! Thank you!!

Hi again,

The grouped courses are not always separated by a blank row. I did that manually to show distinction between the duplicate courses.


"Is the start date always the top row column D and is the end date always the last row column E?"

-Not at all. I wish! For multi row courses they are only in order by CRN. The rest is essentially a data dump. I could do a custom sort by CRN, then by start date, then by end date if that helps.

"Do you want to consider all class days from all rows for both start and end e.g. 20792 has M on one row and TR in another row so its start and end could be on MTR?"

--I think each row would have to be evaluated separately. Originally I combined all the days also (to cut down on row count and better evaluation) but then I realized they were in separate rows for a reason. The most common reason is that the class meets at a different time. 20792 for example, that class on Monday might meet at a different time so that's why its separate from the TR part of the class.

"Do you want the true start and true end results on the top row of a course?"
No that's ok. As long as I can look through and quickly see the dates that are different-I'm not picky for sure!

Extra info FYI
This data is a result of a SQL query from the college's database so I have to bend it to my will! The workbook i attached was just a sample. The typical row count for a query is about 3000 rows so this will help me tremendously! I'll be able to quickly see when I need to report true dates.

Thank you again for your help!!

Hi there,

My apologies, I guess I had put the dates for 20792 on the top row (#6). I don't need the final dates to be on the top row for multiple CRN's. It should look like the rows below:


20792 NUR 2241L M Monday, January 11, 16 Wednesday, April 6, 16 Monday, January 11, 16
20792 NUR 2241L TR Monday, January 11, 16 Wednesday, April 6, 16 Tuesday, April 5, 16

Thanks!

Oh my goodness i am so stupid! I'm sorry i have been working with this data all day and I'm a little fried!

Disregard my entire last message.

I was looking at the wrong tab!

OK. Back to earth. THE UDFs in your example are working how you designed them. The ultimate goal is to have column F & G show the true start/end date.
Just an idea...Perhaps all the dates in column F & G should populate and then I would evaluate the earliest start date and the latest end date for each CRN?

I am sorry for all this trouble. Thanks again for your patience and not giving up!

Awesome! Thank you again! This will save me hours every week looking at dates for 3000 rows!! You rock!! Have a wonderful weekend
-Hope