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?
Hi Christopher,
I'm not sure where you are getting the categories from. You can start a series with Sunday
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:
Enter categories in Column A using the Reorganise tool.:
Not sure what you want next!
Regards,
Ian.
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)
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:
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
Is there a way to change the start day for the week?
