How to remove Sat/Sun?

The answer to your first question is yes, Numbers can handle that. But rather than dragging down, which will take some time, you can put the formula in one cell and copy/paste to all the rest of them in one step.

You can strip out Saturdays and Sundays with a change to the formula. But it would be better if the first date was not a Saturday. 1/1/2000 is a Saturday. I'll get to it in a few minutes.

Starting with Dec 31, 1999 in cell A1, the formula in A2 will be

=IF(WEEKDAY(A1)=6,A1+3,A1+1)

Do you really want formulas to calculate the dates or would you be happier if the dates were the actual dates?Having them as dates instead of formulas makes it easier to remove rows.

A way to create a list of dates without using formulas is to enter the first two dates, select all the cells in the column starting at the first date and including all the empty cells below it that you want filled, then go to the Insert menu and choose Fill Down. Or, if you already have the list of dates, select them all, Copy, then Paste Values.

Once you have all your color coding, I'd create a column that has the formula =WEEKDAY(A) . Sort by that column to get all the 1's and 7's grouped together. Delete all the rows with 1's and 7's. Sort by column A to get it back into date order. Delete the extraneous column of weekdays.

There are no formulas that can tell what color a cell is or that will change the color of a cell. But there is conditional formatting.

There are two ways to set the color of a cell. The first way is to do it manually, as you have done. The second way is to do it with conditional formatting. Conditional formatting does things like "if the value of the cell is 1 then set the fill color to green, if it is 2 then set the fill color to red, ...". So, if the color for each week is based on some kind of a formula, you can use conditional formatting to convert the formula result into a color.

I hope that helps.

4thSpace wrote:

One last question: I'd like to add another column named WeekColor. This value will be what ever the Monday color is for that particular week. If Monday is Purple, Tues-Fri will also be Purple. If Monday is Brown, Tues-Fri will also be Brown. Do you have some idea on how that can be accomplished?

Here's an example:

Formula:

in D2: =OFFSET($A$1,ROW()+1-WEEKDAY(A),2,,)

Fill down to end of column D.

The Bold text in column C was added manually, and is just to let me see what colour should be appearing for each week.

For further information on the functions used, see the iWork Formulas and Functions User Guide. You can download the guide through the link in the Help menu in Numbers.

Regards,

Barry

You're welcome, but I'm surprised to see my answer marked as "Correct Answer" as it doesn't really answer your original question (which was already answered by Badunit before you asked the supplementary question. Please go back and mark BU's answer(s) as 'Helpful' for the benefit of others looking for an answer to the same question as you originally asked.

Regards,

Barry