This is a short post but it took me a while to figure it out, so I thought I'd post it. I have a long list of dates in a spreadsheet, and wanted to have Saturdays and Sundays formatted differently so that they stood out. I tried a number of things in Conditional Formatting, until I hit on this one.
Highlight the column you want to apply this to and bring up the Conditional Formatting dialog from the Format menu. Set a style in the Style dropdown (I chose 10% grey background for eg, and called it 'greyed'. Change the other dropdown to "Formula Is", and put this in the box:
OR(WEEKDAY(CELL("contents"))=1, WEEKDAY(CELL("contents"))=7)
That's basically it. This works in LibreOffice and OpenOffice, and should also work in Excel too, as the functions are the same.