Highlight odd/even months
Color odd months
Conditional formatting formula:
Explaining CF formula in cell B6
Step 1 - Calculate number of month
The MONTH function returns a number representing a month based on a date. 1 = January, 2 = February ... 12 = December.
MONTH($B6)
becomes
MONTH(1/1/2008)
becomes
MONTH(39448)
and returns 1. 1 = January.
Step 2 - Calculate remainder
The MOD function returns the remainder after a number is divided by a divisor.
MOD(MONTH($B6),2)
becomes
MOD(1, 2)
and returns 1.
In cell B10 the date changes to 2/2/2008. MOD(MONTH($B6),2) becomes MOD(2,2) and returns 0 (zero). This cell is not highlighted with this specific color.
How to apply conditional formatting
- Select cell range B6:D19
- Go to tab "Home" on the ribbon
- Press with mouse on "Conditional Formatting" button
- Press with mouse on "New Rule.."
- Select "Use a formula to determine which cells to format"
- Type =MOD(MONTH($B6),2) in field "Format values where this formula is true:"
- Then press with left mouse button on "Format..." button
- Go to tab "Fill"
- Pick a color
- Press with left mouse button on OK button
- Press with left mouse button on OK button to return to Excel.
Color even months
Conditional formatting formula:
The NOT function returns the boolean opposite to the given argument. The numerical equivalent to boolean value TRUE is 1 and FALSE is 0 (zero).
Color odd years
Conditional formatting formula:
The YEAR function returns the year based on a date.
Color even years
Conditional formatting formula:
Border between months
Conditional formatting formula:
Border between years
Conditional formatting formula:
Get excel file for this tutorial.
Highlight every even month.xlsx
(Excel 2007 Workbook *.xlsx)
Cf dates category
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]
Conditional formatting category
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Functions in this article
More than 1300 Excel formulas
Conditional Formatting categories
Excel categories
One Response to “Highlight odd/even months”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
That's how to explain!!!
Congratulations