Monthly work schedule, disappearing assignments

[RadMan]

Asa,

Except for the limitation, that works like a charm. I appreciate you showing me the formulas and the process. This will come in handy later. I'm putting all the months in one file under different sheets.


Is there any way to override my weekend color for specific cells after I've changed the month? I want to keep the assignments as they are but change the color to whatever I want.


Mark

 

[asa]

Hi Mark,

Hmm...

Well, conditional formats override manual formats. So, no. Not without changing the way we format the weekend colors.


I've avoided suggesting macros thus far for your requests because they are a more advanced topic and I'm particularly interested in telling you about ways to solve these and similar problems in a way that you will get the most out of it... plus, there have thus far been solutions that didn't require macros (although some would have used them).


Several solutions come to mind. One involving a macro/bit of VBA code, and the others without:

(1) Instead of using conditional formatting for the weekend colors, we could use a macro that formats those cells only when the month changes, allowing you to re-format those cells as you like.

(2) You could simply change the applies-to range for the weekend colors to just the day of month/day of week area, allowing you to format the assignments as desired.

(3) Instead of using a colored background, you could use a color pattern for the weekend days, or just stick with the dashed line I had put in. These won't interfere with manually applied background colors since they are a different format element. Your manual background colors will work, but the weekend formatting will also show up overlayed on top.

(4) For your manual format, you could use text colors/formatting instead.

(5) You could have a row in the spreadsheet with some kind of special value/flag to indicate you want to manually format that column. We could then modify the conditional format rule to avoid formatting columns that include that flag, enabling you to manually format as you wish. With this exact suggestion, there would be no weekend coloring in any cell in that column.


Are you interested in pursuing any of those options?


Asa

 

[RadMan]

Asa,

I like the idea of just changing the color of the text instead of the background for the weekends. However,the macro seems interesting.


Before that I've adjusted the number of rows i need for my employees and assignments and noted at the bottom of the spread sheet all the months of the year. Is there a way to adjust the data validation rule to include these sheets when considering the "10 days in a pay period maximum"


I didn't copy the sheets for all of the months yet. Just wanted to give you an idea of what i'm talking about.


Then maybe we could work that macro suggestion.


EAS.revised11.25.2011.xlsx


Thanks


Mark

 

[asa]

Hi, Mark!

Happy holidays & new year.

Revisiting the prior limitation regarding number of assignments per pay period:

Except for the limitation, that works like a charm. I appreciate you showing me the formulas and the process. This will come in handy later. I'm putting all the months in one file under different sheets.

The simplest workaround that comes to mind, without making deeper design changes to the workbook, would be to use an INDIRECT formula in the conditional format that would rely on the worksheets for each month being named in a calculable fashion ("Jan 2012" or "1-2012" or..) then, it could count assignments from both the prior and current month's sheet that are in a pay period overlapping the month break.


I have some pressing matters consuming my time this season, so I hope you have been able to get any needed assistance from others. If not, you may want to start new topics for some of the questions that remain.


When I have the time and energy I will check back with you. This has been a fun project and it can be further developed in various ways, depending on development time and energy on your and your helpers behalf, and your needs.


Asa

 

[stemelliott]

As asa seams to be trying his best to support you, and he's well busy, I thought I would try and pick up where he left off.

Conditional formatting. I cannot see which version of excel you are on, but assuming it's 2007 or better then you can have loads of conditional formats. They get processed in order, and some will over right others. However, there is nothing stopping you putting an AND statement into your conditional format e.g. AND(LEFT(TEXT(cell,"ddd"),1)<>"s",your other criteria).

 

[stemelliott]

So, =MOD(DATE(2012,6,1)-DATE(YEAR($A$1),MONTH($A$1),C$2),21)=0

could be

=AND(MOD(DATE(2012,6,1)-DATE(YEAR($A$1),MONTH($A$1),C$2),21)=0,LEFT(TEXT(C$2,"ddd"),1)<>"s")


or you could use:

=AND(COUNTIF(B$4:B$18,B4)=1,NOT(ISERROR(MATCH(B4,B$19:B$30,0))),LEFT(TEXT(C$2,"ddd"),1)<>"s")

 

[stemelliott]

Here's a little inspiration for how to deal with the cross over of days in weeks:

http://img.chandoo.org/d/2012-calendar-v1.xlsx