Conditional formatting

Question

Conditional formatting

Anonymous

Hello, how can i highlight cells every 7 days and skip another seven days for a year with the starting day a monday, i saw one formula but it starts on Sunday

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Answer accepted by question author

0 additional answers

Answer 1

Hi Loomesh Appadoo,

Thank you for contacting Microsoft Community.

To apply conditional formatting that highlights every 7 days starting from a Monday and then skips the next 7 days (i.e., alternating weeks) for a full year, you can use a formula-based rule in Excel. You may try this:

Assume your dates are in column A, starting from cell A2.
Select the range you want to format (e.g., A2:A366 for a full year).

Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

Enter the following formula: =MOD(INT((A2-DATE(2025,1,6))/7),2)=0
Choose Format and adjust according to your needs

Result:

Explanation:

DATE(2025,1,6) is a Monday (January 6, 2025). You can adjust this to your actual starting Monday.
(A2 - DATE(...))/7 calculates how many weeks have passed since the starting Monday.
INT(...)/7 ensures we’re working with whole weeks.
MOD(...,2)=0 highlights every other week (i.e., week 0, 2, 4, etc.).

This will highlight cells that fall within the first week, third week, fifth week, etc., starting from the specified Monday.

I hope this solution works well for your scenario. If you need help adjusting the formula for a different start date or layout, feel free to reach out.

Best regards,

Demi-N | Microsoft Community Support Specialist

Share via

Conditional formatting

0 additional answers