auto-formatting weekends with colours & automatically inputing holiday in autoformatted cells

E

eijaz

hi all,

this is my first post to this group.

i am working in a mortgage loan servicing call-center as a team leader &
handle agents under me.

i am trying to mk a workbook which gives the shift schedule for a my team
for a particular month (my team - 1st sheet) & (other team leaders - 2nd,
3rd, 4th, 5th sheets). it looks somewhat like this:
what i do is mk a copy of this workbook for every month viz.,
shift_Schedule_Sept03.xls, shift_Schedule_Oct03.xls,
shift_Schedule_Nov03.xls etc so that it can come in handy every month. then
all i have to do is change the date here:
(A1) (B1) (C1)
(D1) (E1) (F1)
10/01/2003
(A2) (B2) (C2)
(D2) (E2) (F2)
============================================================================
====
(A3)*date (B3)weekday (C3)agents-names
(D3)present (E3)absent (F3)paid leave
============================================================================
====

(A4) =$A$1 =text(weekday(a4),"dddd") Rhys pereira
data.. data.. data..
(A5) =A4+1 =text(weekday(a5),"dddd") Eijaz Sheikh
data.. data.. data..
(A6) =A5+1 =text(weekday(a6),"dddd") Monisha Almeida
data.. data.. data..

* This would gv me the dates till 10/31/2003.

it would look like:
10/01/2003 Wednesday
10/02/2003 Thursday
10/03/2003 Friday
10/04/2003 Saturday
10/05/2003 Sunday

Now if my teams weekend holidays are Saturday & Sunday, then using Format
menu, Conditional formatting:

if selecting the all the cells from column B to Column F, i give a
conditional formatting to all cells like

if formula is =$B4="Saturday" then paste cells with 'green' color
&
if formula is =$B4="Sunday" then paste cells with 'green' color again,

i get all the weekend rows filled horizontally with green color.

Now my question is, what if i want the green row cells to also reflect a
text in them like: "OFF" or "HOLIDAY", then what do i need to do?????
bcos if the rows are not weekend rows, then i may be have to enter data in
them.

as you may have guessed by now, the weekend rows are automatically
highlighted in 'green' color bcos of conditional formatting....., but i also
want text like the above to be automatically inputed in the cells, so that
if i change the above date i.e, 10/01/2003, then dates below would change
and automatically the weekdays would change, so would the weekends rows be
highlighted automatically with green color & then also show text as "OFF" or
"HOLIDAY".

CAN ANYBODY HELP?? PLS REPLY SOON.

THANKS & REGARDS,

EIJAZ
 
E

eijaz

hi all,

this is my first post to this group.

i am working in a mortgage loan servicing call-center as a team leader &
handle agents under me.

i am trying to mk a workbook which gives the shift schedule for a my team
for a particular month (my team - 1st sheet) & (other team leaders - 2nd,
3rd, 4th, 5th sheets). it looks somewhat like this:
what i do is mk a copy of this workbook for every month viz.,
shift_Schedule_Sept03.xls, shift_Schedule_Oct03.xls,
shift_Schedule_Nov03.xls etc so that it can come in handy every month. then
all i have to do is change the date here:
(A1) (B1) (C1)
(D1) (E1) (F1)
10/01/2003
(A2) (B2) (C2)
(D2) (E2) (F2)
============================================================================
(A3)*date (B3)weekday (C3)agents-names
(D3)present (E3)absent (F3)paid leave
============================================================================

(A4) =$A$1 =text(weekday(a4),"dddd") Rhys pereira
data.. data.. data..
(A5) =A4+1 =text(weekday(a5),"dddd") Eijaz Sheikh
data.. data.. data..
(A6) =A5+1 =text(weekday(a6),"dddd") Monisha Almeida
data.. data.. data..

* This would gv me the dates till 10/31/2003.

it would look like:
10/01/2003 Wednesday
10/02/2003 Thursday
10/03/2003 Friday
10/04/2003 Saturday
10/05/2003 Sunday

Now if my teams weekend holidays are Saturday & Sunday, then using Format
menu, Conditional formatting:

if selecting the all the cells from column B to Column F, i give a
conditional formatting to all cells like

if formula is =$B4="Saturday" then paste cells with 'green' color
&
if formula is =$B4="Sunday" then paste cells with 'green' color again,

i get all the weekend rows filled horizontally with green color.

Now my question is, what if i want the green row cells to also reflect a
text in them like: "OFF" or "HOLIDAY", then what do i need to do?????
bcos if the rows are not weekend rows, then i may be have to enter data in
them.

as you may have guessed by now, the weekend rows are automatically
highlighted in 'green' color bcos of conditional formatting....., but i also
want text like the above to be automatically inputed in the cells, so that
if i change the above date i.e, 10/01/2003, then dates below would change
and automatically the weekdays would change, so would the weekends rows be
highlighted automatically with green color & then also show text as "OFF" or
"HOLIDAY".

CAN ANYBODY HELP?? PLS REPLY SOON.

THANKS & REGARDS,

EIJAZ
 
J

J.E. McGimpsey

If I understand you correctly, you can use this Conditional
Formatting:

CF1: =OR(WEEKDAY($A1,3)>=5, $B1="OFF", $B1="HOLIDAY")
 
E

eijaz

hi all,

this is my first post to this group.

i am working in a mortgage loan servicing call-center as a team leader &
handle agents under me.

i am trying to mk a workbook which gives the shift schedule for a my team
for a particular month (my team - 1st sheet) & (other team leaders - 2nd,
3rd, 4th, 5th sheets). it looks somewhat like this:

what i do is mk a copy of this workbook for every month viz.,
shift_Schedule_Sept03.xls, shift_Schedule_Oct03.xls,
shift_Schedule_Nov03.xls etc so that it can come in handy every month. then
all i have to do is change the date here:
(A1) (B1) (C1)
(D1) (E1) (F1)
10/01/2003
(A2) (B2) (C2)
(D2) (E2) (F2)
============================================================================
(A3)*date (B3)weekday (C3)agents-names
(D3)present (E3)absent (F3)paid leave
============================================================================

(A4) =$A$1 =text(weekday(a4),"dddd") Rhys pereira
data.. data.. data..
(A5) =A4+1 =text(weekday(a5),"dddd") Eijaz Sheikh
data.. data.. data..
(A6) =A5+1 =text(weekday(a6),"dddd") Monisha Almeida
data.. data.. data..

* This would gv me the dates till 10/31/2003.

it would look like:
10/01/2003 Wednesday
10/02/2003 Thursday
10/03/2003 Friday
10/04/2003 Saturday
10/05/2003 Sunday

Now if my teams weekend holidays are Saturday & Sunday, then using Format
menu, Conditional formatting:

if selecting the all the cells from column B to Column F, i give a
conditional formatting to all cells like

if formula is =$B4="Saturday" then paste cells with 'green' color
&
if formula is =$B4="Sunday" then paste cells with 'green' color again,

i get all the weekend rows filled horizontally with green color.

Now my question is, what if i want the green row cells to also reflect a
text in them like: "OFF" or "HOLIDAY", then what do i need to do?????
bcos if the rows are not weekend rows, then i may be have to enter data in
them.

as you may have guessed by now, the weekend rows are automatically
highlighted in 'green' color bcos of conditional formatting....., but i also
want text like the above to be automatically inputed in the cells, so that
if i change the above date i.e, 10/01/2003, then dates below would change
and automatically the weekdays would change, so would the weekends rows be
highlighted automatically with green color & then also show text as "OFF" or
"HOLIDAY".

CAN ANYBODY HELP?? PLS REPLY SOON.

THANKS & REGARDS,

EIJAZ
 
E

eijaz

Hi,

thanks for replying!

getting the green color but not the text e.g. either "OFF" or "HOLIDAY"
which should be displayed automatically like the green color in the green
(weekend) rows i.e. Saturday & sunday. my main concern is getting the text
along with the green color!

rgds,

eijaz




If I understand you correctly, you can use this Conditional
Formatting:

CF1: =OR(WEEKDAY($A1,3)>=5, $B1="OFF", $B1="HOLIDAY")
 
J

J.E. McGimpsey

Formatting cannot insert text into a cell. For that you'll need to
either enter it directly or use a formula.

I can't tell from your post how XL should know if a particular date
is a holiday or off day - I assumed you manually entered OFF or
HOLIDAY.
 
E

eijaz

Thats true!

i want the text to show automatically in the adjacent cell(or cells) on the
right of the weekday cell, in the row highlighted with green color.
i knw that there is a formula, but cant remember!

pls help!

eijaz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Challenging Excel needs.... 0
Help with conditional formatting in Excel 2010 8
Conditional Formatting Across Multiple Cells 0
Conditional Formatting of Merged Cells 7
Conditional formatting with merged cells 3
Conditional Formatting - more than 4 cell colors 5
Excel 2007 conditional formatting bug? 2
Saving Excel 2007 conditional formatting in Excel 2003 format 0

Top