Conditional Formatting Based on Change of Date but never certain how many lines have same date

[abuchanan]

I am sure this is answered somewhere, but just difficult to find (plus I'm getting old and can't remember stuff!)

I have a data set that shows classes by dates. You never know how many classes will be on the same date. Sometimes it is just one class, sometimes 2, sometimes 3, etc. They may or may not have same or overlapping times (but start time is in a different field.). Basically this is a spreadsheet showing classes being offered by day and time.

What I want to do is sort on date and start time, but would like to color code all classes on same date with one color and then change the color when date change...
So may have data as follows:
Jan 01 6p
Jan 01 7p
Jan 04 3p
Jan 04 3p. (yes two classes can start at same time... just different instructors)
Jan 04 8p
Jan 07 5p
Jan 10 4p

So basically I want the colors to change when the DATE changes... row one and two should be same, row 3,4,5 should be same, row 6 by itself, row 7 by itself...
Like I said, don't think this is that big of a deal, used to be pretty good at excel, but been 3-4 years!

Even better would be is I could just make a vertical calendar and alternate colors based on date... (but again, I would have to have all dates same color).... Maybe just put in a blank line showing no classes on dates that have no data? But these things are just "nice to have". More important to make it easy to see when date changes.

As info, I'm trying to assign an assistant to each class, and want to look at all classes on a day so I can make assignment. I can then filter and see what individual assistant schedules look like.

Thanks in advance for any help. Have posted in age, but was always a great place to get some help from experts!

 

[Joe4]

It is not as easy as you might think.

But there is a way to have alternating colors for dates using a "helper" column.
Let's say that our dates are in column A, and there is a header in row 1, so the data starts on row 2.
Let's say our helper column in column B.

After sorting, if your date/time entries are valid date time entries, enter this in cell B2 and copy down for all rows:
=IF(ROW()=2,1,IF(INT(A2)=INT(A1),B1,B1+1))

If they are in Text format, in the manner you showed in your first post, change the formula to this:
=IF(ROW()=2,1,IF(LEFT(A2,6)=LEFT(A1,6),B1,B1+1))

Then, you can get up to Conditional Formatting formulas for your alternating colors.
So, select all rows from row 2 to the end, and use this Conditional Formatting formula:
=ISODD($B2)
and choose your desired color.

Then, selecting the same rows, enter a second CF formula, like this:
=ISEVEN($B2)
and choose your second desired color

Actually, you don't even really have to set up a second one if you want, as it will alternate between colored and not-colored.

 

[Peter_SSs]

Quite similar to Joe's idea in using a helper column and just using one colour to make the banding, though a second can be added, though I have interpreted that date and start time are in different columns:

but start time is in a different field

The helper column can be hidden once you have entered the zero in the top cell and the formula then copied down to the end of the data.
Then select from top left to bottom right of the data, excluding headings, (A2:C8 for my sample) and apply the very simple conditional formatting formula shown.

20 07 17.xlsm
	A	B	C	D	E
1	Date	Start Time	Class		0
2	Jan-01	6p	Class 1		1
3	Jan-01	7p	Class 2		1
4	Jan-04	3p	Class 3		0
5	Jan-04	3p	Class 4		0
6	Jan-04	8p	Class 5		0
7	Jan-07	5p	Class 6		1
8	Jan-10	4p	Class 7		0
Colour Bands

Cell Formulas
Range	Formula
E2:E8	E2	=IF(A2=A1,E1,1-E1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C8	Expression	=$E2	text	NO

So that there is no confusion about the exact layout of your data for the future, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[abuchanan]

Thanks Joe.

I understand what that is doing but it isn't exactly what I'm hunting. I think I need a switch in the helper column that is 0 or 1. It need to be set at to the same flag (0/1) of the previous row0 if the date of the row matches the date of previous row. I

 

[abuchanan]

Ignore that last post! Both of these solutions work great. I had done this a long time ago and used a solution like Peter explained. Had never used what Joe said, so glad to know both.

I've been off of the site for several years due to health (and just being lazy!). Peter, you are correct... I should have just posted some of the file! I'll remember next time. I just started doing a little consulting work with one of the local universities and I'm surprised that I've forgotten a lot of things! I guess age and laziness will do it to you!

Thanks to you both. I really appreciate the help!

 

[Peter_SSs]

Thanks to you both. I really appreciate the help!

You're welcome. Glad we could help. Thanks for letting us know.

What about doing this anyway, ready for next time?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)