One week color
Solved/Closed
Related:
- One week color
- Windows calendar show week number - Guide
- Rj45 color - Guide
- Notepad++ change background color - Guide
- Powertoys color picker - Download - Other
- What color is audio out on pc - Guide
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
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
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
Apr 12, 2010 at 12:30 PM