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

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 he
(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

"eijaz" wrote in message
...
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 he
(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

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

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



In article ,
"eijaz" wrote:

snip

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

"eijaz" wrote in message
...
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 he
(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

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




"J.E. McGimpsey" wrote in message
...
If I understand you correctly, you can use this Conditional
Formatting:

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



In article ,
"eijaz" wrote:

snip

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

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.



In article ,
"eijaz" wrote:

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!

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