One week color

Solved/Closed
-
 Camarlengo -
Hi, Is there any formula to make my one week date in to color red then next week in to color blue? I just want 2 colors interval each ever week of the year,...

example: (MSoffice excel)

07/03/2010
08/03/2010
11/03/2010
13/03/2010

will turns into color red

14/03/2010
18/03/2010
19/03/2010
20/03/2010

.. into blue

Thank you,
Camarlengo

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Objective:
Show and Color the week of the year

Assumption:
Lets say the dates are in Column A starting from A2

Steps:
1. Select Column A
2. Go to condtional formatting and insert this formula
=MOD(TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7),2)=0
and choose format and select color red

3. Go to conditional formatting and insert this formula
=MOD(TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7),2)=1
and choose format and select color blue

Basically TRUNC(((A1-DATE(YEAR(A1),1,0))+6)/7) gives you the week of year. Then we use MOD function to see what is the remainder if that week is divided by 2. If there is no remainder, we are selecting red color and if there is a remainder that we are selecting blue color
rizvisa1, thanks for immediate reply, it solved my problem!