Color background of chart based on day

stvelucky wrote:
I have a line chart that lists sales by day and an overall average

I want to color the background based on the day. I would like Mon-Fri. to be one solid color while Sat & Sun are a different color. This will help me to visually see how my sales did on the weekend vs. weekdays. I thought the easiest way might be to create a second bar chart and place it behind my line chart.

Right idea. Instead of a second chart, I used a single line table, placed behind the chart. The image also contains a duplicate of this table, which you won't need.



The selected table, 2-1, is the duplicate of the one placed behind the chart to highlight the weekend days (May 1, 2, 8, 9). In the duplicate, I've left the 'standard' cell background (white) and text colour (black), and set the conditional text colour to red and fill colour to a light yellow so that the value contained in each cell is visible.

In the 'real' Table 2, Fill and Text colours are both set to white (choose which ever colour you want for weekdays) and conditional Fill and Text colours are both set to the same shade of yellow (again, choose whichever colour you want for weekends) to make the text invisible.

the rule (condition) used for all cells in Table 2 is "Equal to" 1.

The fill colour of the Chart is set to 'none' (top left cell in the colour palette shown when you click the Fill color well) to allow table 2 to show through when it is placed behind the chart.

The formula in all cells of Table 2 is:

=IF((OR(DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Saturday",DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Sunday")),1,0)

which is also shown in the text box on the screen shot.

The formula requires that the dates in column A of Table 1 be actual dates, and that they be in column A.

Table 2 is a single row, plain style table (no header rows, no header columns). Resize it by selecting the table to get the handles shown on table 2-1 in the screen shot, then drag the handles to make the table wide enough to center the columns on the data points of the chart, and to make the height of the table cover the chart (and if desired, the x-axis labels).


Regards,
Barry

stvelucky wrote:
In some of the rows on Table 1, the date is blank which returns an error on my new colored date chart. Is there a way to make it suppress the warnings?

Enclose the formula in an IFERROR() statement.

=IFERROR(IF((OR(DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Saturday",DAYNAME(OFFSET(Table 1 :: $A$1,COLUMN(),ROW()-1))="Sunday")),1,0),0)

With the last value set as 0, the no-date columns will have the same colour as weekdays. If you want a different colour, use a different value ( eg. end the formula with " ),-1)" ), and set a second conditional rule to set Fill and Text colours to the 'different colour' when the cell is 'equal to -1'.

Regards,
Barry

Thanks for the feedback.

IFERROR() is quite useful (as you'll see when checking some of the examples in the forum).

Regards,
Barry