Highlight cells of a training schedule if training start/end dates filled

anikipipo

New member
Joined
Nov 3, 2011
Messages
3
Reaction score
0
Points
0
Hi All,

I'm customizing a calendar template to use it as a training schedule.

I want days in the calendar to be highlighted in certain colors when training information (mostly start/end date & status) are filled in ("training list" tab) through conditional formatting or other solution.

I already have it working for Holidays & priority need to be kept for holidays over the training if 1 is scheduled at the same time.

Any idea to also showcase the customer, training name & instructor in the schedule would be awesome.

Thx in advance.

PS : sample attached
 

Attachments

  • Training schedule.xlsx
    31.3 KB · Views: 73

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there, and welcome to the forum!

I have a calendar that I use for planning our month ends that is quite similar to this. My suggestion here would be that your "List of trainings" actually be built on a day by day basis, rather than date range. From there, you could pull this into summary tables (like you have with holidays on the front page) for "confirmed" or "soon to be..."

The next step would be to set up a conditional formatting rule set that checks the date vs each table (using Stop If True in the cf rule to control the order)
 

anikipipo

New member
Joined
Nov 3, 2011
Messages
3
Reaction score
0
Points
0
Thank you Ken.
Sorry i was on a business trip.

Let me try this & i'll let you know how it works.
By the way what if i have 2 trainings in the same date ? Do you think there could be a way to have a gradient ?
In parallel i guess there's no way to put customer & instructor name in the calendar.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi there,

Sure, you could have a gradient. Gradient fills are available, so it's just making a rule to count how many items are on each date.

With regards to putting in both customer and instructor, why not? Join the text in advance and use a line break:
=A1&CHAR(10)&B1

Set the cell holding that formula to wrap text and it will show on two lines. :)
 

anikipipo

New member
Joined
Nov 3, 2011
Messages
3
Reaction score
0
Points
0
Solved - Highlight cells of a training schedule if training start/end dates filled

Hi Ken,

I made some testing, it looks fine. Just need some work to fine-tune everything & make it clear.
Thx so much for your help.

Cheers.
 
Top