Have Excel default to start week on a Monday, instead of Sunday.

[hutch458]

I have an excel document that auto calculates multiple due dates, I’ve also built in conditional formatting that highlights due dates for “this week” (high red) and “next week” (highlight yellow) when I open it. Excel auto starts the day of the week as Sunday. Is there any way to change the start of the week to Monday? Any guidance or assistance would be greatly appreciated!

 

[bobsan42]

weekday() function takes a second parameter which defines the start day of the week.

 

[hutch458]

Is there a way to make the WEEKDAY function apply to an entire sheet?

 

[bobsan42]

How exactly does the first day of the week affect you worksheet?

 

[sandy666]

maybe change system settings: Region - Additional Settings - Date - First Day od the Week : Monday

 

[hutch458]

One sheet of my excel document identifies multiple due dates. For example, from the time we open an account I have documents due at day 3, 10, 15, 30, 45, 60, etc. I've used conditional formatting to highlight items that are due "this week" to appear in red, and items that are due "next week" to highlight yellow. My problem is that the excel default goes from Sunday to Saturday for a week, when I need it to go from Monday to Sunday. When I see red highlight dates for this week I don't want to see anything from last week on Sunday because that should have been taken care of in the previous week. I hope this makes sense.

 

[bobsan42]

What are the conditional formatting formulas that you used?

 

[hutch458]

Some of the due dates auto generate from another tab (i.e. - "='Master List'!AN141"), some have their own formulas that are based off another date (i.e. - "=A149+30"). The conditional formatting is... Conditional Formatting > Highlight Cells Rules > A Date Occurring... then I have three of these categories running that include "Last Week", "This Week", and "Next Week". Each category has it's own highlight color to differentiate them from one another.

 

[bobsan42]

Despite the fact that my date settings say that the week starts on Monday, this sort of conditional formatting still starts it on Sunday.

So I would suggest to use a formula to determine which cells to format.
Something like:
=Weeknum(A1,2)=weeknum(Today(),2)-1

or better (to avoid start/end of year confusion) Conditional format for previous week like this:
New rule ... -> Format only cells that contain -> Cell Value ->
between ->
=TODAY()-WEEKDAY(TODAY(),2)-6
and
=TODAY()-WEEKDAY(TODAY(),2)