Conditional formatting based on date

问题:

Trying to set up a time card at work in Excel, have dates going down the left-hand side of the worksheet:

Fri 1/1/11

Sat 1/2/11

Sun 1/3/11

And so on. I want to gray out the rows with weekend dates like so:

Fri 1/1/11

Sat 1/2/11
Sun 1/3/11

Is there an obvious way to do this using conditional formatting?

回答1:

Sure, you can use the WEEKDAY function to determine the day of the week, like this:

=WEEKDAY($A1,2)>5

Depending on what days of the week are considered the weekend, you may need to modify the second parameter. You can find more information about the WEEKDAY function (and the second parameter) here.

回答2:

Assuming dates are in Column A:

=IF(OR(WEEKDAY($A1)=1,WEEKDAY($A1)=7),1,0)

Reads If the weekday is either Sunday or Saturday then True (Use Conditional Formatting)

Or you can modify the same formula so that the 'weekdays' starts on Monday so it looks like this:

=IF(WEEKDAY($A1,2)>5,1,0)