Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday?
Another possibility -
You say you are returning days as Text. Sat & Sun are the only days that
start with an "S" - right? (in English at least).
so you could change the first formula in my previous example to
=LEFT($B1,1)="S"
assuming day as text is in B1 and the first character in the cell relates to
the day.
Regards,
Peter T
"Peter T" <peter_t@discussions wrote in message
...
I don't follow where/how your IF function comes into things. But one way
or
another it should be possible to use the results of the WEEKDAY function
to
CF your cells. Look in help for the second argument in the function. I
used
value 3 so that Mon-Fr returns 0-4, making it easy to return True/false in
another formula.
Enter a date in A1, eg Ctrl-semicolon
In B1, =TEXT(A1,"dddd")
Apply CF to B1 like this
Change "Cell value is" to "Formula is"
=WEEKDAY($A1,3)<5
Apply some format
Add condition-2, also "Formula is"
=WEEKDAY($A1,3)4
apply a format
Select A1:B1
Grab the small selection handle bottom right, drag down. You should see
Sat-Sun with different format to Mon-Fri.
As I said, I don't follow your IF function so you will need to adapt.
Perhaps helper cells with the same formula as above, to return simply
True/false.
Note the row ref in the formula is relative (without a $) to allow you to
copy the formula down.
Regards,
Peter T
"StargateFanFromWork" wrote in message
...
This weekday function looked promising but the same trouble may exist as
befo the dates will always vary in any given cell. B2 may have any
day
of the week.
When I finally got a working formula - at least, it returns a value - I
get
Thursday instead of Saturday.
I modified this: =IF(A2<"",A2+1,"")
to what would work, which happened to be this:
=IF(A2<"",WEEKDAY(A2+1,3),"")
And in the cell that is formatted to show date in text format, it says
Thursday instead of Saturday.
Did I do something wrong?
"Peter T" <peter_t@discussions wrote in message
...
Look at Excel's WEEKDAY function, eg
=WEEKDAY(mydate,3)
If it returns 0-4 it's a weekday. Lends itself nicely to conditional
formatting.
Regards,
Peter T
"StargateFanFromWork" wrote in message
...
Hope the subject line isn't totally muddy <g.
I have a spreadsheet that is super. The only trouble is that
weekends
aren't differentiated from weekdays in any way.
Since the spreadsheet contains a macro for the user to select the
starting
date and then the rest of the spreadsheet is then populated for 3
weeks
following that date, Saturday and Sunday always fall on different
cells
at
any given time. Also, affected cells may say "Saturday" or "Sunday"
but
there is actually only a formula in any of them. The only exception
is
the
very first cell which is the initial one the macro dumps to. So the
first
might read "Friday" if I choose today's date in the prompt box, but
all
the
rest of the days of the weeks shown display the dates according to
appropriate variations of this formula:
=IF(A2<"",A2+1,"")
In today's example, this actual formula above displays "Saturday".
The
cell
immediately below displays "Sep.17.2005". I'm hoping that since
it's
the
same type formatting issue, hopefully resolving cell colouring for
the
text
"Saturday" issue will lead me to figure out how to do so for dates
that
fall
on weekends.
Is there a way to do this cell colour change under these conditions?
I'm in favour of conditional formatting, but couldn't figure out how
to
do
so because of the fact above that no actual text is found where all
the
Saturdays and Sundays are, only the formula. Tx.! :oD
|