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

abuchanan

New Member
Joined
Jan 25, 2014
Messages
32
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,909
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,444
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1DateStart TimeClass0
2Jan-016pClass 11
3Jan-017pClass 21
4Jan-043pClass 30
5Jan-043pClass 40
6Jan-048pClass 50
7Jan-075pClass 61
8Jan-104pClass 70
Colour Bands
Cell Formulas
RangeFormula
E2:E8E2=IF(A2=A1,E1,1-E1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C8Expression=$E2textNO



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

New Member
Joined
Jan 25, 2014
Messages
32
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

New Member
Joined
Jan 25, 2014
Messages
32
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,444
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,259
Messages
5,635,126
Members
416,842
Latest member
Ateen4ever

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top