Cell colours according to date
 

I have formulas set up to have in cell a1 Mon 01/01/07 as a date, in the rest
of the cells I have =a1+1 and so on to fill in all dates for the year, what I
need is for cells that only contain Sat and Sun in the date to change colour
to yellow, is this possible, I have tried through CF but not formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a date, in the
rest
of the cells I have =a1+1 and so on to fill in all dates for the year,
what I
need is for cells that only contain Sat and Sun in the date to change
colour
to yellow, is this possible, I have tried through CF but not formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

I put this formula into CF still nothing getting done.

"Peter T" wrote:

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a date, in the
rest
of the cells I have =a1+1 and so on to fill in all dates for the year,
what I
need is for cells that only contain Sat and Sun in the date to change
colour
to yellow, is this possible, I have tried through CF but not formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

=WEEKDAY(A1,2)

for dates Mon-Fri returns 0-5
for dates Sat & Sun returns 6 & 7

=WEEKDAY(A1,2)5

Returns True for Dates Sat & Sun

Also returns True if cell is empty or 0 (if necessary the CF formula can be
amended to exclude empty or 0 cells)

See the Weekday function in Help

Does A1 contain a true date, not text. If you clear all formats the cell
should show a number.

In the CF dialog did you change 'Cell Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
I put this formula into CF still nothing getting done.

"Peter T" wrote:

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a date, in
the
rest
of the cells I have =a1+1 and so on to fill in all dates for the year,
what I
need is for cells that only contain Sat and Sun in the date to change
colour
to yellow, is this possible, I have tried through CF but not
formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

Still not working for me, A1 does contain a true date, when I enter the
formulas you gave me it doesn't return the dates in the cells as if mon
01-jan-07 and so on until the end of the year and I don't have an option to
change cell value to formula is in 2007

Regards MN

"Peter T" wrote:

=WEEKDAY(A1,2)

for dates Mon-Fri returns 0-5
for dates Sat & Sun returns 6 & 7

=WEEKDAY(A1,2)5

Returns True for Dates Sat & Sun

Also returns True if cell is empty or 0 (if necessary the CF formula can be
amended to exclude empty or 0 cells)

See the Weekday function in Help

Does A1 contain a true date, not text. If you clear all formats the cell
should show a number.

In the CF dialog did you change 'Cell Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
I put this formula into CF still nothing getting done.

"Peter T" wrote:

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a date, in
the
rest
of the cells I have =a1+1 and so on to fill in all dates for the year,
what I
need is for cells that only contain Sat and Sun in the date to change
colour
to yellow, is this possible, I have tried through CF but not
formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

What happens if you enter
=WEEKDAY(A1,2)
in any cell and A1 contains 1/1/07, a Monday
The formula should return 1
Now try some other dates incl Sat & Sun, eg Sun 21-Jan-07 should return 7

Now change the formula to =WEEKDAY(A1,2)5

try different dates
do you get the False for Mon-Fri and True for Sat & Sun

If it works in cells it should work in CF

I don't understand what you mean here
I don't have an option to
change cell value to formula is in 2007

I haven't asked you to change the cell from a value to a date, it doesn't
make any difference to the CF if the cell is a 'constant' date value or a
formula that returns a date. However in the CF dialog you MUST change 'Cell
Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
Still not working for me, A1 does contain a true date, when I enter the
formulas you gave me it doesn't return the dates in the cells as if mon
01-jan-07 and so on until the end of the year and I don't have an option
to
change cell value to formula is in 2007

Regards MN

"Peter T" wrote:

=WEEKDAY(A1,2)

for dates Mon-Fri returns 0-5
for dates Sat & Sun returns 6 & 7

=WEEKDAY(A1,2)5

Returns True for Dates Sat & Sun

Also returns True if cell is empty or 0 (if necessary the CF formula can
be
amended to exclude empty or 0 cells)

See the Weekday function in Help

Does A1 contain a true date, not text. If you clear all formats the cell
should show a number.

In the CF dialog did you change 'Cell Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
I put this formula into CF still nothing getting done.

"Peter T" wrote:

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a date,
in
the
rest
of the cells I have =a1+1 and so on to fill in all dates for the
year,
what I
need is for cells that only contain Sat and Sun in the date to
change
colour
to yellow, is this possible, I have tried through CF but not
formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

Hi Peter, I see what your getting at now, the cells do return numbers but
work back from 7 to 1 instead of 1 to 7 and this is after I format cell to
general, the only problem is that I need the cell to have the date in it not
a number or true or false, any other suggestions, I may sound a bit stupid.
MN

"Peter T" wrote:

What happens if you enter
=WEEKDAY(A1,2)
in any cell and A1 contains 1/1/07, a Monday
The formula should return 1
Now try some other dates incl Sat & Sun, eg Sun 21-Jan-07 should return 7

Now change the formula to =WEEKDAY(A1,2)5

try different dates
do you get the False for Mon-Fri and True for Sat & Sun

If it works in cells it should work in CF

I don't understand what you mean here
I don't have an option to
change cell value to formula is in 2007

I haven't asked you to change the cell from a value to a date, it doesn't
make any difference to the CF if the cell is a 'constant' date value or a
formula that returns a date. However in the CF dialog you MUST change 'Cell
Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
Still not working for me, A1 does contain a true date, when I enter the
formulas you gave me it doesn't return the dates in the cells as if mon
01-jan-07 and so on until the end of the year and I don't have an option
to
change cell value to formula is in 2007

Regards MN

"Peter T" wrote:

=WEEKDAY(A1,2)

for dates Mon-Fri returns 0-5
for dates Sat & Sun returns 6 & 7

=WEEKDAY(A1,2)5

Returns True for Dates Sat & Sun

Also returns True if cell is empty or 0 (if necessary the CF formula can
be
amended to exclude empty or 0 cells)

See the Weekday function in Help

Does A1 contain a true date, not text. If you clear all formats the cell
should show a number.

In the CF dialog did you change 'Cell Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
I put this formula into CF still nothing getting done.

"Peter T" wrote:

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a date,
in
the
rest
of the cells I have =a1+1 and so on to fill in all dates for the
year,
what I
need is for cells that only contain Sat and Sun in the date to
change
colour
to yellow, is this possible, I have tried through CF but not
formatting as
the cells contain formulas

Regards

MN

Cell colours according to date
 

It will be quicker to send you and example WB. Contact me at
pmbthornton gmail com
fill in the obvious at and dot

Regards,
Peter T


"santaviga" wrote in message
...
Hi Peter, I see what your getting at now, the cells do return numbers but
work back from 7 to 1 instead of 1 to 7 and this is after I format cell to
general, the only problem is that I need the cell to have the date in it
not
a number or true or false, any other suggestions, I may sound a bit
stupid.
MN

"Peter T" wrote:

What happens if you enter
=WEEKDAY(A1,2)
in any cell and A1 contains 1/1/07, a Monday
The formula should return 1
Now try some other dates incl Sat & Sun, eg Sun 21-Jan-07 should return
7

Now change the formula to =WEEKDAY(A1,2)5

try different dates
do you get the False for Mon-Fri and True for Sat & Sun

If it works in cells it should work in CF

I don't understand what you mean here
I don't have an option to
change cell value to formula is in 2007

I haven't asked you to change the cell from a value to a date, it
doesn't
make any difference to the CF if the cell is a 'constant' date value or
a
formula that returns a date. However in the CF dialog you MUST change
'Cell
Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
Still not working for me, A1 does contain a true date, when I enter
the
formulas you gave me it doesn't return the dates in the cells as if
mon
01-jan-07 and so on until the end of the year and I don't have an
option
to
change cell value to formula is in 2007

Regards MN

"Peter T" wrote:

=WEEKDAY(A1,2)

for dates Mon-Fri returns 0-5
for dates Sat & Sun returns 6 & 7

=WEEKDAY(A1,2)5

Returns True for Dates Sat & Sun

Also returns True if cell is empty or 0 (if necessary the CF formula
can
be
amended to exclude empty or 0 cells)

See the Weekday function in Help

Does A1 contain a true date, not text. If you clear all formats the
cell
should show a number.

In the CF dialog did you change 'Cell Value Is' to 'Formula Is'

Regards,
Peter T

"santaviga" wrote in message
...
I put this formula into CF still nothing getting done.

"Peter T" wrote:

Have another look at CF

Formula Is: =WEEKDAY(A1,2)5

Regards,
Peter T

"santaviga" wrote in
message
...
I have formulas set up to have in cell a1 Mon 01/01/07 as a
date,
in
the
rest
of the cells I have =a1+1 and so on to fill in all dates for
the
year,
what I
need is for cells that only contain Sat and Sun in the date to
change
colour
to yellow, is this possible, I have tried through CF but not
formatting as
the cells contain formulas

Regards

MN