I am unable to do a condition within a calendar

_dave

New member
Joined
May 11, 2016
Messages
21
Reaction score
0
Points
0
Excel Version(s)
2013
I have a calendar that I need to add 5 people (Tom,Dick, Harry, Mary and Jane lets say) to work later that week (Monday / Friday).
I cannot see how to set up a color fill condition for that week based on the name.

Do I have to create a condition for each name and week? Or is there an easy way to do this.

Thanks in advance for any suggestion or help.

_dave
 

Attachments

  • Extended Shift Calendar.xlsx
    21.5 KB · Views: 17
Two rules per person, one for the left side of the calendar and one for the right. The CF rule will use a formula that references the column with the names (left - A, right - K) looking for the person's name. YOu will apply the left rule to range C6:G56 and the right rule to L6:p56. Hope this helps you to get started!
 
Do I have to create a condition for each name and week?
I think that you need 5 rules for each row-range of the month eg L33:p33, L34:p34 etc.
30 rules per month.
For each person name you should rule and color format. (my opinion)
Or maybe VBA?
 
I think that you need 5 rules for each row-range of the month eg L33:p33, L34:p34 etc.
30 rules per month.
For each person name you should rule and color format. (my opinion)
Or maybe VBA?

No, you don't need that many rules!

This formula rule:

=$A6="Tom"

applied to this range:

=$C$6:$G$56

deals with all of Tom's rows on the left of the calendar.

You need another similar rule for Tom for the right side of the calendar, and then two rules for each other person (Dick, Harry, Mary and Jane). So that's 5 people in total, two rules per person = 10 rules.

I've created the first rule in the attachment.
 

Attachments

  • Extended Shift Calendar AliGW.xlsx
    21.7 KB · Views: 15
Last edited:
No, you don't need that many rules!
I understand what you mean.
But then you apply the CF on the dates that are not in that month?
 

Attachments

  • dave-navic.xlsx
    22.6 KB · Views: 10
I understand what you mean.
But then you apply the CF on the dates that are not in that month?

I don't understand your problem. The OP asked to apply a colour to the row (Monday to Friday). That is what my formula does.
 
Highlight row range with criteria

I don't understand your problem. The OP asked to apply a colour to the row (Monday to Friday). That is what my formula does.
No problem, your formula is ok, there are only two differences.
If you look a new attached file, you will understand what I mean. regards
 

Attachments

  • dave-navic2.xlsx
    23.4 KB · Views: 9
Let's just wait and see what the OP says. Either I have underestimated the requirement or you have overestimated it! ;)
 
Navic, Ali is right, I only need the work week (Monday-Friday). I have updated the calendar to more reflex the "person" actual work week (using Ali version). Some workers will over lapped into the next month within the week (so they will carry over to the next month).

I keep trying to add in Ali CF for the others, but so far it keeps failing me. I'll keep trying till I get it (hopefully, otherwise I'll be back).

Thanks guys for your help on this. I need to have it ready by Monday ( again hopefully). Have a good weekend.

_dave
 

Attachments

  • Extended Shift Calendar AliGW_b.xlsx
    21.6 KB · Views: 5
Glad we got you part way there, Dave. Post again tomorrow if you need help refining it before Monday. You gave yourself a pretty short deadline!!!
 
Yea, it the way managements works. They wanted it when.....

And I know (believe anyway) this is not a one time thing.

Thanks again for the help.
 
Just want to say "Thanks" for the support both AliGW and navic provided.

I have attached the final version for anyone to review or use.

:clap2:
 

Attachments

  • FINAL Calendar from AliGW.xlsx
    22.2 KB · Views: 7
:help:
I just found out that the calendar is broken (dang!!). When I added the workers name in a new column to the left, it broke the way the calendar works. I have enclosed the original Microsoft version for comparison.

I don't see how I can fix it now (for sure), I don't understand by adding the columns outside the area broke the calculation for the dates. Only the left side is effected, the right side appears to be OK for now.

The calendar (original) show SMTWTFS, while my version shows MTWTFSS. The more columns you add to the left, the more the days shift.

Can anyone help me on fixing this new problem? If so, really many thanks in advance.

_dave
 

Attachments

  • One year calendar_ORGINAL.xlsx
    20.7 KB · Views: 7
The calendar (original) show SMTWTFS, while my version shows MTWTFSS.
There are hidden data.
Sunday for SMTWTFS,
Monday for MTWTFSS

If you are adding columns, pay attention to the address in the formula
 

Attachments

  • One year calendar_ORGINAL.xlsx
    21.7 KB · Views: 2
You'll need to check the formulae used to generate the calendar itself and any associated UDFs where cell references have shifted. Unfortunately the file won't open in Excel on this iPad - sorry!
 
Back
Top