[Case study] Conditional formatting on excel with customized “first day of the week”

Recently, we have a task to highlight the schedule in excel for last week and this week presenting with different color. However, the conditional formatting function come with Excel doesn’t not meeting our requirement. According to international standard ISO 8601, Monday is the first day of the week. It is followed by Tuesday, Wednesday, Thursday, Friday, and Saturday. Sunday is the 7th and last day of the week.

There is not option to customize the predefined function in conditional formatting, that’s said we need to DIY a new function with formatting rule. By using function WEEKNUM([DATE],2), we can get the actual week number of the [DATE] which comes with ISO8601 standard.

Therefore, using =WEEKNUM(A1,2)=WEEKNUM(TODAY(),2) can get the [DATE] result for this week; =WEEKNUM(A1,2)=WEEKNUM(TODAY(),2)-1 can get the [DATE] result for last week.