2 Replies Latest reply: Sep 1, 2013 10:34 PM by Barry
Christopher Mcfarland Level 1 Level 1 (35 points)

So I really like the "Insert Categories from the following" functionality of the Reorganize tool, and I fell in love with it's ability to break down by date automagically. I've encountered one problem with my use of it, though: I start my "work week" on Sunday, and it defaults to Monday as the first day of a week without any seeming way to change it to Sunday. Is there a way to do this?

  • Yellowbox Level 5 Level 5 (6,640 points)

    Hi Christopher,

     

    I'm not sure where you are getting the categories from. You can start a series with Sunday

     

    Screen Shot 2013-09-02 at 3.21.55 PM.png

     

    Enter Sun then Mon (press enter after each entry). Select the first two cells and drag the Fill Handle down. The Fill Handle is the little white circle bottom right of Cell A3 in this screen shot.

     

    To get this:

     

    Screen Shot 2013-09-02 at 3.26.25 PM.png

     

    Enter categories in Column A using the Reorganise tool.:

     

    Screen Shot 2013-09-02 at 3.28.01 PM.png

    Not sure what you want next!

     

    Regards,

    Ian.

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

    Hi Christopher,

     

    The WEEKDAY function allows specifying either Sunday or Monday as the first day of the week:

     

     

    WEEKDAY

    The WEEKDAY function returns a number that is the day of the week for a given date. WEEKDAY(date, first-day)

    •   date:  The date the function should use. date is a date/time value. The time portion is ignored by this function.
    •   first-day: An optional value that specifies how days are numbered.
      Sunday is 1 (1 or omitted):  Sunday is the first day (day 1) of the week and Saturday

      is day 7.
      Monday is 1 (2):  Monday is the first day (day 1) of the week and Sunday is day 7. Monday is 0 (3):  Monday is the first day (day 0) of the week and Sunday is day 6.

     

    But I think you are referring to the first day of the 'workweek', for which I do not see a means of defining a custom value.

     

    Since you want to 'insert categories', though, you could easily define your own, using WEEKDAY(date) or WEEKDAY(date,1), plus an IF statement to return the category label appropriate to the day. Here's one for a Sunday to Thursday work week. Dates are in column A, the formula is in whichever column you want as the Category column. For the example, I've placed it in column B.

     

    B2, and filled down: =IF(WEEKDAY(A)<6,"Work","Off")

     

    The top table shows the weekday numbers returned for each day of the week for each of the three permitted values for the optional second argument. The bottom table shows the results from the formula above, used to define a category label for each date:

    Screen Shot 2013-09-01 at 10.23.29 PM.png

    A10 was left blank intentionally, to determine if the lack of data resulted in an error. The Warning message, flagged by the blue 'warning' triangle, is "The formula uses a number in place of a date." The 'date' assigned to this numerical value of zero was a Friday, but I'm not certain when. Probably best to avoid extra rows with no date shown.

     

    Regards,

    Barry