11 Replies Latest reply: May 21, 2011 9:00 PM by 4thSpace
4thSpace Level 1 Level 1 (0 points)

I'd like to create dates that span multiple years.  These dates are every day in each year sequentially.

 

If I start with 1/1/2000 and want to create every day up to 1/31/2005, can I start in A1 with 1/1/2000 then A2=A1+1 and drag that cell until I reach 12/31/2005?  Does Numbers handle those calculations correctly?

 

If that is possible, how do I then strip out all Saturdays and Sundays so I'm left with only Mon-Fri?

  • Badunit Level 6 Level 6 (11,400 points)

    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.

  • Badunit Level 6 Level 6 (11,400 points)

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

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

  • 4thSpace Level 1 Level 1 (0 points)

    Ok, nice. Exactly what I need.

     

    Is it possible to apply, after the fact, this to a column of dates that are a 7 day week (includes Sat/Sun) and have it strip out Sat/Sun?  I have an additional column I'm using that applies colors to each day in a particular order.  I can copy/paste this pattern of colors but only against a 7 day week.  Once that is done, I'd like to remove all Saturdays and Sundays since the color pattern has already been implemented.

  • Badunit Level 6 Level 6 (11,400 points)

    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.

  • 4thSpace Level 1 Level 1 (0 points)

    Excellent - that works great.  I went ahead with the fill technique since I don't need forumulas.

     

    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?

  • Badunit Level 6 Level 6 (11,400 points)

    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 Level 1 Level 1 (0 points)

    Thanks but I'm not actually setting the color of a cell.  It's just text such as "Brown", "Purple", etc.

  • Barry Level 7 Level 7 (29,210 points)

    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:

    Picture 8.png

    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

  • 4thSpace Level 1 Level 1 (0 points)

    Thanks again Barry.

  • Barry Level 7 Level 7 (29,210 points)

    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

  • 4thSpace Level 1 Level 1 (0 points)

    Yes - sorry.