# One week color

Solved/Closed
Camarlengo - Apr 12, 2010 at 12:09 AM
Camarlengo - Apr 12, 2010 at 12:30 PM
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 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 12, 2010 at 09:18 AM
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!