Search code examples
excellibreoffice-calc

Office calc - conditional formatting for weekends

Programming a calendar, want to highlight all weekend days by conditional formatting. The value of the cell is the date do be controlled.

There is a Condition called Formula is and according to the help you can use any formula. So I could write for every day of the year a own formula. In visual basic there is something like "activeCell" or so, which is referencing the active cell. This would make one formula for all.

// pseudo code for conditional formatting "Formula is"
IF(WEEKDAY(activeCell)=6 or WEEKDAY(activeCell)=7); TRUE();FALSE())

Is there something like a activeCell or a total other way to archive this simple looking task?

Solution

  • You can use the following formula:

    =NOT(OR(WEEKDAY(A1;2)=6;WEEKDAY(A1;2)=7))

    Conditional formating to mark only weekdays in Excel

    A1 is the cell holding your date. WEEKDAY with the second parameter being 2 delivers the number of the weekday with Monday being 1 and Sunday being 7.

    The formula results to TRUE if the day is neither a Saturday or a Sunday.

    I hope that helps.