Date-Related Conditional Formatting Rules in Google Sheets

Based on my experience, much of the conditional formatting in Google Sheets centers around dates. Therefore, in this tutorial, I’ve included several date-related conditional formatting custom formula rules for use in Google Sheets.

You can apply these types of highlight rules in two different ways:

Using built-in rules – the rules already defined.
Using custom formulas – user-defined.

I’ll explain both types.

Here are a couple of examples that include built-in options and custom formulas.

Highlight Today’s Date in a Cell Range (2D Array)

Let’s begin the date-related conditional formatting by highlighting today’s date within a specified range.

To highlight today’s date in the range A2:F8, you can use the following conditional formatting rule in Google Sheets:

Select the range A2:F8.
Click on “Format” in the menu bar.
Choose “Conditional formatting.”
Under “Format rules,” select “Date is” from the drop-down menu.
In the second drop-down menu, select “today.”
Click “Done” to apply the formatting.

Date-related conditional formatting examples based on today's date

Note: The screenshot was taken on 01/12/2018, which reflects today’s date. When you use this highlight rule, it will pick the current date because the TODAY() function is volatile. This applies to all screenshots wherever today’s date is applied in the formula.

The problem with the above method (built-in rule) is that it highlights cells containing today’s date, including timestamps. To exclude timestamps, follow the custom formula approach below:

Select the range A2:F8.
Click on “Format” in the menu bar.
Choose “Conditional formatting.”
Under “Format rules,” select “Custom formula is” instead of “Date is.”
In the provided field, enter the following formula: =INT(A2)=TODAY()
(Note: A2 refers to the top-left corner cell in the range.)
Click “Done” to apply the formatting to the range A2:F8.

Highlighting an Entire Row or Column if the Date is Today’s Date

The default rules in Google Sheets’ conditional formatting are specific to individual cells. Hence, we resort to custom formula-based rules to match a date within a cell and highlight the entire row or column. Below, you’ll find two such date-related conditional formatting rules.

Rules for Entire Row:

When there are date entries in a column, highlighting rows containing today’s date in that column can help focus on entries falling within today’s date.

Assuming we have dates in the range A2:A9, to highlight the rows containing today’s date in this column, follow these steps:

Select the range A2:D9 (as illustrated below) or the desired range starting from cell A2.
Click on “Format” > “Conditional formatting.”
Under “Format rules,” select “Custom formula is.”
In the provided field, enter the following formula: =$A2=TODAY()
(Note: A2 refers to the first cell in the column range.)
Click “Done” to apply the formatting.

Google Sheets - Entire row highlighted if cell matches today's date

Rules for Entire Column:

Now, let’s explore how to highlight an entire column if the header row contains today’s date.

If the range is A2:C100, you can use this formula:

=A$2=TODAY()

Google Sheets - Entire column highlighted if cell matches today's date.

Key Points:

The rule should be coded to evaluate the first cell in the apply-to range when utilizing custom formula rules. The sheet will then apply this rule to the selected range (Apply to range).

Correctly using dollar signs in the cell reference is crucial for highlighting entire rows and columns. In entire row highlighting, prefix the $ with the column letter, and in the entire column highlighting, prefix the $ with the row number.

Conditional Formatting if Date Falls Between Two Dates

It is another commonly occurring date-related conditional formatting in Google Sheets.

Suppose you want to highlight dates in column A that fall between 01/11/2018 and 30/11/2018. You can use the following custom formula in conditional formatting.

I am applying this formatting to a small range of A2:A10.

=AND(A2>=DATE(2018, 11, 1), A2<=DATE(2018, 11, 30))

Alternatively, you can rewrite the above rule using the ISBETWEEN function as follows:

=ISBETWEEN(A2, DATE(2018, 11, 1), DATE(2018, 11, 30))

Note: The syntax of the DATE function in the formula is: DATE(year, month, day)

However, you can achieve the same using the built-in conditional formatting rule.

Steps:

Select the range A2:A10.
Click on “Format” > “Conditional formatting.”
Under “Format rules,” select “Is between” from the drop-down menu.
Enter the formulas =DATE(2018, 11, 1) and =DATE(2018, 11, 30) under that.

Please see the below illustration.

Highlighting dates within two specified dates in Google Sheets

Conditional Formatting for Upcoming Dates in Google Sheets

This method is particularly useful when you need to highlight upcoming birthdays, project deadlines, contract renewals, and so forth.

Please note, as mentioned previously, all the formulas in this section are based on the current date, utilizing the TODAY() function. Consequently, the screenshots may not correspond with the date you read this post.

Highlighting Dates Falling Within One Week

For the “Apply to range” A2:A, you can utilize the following formula:

=AND(A2>TODAY(), A2<=TODAY()+7)

Alternatively, you can use the ISBETWEEN function with the following alternative rule:

=ISBETWEEN(A2, TODAY(), TODAY()+7, FALSE, TRUE)

This formula will highlight dates that fall within the next 7 days starting from tomorrow onwards.

Highlighting Dates Falling Within N Months (One Month, Six Months, or One Year)

Using the EDATE function, we can return a date that is a specified number of months before or after a start date.

For example, =EDATE(TODAY(), 1) will return the date one month after today’s date.

For instance, if TODAY() is 01 December 2018, the formula will return 01 January 2019.

Let’s utilize the EDATE function in date-related conditional formatting for the range A2:A:

To highlight dates falling within one month, use the following rule:
=ISBETWEEN(A2, TODAY(), EDATE(TODAY(), 1), FALSE, TRUE)
For six months, replace EDATE(TODAY(), 1) with EDATE(TODAY(), 6).
For one year, replace EDATE(TODAY(), 1) with EDATE(TODAY(), 12).

Highlight a Column Based on the Date in Another Column

I have two columns of data in the range A2:B6. In that, I want to highlight cells in column A (A2:A6) if the dates in column B (B2:B6) fall within six months from today’s date.

Custom Conditional Format Rule:

=ISBETWEEN(B2, TODAY(), EDATE(TODAY(), 6), FALSE, TRUE)

Apply to Range: A2:A6.

To extend the highlighting into both columns, change the “Apply to range” to A2:B6 and place the dollar sign with the cell reference in the formula.

=ISBETWEEN($B2, TODAY(), EDATE(TODAY(), 6), FALSE, TRUE)

You can understand from the above examples that the TODAY and EDATE functions play a key role in date-related conditional formatting in Google Sheets. Also, the comparison operators can be replaced with the ISBETWEEN function when comparing whether a cell date falls between today’s date and an upcoming date.

Highlighting Specific Weekdays

With the help of the WEEKDAY function, we can highlight specific weekdays in Google Sheets.

For example, here is the formula to highlight all the Fridays in cell range B2:B6.

=WEEKDAY(B2)=6

By default, the counts in WEEKDAY start with Sunday = 1. So, the number 6 will represent Friday.

Resources

123 COMMENTS

Pranay Sawant December 14, 2023 At 5:30 pm

Hi, I have a conditional formatting question:

I have a column labeled ‘Date of Birth.’

I would like the cells in the column to turn yellow when the time comes.

- Prashanth KV December 15, 2023 At 1:55 pm
  
  You can try this formula:
  
  =DATE(YEAR(TODAY()), MONTH(E2), DAY(E2))=TODAY()
  
  Assuming E1 contains ‘Date of Birth,’ select E2:E and navigate to Format > Conditional formatting > Format rule is “Custom formula is.” Insert the formula in the given field.
  
Julia May 18, 2023 At 1:14 am

Hi, I have a conditional formatting question:

I have a column with the label ‘Date of Expiration.’

I would like the cells in the column to show yellow when they are near expiring (say a month) and red when they have expired.

So, for example, if the date of expiring is 5/5/2023 for a cell in that column: I want it to show yellow on 4/5/2023 and then red once the current date is 5/5/2023 or beyond.

- Prashanth May 18, 2023 At 11:28 am
  
  Hi, Julia,
  
  I’ve answered this already. For column C, insert these formulas in the custom formula rule.
  
  Apply to Range: C1:C
  
  Red:
  
  =and(isdate(C1),gte(today(),C1))
  
  Yellow:
  
  =isbetween(C1-today(),1,30)
  
Jay May 16, 2023 At 2:56 am

Hi, I have a formatting situation.

I am trying to get cell B34 and cell D34 to turn a specific color when cell D34 shows today’s date and cell E34 contains the letters “po.”

Thanks!

- Prashanth May 16, 2023 At 2:15 pm
  
  Hi, Jay,
  
  Since you want to evaluate two conditions to TRUE, use AND logical test.
  
  =and(D34=today(),search("po",E34))
  
  - Jay May 16, 2023 At 10:34 pm
    
    Thanks so much!
    
Rylie April 18, 2023 At 11:03 pm

I am trying to get the D column to turn red if the date is older than 11 months.

- Prashanth April 19, 2023 At 3:20 pm
  
  Hi, Rylie,
  
  Please go thru’ the comments or share an example Sheet below.
  
James Riggs April 12, 2023 At 4:02 am

Question on conditional formatting rules.

I want a formula or to set up a conditional format for the cell to change color to yellow (nearing) and red (past) on an exact date.

- Prashanth April 12, 2023 At 8:40 am
  
  Hi, James Riggs,
  
  The formula for cell C4.
  
  Nearing (10 days):
  
  =isbetween(C4-today(),1,10)
  
  Expired:
  
  =and(isdate(C4),gte(today(),C4))
  
Stacy March 31, 2023 At 12:50 am

I have a column that has a list of appointment dates.

I want the dates in this row to be highlighted if they are greater than one year old.

I have tried many different things and cannot seem to get it to work.

- Prashanth March 31, 2023 At 1:38 pm
  
  Hi, Stacy,
  
  Here is an example for column A.
  
  =and(len($F1),today()>edate($F1,12))
  
  The “Apply to range” is F1:F
  
  I hope you can adapt this into a different column.
  
Fabio March 14, 2023 At 8:48 pm

Hello Prashanth,

Hope everything is well.

I am trying to do a conditional format rule when two conditions.

I need a full row to be highlighted in grey if the below two conditions are met:

– In column J I have “NOT ESCALATED YET”
AND
– In column E 3 work days have passed since the corresponding date mentioned on each E cell.

CONTEXT:
I need to easily track when I am able to escalate certain support cases. For the above example, I am only allowed to escalate a case after 3 days I received it in my queue.

In column J I have a dropdown menu with two text options – “NOT ESCALATED YET” and “ALREADY ESCALATED”.

In column E I have the date when I received a certain case in a “DD/MM/YY HH/MM/SS” format.

Do you think this is possible?

Thank you!

- Prashanth March 17, 2023 At 8:04 am
  
  Hi, Fabio,
  
  You can try this rule.
  
  =and(today()-$E2>=3,len($E2),$J2="NOT ESCALATED YET")
  
  The apply to range is important. It must start on the second row, e.g., A2:Z100.
  
  I haven’t considered the time. If you want, please let me know.
  
Nai January 27, 2023 At 2:32 am

Hi!

I am trying to conditional format two things:

1. Column Z to highlight green if the date entered is on or before the date in column Y

2. Column Z to highlight red if the date entered is after the date in column Y.

Thank you in advance!

- Prashanth January 27, 2023 At 11:35 am
  
  Hi, Nai,
  
  Rule # 1 (Green):
  
  =lte(datevalue(Z2),datevalue(Y2))
  
  Rule # 2 (Red):
  
  Simply wrap the above formula with NOT(), and here it is!
  
  =not(lte(datevalue(Z2),datevalue(Y2)))
  
  N.B.: LTE means Less Than or Equal to.
  
  - Nai January 27, 2023 At 11:00 pm
    
    Thank you!!
    
Dan October 31, 2022 At 9:48 pm

Hello!

I am trying to create conditional formatting so that if the date in the cells (Column E) after 6 weeks past from today’s date (If today is 10/31/22, then any date 9/19/22 or earlier) is highlighted.

I attempted =gte($B4,today()+14) but changed it to 42 (for 42 days = 6 wks) and changed B4 to E14 (for my test cell), but it is not functioning.

PS – I cannot share my file because it contains patient information. I appreciate any and all help!

- Prashanth November 1, 2022 At 10:43 am
  
  Hi, Dan,
  
  This may work for you!
  
  =and(isdate($E14),lte($E14,today()-42))
  
Ali Abbasi September 11, 2022 At 4:32 am

Hello Prashanth,

I have a situation, column A with dates and columns B to G with other related data.

I want all columns to turn to an Orange color when the date is entered in Column A.

- Prashanth September 12, 2022 At 12:18 pm
  
  Hi, Ali Abbasi,
  
  Apply the following formula for the range A:G.
  
  =isdate($A1)
  
  - Shivaraj September 29, 2022 At 11:14 pm
    
    Thank you, that’s just what I needed too!
    
Suruchi July 18, 2022 At 5:10 pm

Hey,

Can you help me with this formatting?

Column A has dates that show when my book should be delivered.

Highlighting Conditions:-

Column B should be highlighted in yellow if it is one day above and is blank.

Column B – to be highlighted in red if it is five days above the date in column A and is still blank.

Best
Suruchi

- Prashanth July 19, 2022 At 12:19 pm
  
  Hi, Suruchi,
  
  Select B1:B and insert the rules for this range.
  
  Green:- Date falls within the past 5 days.
  
  =and(gte(A1,today()-4),lte(A1,today()),B1="")
  
  Red:- Date past 5 days+
  
  =and(len(A1),lt(A1,today()-4),B1="")
  
  Yellow:- Future dates
  
  =and(gt(A1,today()),B1="")
  
  - Shivaraj October 1, 2022 At 12:01 am
    
    Hi Prashanth, could you help me understand why you used “len” in the formula:
    
    =and(len(A1),lt(A1,today()-4),B1="")
    
    - Prashanth October 1, 2022 At 10:09 am
      
      Hi, Shivaraj,
      
      It’s one way to test whether A1 is blank or not.
      
      Please check yourself =and(len(A1)) in your Sheet.
      
Jeffrey May 6, 2022 At 2:33 am

Thank you!!

Jeffrey May 5, 2022 At 3:50 pm

Thanks for the great article! I have a question that I’m hoping you can help with.

In Google Sheets, I have the numbers 1 to 31 in Row A. The number 1 starts in A2.

Here’s my question.

Suppose today is the 13th of the month.

Is there a formula that would highlight the number “13” in column A?

- Prashanth May 5, 2022 At 8:05 pm
  
  Hi, Jeffrey,
  
  Yep! Please try this custom formula.
  
  =A2=day(today())
  
  Apply to range: A2:A32
  
Riya April 23, 2022 At 1:18 am

Hi Prashanth,

I tried your formula, but it didn’t work 🙁

- Prashanth April 23, 2022 At 9:33 am
  
  Hi, Riya,
  
  Time to share a copy of your Sheet or sample Sheet with edit rights. Include the URL in your reply below, which won’t be published.
  
Riya April 21, 2022 At 3:39 am

Hi,

I want an entire row to change a certain color when the date in a cell has been 2 weeks or greater than today’s date.

- Prashanth April 21, 2022 At 4:53 pm
  
  Hi, Riya,
  
  B4 – 05/05/2022 (date to test)
  
  Want to highlight row A4:Z4?
  
  Custom Formula rule: =gte($B4,today()+14)
  
  If you have another date to test in B5, just replace the Apply to range A4:Z4 to A4:Z5. The same formula will work.
  
Kari February 20, 2022 At 11:08 pm

My turn!

If D2 is 18/2/22, I need A2 to change to “Bob” if N2 is either “Sally” or “John” after two days from the date in D2. Is this doable?

Any help is appreciated!

- Prashanth February 21, 2022 At 8:45 am
  
  Hi, Kari,
  
  You can try this.
  
  =if(AND(OR(N2="Sally",N2="John"),gte(today(),D2+2)),"Bob","default name")
  
Julio February 16, 2022 At 9:35 pm

Hey, I haven’t found this one yet.

Is there a way to make a color scale for dates organized by how close they are to today?

Thanks in advance for your responsiveness!

- Prashanth February 18, 2022 At 9:48 am
  
  Hi, Julio,
  
  I don’t find a way to use today() function within Color Scale.
  
  So you may manually insert today’s date within the Color Scale.
  
  Steps:-
  1. Enter =datevalue(today()) in any (blank) cell and copy that output.
  2. Within Color Scale, set Minpoint Min value and Maxpoit to Max value to White.
  3. Against the Midpoint Number field, paste the copied value. You may require to copy-paste the said value every day from the formula inserted cell.
  
HRW January 26, 2022 At 11:47 pm

I apologize if this was already answered. I looked through, and none of the responses seemed to fit my issue.

I’m looking to highlight cells in columns B, D, and E if the cells in column A is either today or tomorrow. How can I do this? Thanks so much!

- Prashanth January 27, 2022 At 4:53 am
  
  Hi, HRW,
  
  To apply the formula rules for the range B2:B1000, D2:D1000, and E2:E1000, you can use the below date (TODAY function) related rules.
  
  Settings within “Conditional Format Rules”:-
  
  Apply to Range: B2:B1000,D2:D1000,E2:E1000
  Rule 1: =$A2=today()
  Rule 2: =$A2=today()+1
  
james January 26, 2022 At 9:51 pm

Hi, I have a Column of dates to show when employees’ training expires.

How can I have them highlighted to show the order of expiry?

For instance, red for the soonest, yellow for the ones after that, and green for the ones with plenty of time before they expire.

Many thanks.

- Prashanth January 27, 2022 At 4:44 am
  
  Hi, James,
  
  Color Scale will be the best option. If that doesn’t meet your requirement, you can try this.
  
  Apply to range B2:B1000.
  
  Rule 1 – Red
  =B2=small($B$2:$B$10,1)
  
  Rule 2 – Yellow
  =B2=small($B$2:$B$10,2)
  
  Rule 3 – Green
  =B2=small($B$2:$B$10,3)
  
  Or this one.
  =gte(B2,small($B$2:$B$10,3))
  
Danielle January 18, 2022 At 5:10 am

I want to highlight cell E if the date in Cell C is not in between the dates in Cell H and Cell I. Is this possible?

- Prashanth January 19, 2022 At 9:48 am
  
  Hi, Danielle,
  
  It’s, of course, possible! For that, we can use the ISBETWEEN function.
  
  Highlight E2 if C2 is not between H2 and I2.
  
  Custom Formula:
  
  =not(isbetween($C2,$H2,$I2))
  
  Apply to range: E2
  
  In the above formula, both the dates in cells H2 and I2 are inclusive. If you don’t want, try this one.
  
  =not(isbetween($C2,$H2,$I2,false,false))
  
  The above same formulas will work for columns. Just change “Apply to rage” to E2:E.
  
Dakota Weichers November 29, 2021 At 2:06 am

Hi, I am confused.

I am trying to have conditional formatting, where the item was bought in one column and then sold in another column.

If it’s 7-days difference, it’s green; 7 to 14 days difference, it’s yellow; and 14+ days difference, it’s red.

I know it will be multiple rules.

- Prashanth November 29, 2021 At 7:46 am
  
  Hi, Dakota Weichers,
  
  B2:B = Item Names
  C2:C = Date of Purchase
  D2:D = Date of Sales
  
  Rules:- Select B2:B, C2:C, or D2:D, then apply the below three rules one by one.
  
  Green: =and(gt(days(D2,C2),0),lte(days(D2,C2),7))
  Yellow: =and(gt(days(D2,C2),7),lte(days(D2,C2),14))
  Red: =gt(days(D2,C2),14)
  
Ashleigh September 2, 2021 At 7:12 pm

Hi there!

I am trying to conditional format the following:

Cell turns red IF the date is 5 WORKDAYS past the date in the column next to it. SOS!

- Prashanth September 3, 2021 At 9:43 am
  
  Hi, Ashleigh,
  
  The following formula highlights cell D3 if D3 + 5 working days is less than the date in E3.
  
  =GTE(E3,WORKDAY(D3,5))
  
  Eg.
  E3: Mon, 6 Sep 21
  F3: Tue, 14 Sep 21
  E3 + 5 working days is equal to Mon, 13 Sep 21
  
  So it highlights.
  
  To apply the same formula to D3:D100, just set “Apply to range” in conditional format to D3:D100. No need to change the formula.
  
  Related: How to Highlight Next N Working Days in Google Sheets.
  
  - Ashleigh September 3, 2021 At 8:03 pm
    
    How does it change if I need it to highlight when it is five days BEYOND?
    
    - Prashanth September 4, 2021 At 9:34 am
      
      Hi, Ashleigh,
      
      You may change the GTE function in the formula.
      
      This tutorial may help.
      
      Comparison Operators in Google Sheets and Equivalent Functions.
      
      If you still have issues, share a copy of your sheet below.
      
      I won’t publish it.
      
      N.B. Replace original content with sample content in your sheet.
      
Rachel July 18, 2021 At 6:25 am

What if I want to set conditions by the date in the cell? For example, if Column A has dates, and I want them rendered conditionally based on that date (if the date has passed, make the cell red, if not, it should be green), how should I do that?

- Prashanth July 19, 2021 At 10:59 am
  
  Hi, Rachel,
  
  Rule 1:
  =gte(A1,today())
  
  Rule 2:
  =and(len(A1),lt(A1,today()))
  
  Choose two different colors for each rule.
  The above rules are for A1:A.
  
  - nadia September 1, 2021 At 9:01 pm
    
    Hello,
    I need help with this type of conditional formatting.
    In my case, I need cells in column S to be yellow if cells in column A have a date and green if cells in S have a date.
    
    - Prashanth September 2, 2021 At 11:47 am
      
      Hi, Nadia,
      
      Apply to Range: S2:S100
      
      Rule # 1 – Green
      =isdate(A2)*isdate(S2)
      
      Rule # 2 – Yellow
      =isdate(A2)
      
      Within conditional formatting, the Green rule should be above the Yellow rule.
      
SH May 14, 2021 At 3:36 am

Hi,

Not sure this has been addressed.

I have a column for dates that are going to be added as items are added to the spreadsheet. What I’m trying to do is conditionally format the column such that the date entered, is compared to today() and colors adjusted.

Let say I have 3 dates. I’d like the formula to compare the cell date to TODAY() and if it’s before the cell date we’re a yellow color. If Today ()= any of the cells then we’re green. IF Today() is after the cell date we’re in red.

I’ve tried grater/smaller than custom formulas; I’ve added a cell with Today() so I can use that cell part of the formula; I’ve tried =Today() -7; and other permutations… no go…Please help

- Prashanth May 14, 2021 At 1:41 pm
  
  Hi, SH,
  
  You require three rules. Assume the cell in question is cell C2. Then you can use the below three formulas.
  
  Red
  
  =and(len(C2),LT(C2,today()))
  
  Green
  
  =EQ(C2,today())
  
  Yellow
  
  =GT(C2,today())
  
  If you have an issue, you may share a sample sheet. I’ll add the conditional format rules for you.
  
Mrs S May 13, 2021 At 7:08 pm

I am trying to turn a row of cells red if the date in the first cell is greater than six months. Can someone please advise how I can do this?

- Prashanth May 14, 2021 At 1:00 pm
  
  Hi, Mrs S,
  
  Cells to turn red: B2:G2.
  The first cell in the range: B2.
  
  Custom Formula for the apply to range B2:G2: =$B2>edate(today(),6)
  
  I hope this helps?
  
Khae May 6, 2021 At 10:28 am

Just want to leave a note of gratitude for being so responsive Prashanth!

CW April 23, 2021 At 4:58 am

Can someone please help?

I am trying to create a rule when the date I have entered has gone 3 days past the date, it will turn yellow. Any advice?

- Prashanth April 23, 2021 At 7:16 am
  
  Hi, CW,
  
  Not clear.
  
  You were saying 3 days past the date ENTERED or 3 days past the current date?
  
Ace April 14, 2021 At 12:40 am

Hey Prashanth, trying to do conditional formatting on a per-row basis in a tab named ‘Parameters’.

One cell in each row of the tab ‘Parameters’ contains a date. I want the entire row to glow red if the date in a row of data in ‘Parameters’ is greater than a date sitting (and won’t relocate) in a cell on another tab I’ve named ‘Variables’, what should my custom formula be in the ‘Parameters’ tab?

View only link to the spreadsheet for reference: — link removed by admin —

Thanks ahead of time!

- Prashanth April 14, 2021 At 8:38 am
  
  Hi, Ace,
  
  You can try the below rule for the “Apply to range” A2:R321.
  
  =GT($H2,indirect("Variables!F2"))
  
  There are already two rules in your’s Sheet. To which format rules you give more priority, that is up to you. Accordingly, drag the rules up and down.
  
Bradley April 6, 2021 At 8:10 pm

I am trying to format a sheet involving filter changes for Air Handling Units.

Some have to be changed every 2 months from the install date. For these, I would like to have them be yellow after 1 month for a check-up then red at the 2-month mark indicating a filter change.

Another format where the color changes at the 3-month mark from the install date for a filter change.

If you are able to help with this that would be great. I am new to using sheets and excel and am doing the best that I can to learn conditional formatting but clearly, I am struggling.

Thank you!

- Prashanth April 7, 2021 At 12:14 pm
  
  Hi, Bradley,
  
  Please share a sample of your sheet. I will be happy to assist.
  
  Note:- You can leave the URL of your sheet via “Reply” which won’t be published.
  
Laurence Alberghini February 23, 2021 At 3:28 pm

Hello,

I would like to highlight cells in a column when the dates in another column are later than today.
What would be the formula?

Thank you

Laurence

- Prashanth February 24, 2021 At 10:32 am
  
  Hi, Laurence Alberghini,
  
  Eg.
  The cell-range to highlight is C2:C1000 (called “Apply to range” inside the format panel). The dates are in the cell range D2:D1000.
  Custom formula (under “Format rules”) to use is;
  
  =gt(D2,today())
  
Shane February 6, 2021 At 6:54 am

This is Awesome! Thank You so much!

- Prashanth February 6, 2021 At 4:06 pm
  
  Hi, Shane,
  
  I have slightly modified the formula in my previous thread. Please change the formula in your sheet accordingly.
  
  Here is a related guide – Find the First and Last Workdays of a Month in Google Sheets.
  
Shane February 2, 2021 At 4:51 pm

Good Day Jason,

In Google Sheets, I would like to conditional format a row to Green when the date is the last workday of the current month.

eg, if today is 02-Feb then highlight the row that shows the last workday, 26-Feb.

The “exact date” feature does not seem to work as per the samples available.

Kind Regards

- Prashanth February 4, 2021 At 10:11 am
  
  Hi, Shane,
  
  I wish to help you with this. Can you share a mockup sheet?
  
  - Shane February 4, 2021 At 11:51 am
    
    Thank you, Prashanth.
    
    — link removed —
    
    - Prashanth February 5, 2021 At 10:27 am
      
      Hi, Shane,
      
      Your date column is the column B.
      
      So select A1:C100 and insert the following formula in conditional formatting.
      
      =int($B1)=workday.intl(eomonth(today(),0)+1,-1)
      
      Your date column contains timestamps. So I have used int($B1) instead of using $B1 in the formula.
      
Jackson January 13, 2021 At 3:38 am

I am working in a Google Sheet from a Google Form. The Google Sheet has the date (timestamp) from the Google Form. I want to have conditional formatting that after 15 days from the timestamp it will turn Red. The only Data I do have is the Timestamp. Thanks.

- Prashanth January 13, 2021 At 8:41 am
  
  Hi, Jackson,
  
  Assume the timestamps are in column A (range A2:A as A1 may contain a label).
  
  Steps:
  
  1. Select A1:A100 (or the range that you want in column A). It’s the “Apply to range”.
  2. Insert the following custom rule in the custom formula field inside the conditional format rule.
  
  =days(today(),int(A2))>14
  
Jason November 27, 2020 At 9:06 am

I don’t know how to use a VLOOKUP table in Sheets over a range of dates.

So if a US President is in office from Jan 20, 1977 to Jan 20 1981 I want to use a VLOOKUP table to say “Carter”

=VLOOKUP(J3, A1367:C1368, 1, FALSE)

- Prashanth November 27, 2020 At 2:51 pm
  
  Hi, Jason,
  
  I’ve added the below formula in cell A3 in your sheet.
  
  =ArrayFormula(IFNA(VLOOKUP(J3:J1349, A1367:C1368, 3, TRUE)))
  
  Actually, I have already a tutorial on this.
  
  How to Vlookup a Date Range in Google Sheets [Sorted/Unsorted Data]
  
Jason November 25, 2020 At 11:55 pm

Dear Prashanth, I was just trying to see if I could do a similar thing for DATE RANGES that I am doing for number ranges?

=IF((AND(A2>0,A2=500,A2=1000,A2=1500,A2<2000),"Between 1500 and 2000")))

- Prashanth November 26, 2020 At 11:20 am
  
  Hi, Jason,
  
  Can you prepare a very basic example sheet and share the Sheet link (URL) via your reply to this comment. So that I can understand it and solve it if possible.
  
  Note: The LINK will be safe. I won’t publish it.
  
Vanessa October 13, 2020 At 9:34 pm

I need help.

I want my column to turn yellow 1 week before the date today in order to just to warn me.

Example:

Today is 13 Oct 2020. everything in Column A must turn yellow 1 week before the date.

- Prashanth October 16, 2020 At 11:12 am
  
  Hi, Vanessa,
  
  Apply to range: A1:A1000
  Format rules: Date is after > Exact date
  Formula: =today()-8
  
  - Anelise February 18, 2021 At 1:31 am
    
    I have the same need to highlight a date in a column to warn me that a permit is expiring within the next week.
    
    I was able to format the expired and not expired permits, but couldn’t set up for the upcoming permit expirations.
    
    I’ve tried all the formulas I’ve found on the internet and none seems to work, please help me.
    
    - Prashanth February 18, 2021 At 8:43 am
      
      Hi, Anelise,
      
      Please check the reply to one of my readers “Isobel Rose Reid”.
      
      If that doesn’t help, please share your sheet (a sample sheet will be sufficient).
      
Sarah July 9, 2020 At 4:49 pm

Hi there,

I have a question. I have a sheet with many due dates across each row for different stages of each project, I would like to highlight the first cell in the row green if any date in the row is today and red if any date in the row is before today.

- Prashanth July 10, 2020 At 1:38 pm
  
  Hi, Sarah,
  
  You haven’t given any range. So let me assume it’s B2:1000.
  
  In this B2:B1000 contains the description and C2:1000 contains the dates. To highlight B2:B based on the said due dates, we can use the two formula rules given below.
  
  “Apply to range” is B2:B1000 (that means select B2:B1000 before clicking the “Conditional formatting” menu)
  
  Custom formula # 1 (red)
  
  =countif($C2:2,"<"&today())
  
  Custom formula # 2 (green)
  
  =countif($C2:2,today())
  
  - Sarah July 10, 2020 At 10:38 pm
    
    Thanks very much! My range with the dates isn’t the whole row, more like this:
    
    H3:I3, K3:L3, O3:Q3, U3:X3, Z3:AA3, AC3:AE3, AG3:AO3, AQ3:AZ3, BB3:BE3, BG3:BK3
    
    I’ve tried replacing $C2:2 with the range above but it doesn’t work.
    
    - Prashanth July 12, 2020 At 4:28 pm
      
      Hi, Sarah,
      
      Code the formula in the following syntax.
      
      =countif({$H3:$I3,$K3:$L3,$O3:$P3},"<"&today())
      
      - Sarah July 13, 2020 At 6:32 pm
        
        You’re amazing. That worked perfectly. Thanks so much!
Ryan Hart June 15, 2020 At 8:45 pm

I hope that you are still taking questions. My function for some reason isn’t working on the conditional formatting between dates.

I’m trying to set up my column E3:E1001 to change orange text with a white background when referencing column L3:L1001 if the date is between 7/27/2020 – 12/12/2020.

Here is the formula I’ve typed =and(L3:L1001>=date(7/26/2020),L3:L1001<=date(12/13/2020))

Please help!

- Prashanth June 16, 2020 At 10:54 am
  
  Hi, Ryan Hart,
  
  There are two issues in your formula.
  
  The first one is the use of the DATE function. To understand the associated problem, see this syntax.
  
  DATE(year, month, day)
  
  Now the second issue is you only need to write the formula for a single cell, not a whole range.
  
  Select the range E3:E and insert this formula in conditional formatting.
  
  =and(L3>=date(2020,7,26),L3<=date(2020,12,13))
  
  It will take care of the entire cell range E3:E.
  
Alex Titley May 19, 2020 At 4:20 pm

Hi,

I’m trying to conditional format a whole column to highlight policy review dates.

I’ve managed to set a single cell (J17) with a date coming up within 90 days with formula =and(J17<today()+90) with 'apply to range' set to J17 but can't seem to get it to work with the Date range J3:J66.

What do I need to change in the formula so it will work for a full date range?

Many thanks
Alex

- Prashanth May 20, 2020 At 2:35 pm
  
  Hi, Alex Titley,
  
  ‘Apply to range’: J3:J66.
  
  Formula: =and(len(J3),LT(J3,today()+90))
  
  Hope this helps?
  
Eric March 23, 2020 At 6:18 am

Hi,

I have a column (Column G) full of dates (a single date for each row).

When 30 days go by, I created a formula to have each date (in the G column) turn red. When that happens, I have to complete a lot of paperwork which takes a significant amount of time. To avoid that, I have the option of calling them one week prior to the 30-day mark.

For example, today’s date is 3/22/2020. In column G, if one of the dates was 2/22/2020, it would turn red. What I would like to do is have it turn yellow between 3/15/2020 and 3/21/2020 as a warning that the expiration date is approaching and that I need to call that person.

So, I need the formula to change the fill color to yellow for that cell when the column date is 7 days before the 30-day mark.

I would like for it to remain yellow for those 7 days until it reaches the 30th day when the fill color changes to red.

- Prashanth March 23, 2020 At 11:45 am
  
  Hi, Eric,
  
  I am considering not 30 days but exactly the same date to today’s date in the last month for the highlighting (Red color). The EDATE function is useful for that.
  
  I mean instead of =today()-29 as one month, considering =EDATE(TODAY(),-1) as one month. Both dates may or may not be the same.
  
  I’ll then find the dates fall in the last week based on that one month.
  
  There are two rules to apply. For red color 1 month mark (not 30-days);
  
  =lte(G2,edate(today(),-1))
  
  For yellow color, 7 days before the one month mark;
  
  =and(gte(G2,edate(today(),-1)+1),lte(G2,edate(today(),-1)+7))
  
  Formulas are for the column range G2:G.
  
  Best,
  
Darren Colclough March 21, 2020 At 4:07 am

I have a sheet with three columns; Item, Due Date, and Status. I would like to color scale the row based on multiple factors. Those are:

1) Due Date = Mar 19
2) Status = Not Started
3) But today’s date is Mar 20
In this scenario, the row would be red.

Next would be:

1) Due Date = Mar 19
2) Status = In Progress
3) But today’s date is Mar 20
In this scenario, the row would be orange.

Next would be:

1) Due Date = Mar 19
2) Status = Review
3) But today’s date is Mar 20
In this scenario, the row would be yellow.

Finally:

1) Due Date = Mar 19
2) Status = Completed
3) Today’s date is Mar 20
In this scenario, the row would be green.

Any help would be appreciated. Thanks

- Prashanth March 21, 2020 At 9:08 am
  
  Hi, Darren Colclough,
  
  Seems this could be achieved easily.
  
  Problem: Highlight rows if due date is less than today() and also meets the criterion in another column.
  
  Solution: Tested Rage is A1:C in which column A contains ‘Item’, B contains ‘Due Date’ and C contains ‘Status’. Status is the criteria column. The first row in this range is the header row.
  
  Select A2:C. Right-click and click on “Conditional Formatting”.
  
  Insert the following formula (you may change the formula as per your locale [comma to semicolon]) in the custom formula field.
  
  =and(isdate($B2),lt($B2,today()),$C2="Not Started")
  
  This is for ‘Red’ color highlighting. Add another rule. In that, just change “Not Started” with “In Progress”. Choose the color Orange.
  
  Repeat this for another two colors.
  
Chris February 28, 2020 At 12:55 am

I have a Google Sheet where I have equipment signed out.
I have a start date when the equipment leaves the office (Column C) and return date (Column D).
I would like to highlight the row if today’s date is in between those two dates. Thus, I could quickly see which equipment is currently out. Any help would be appreciated!

- Prashanth February 28, 2020 At 8:44 am
  
  Hi, Chris,
  
  The formula to highlight if today’s date is between a start date and end date in columns C and D respectively is as follows.
  
  =if(today()>=$C1,today()<$D1)
  
  Select the rows from the first row in your sheet until the number of rows to include, for example, A1:Z100. Then insert the above formula in the conditional formatting (Format rules > Custom formula).
  
Carolyn Benedict February 21, 2020 At 11:39 pm

I’m trying to set up a sheet that has the date a document was received in Column H and the date it’s Due in Column I, with a color change depending upon how far past its due date it is. I know this requires 2 rules to be set, but can’t seem to get the formulas correct.

Example: Recv’d on 2/14/2020 – Due on 2/17/2020 – Current date is 02/21/2020
If the item is 1 day past its due date, the cell needs to turn yellow and if it is 1 week past its due date, it needs it to turn red.

Any help with this would be appreciated!

- Prashanth February 22, 2020 At 9:03 am
  
  Hi, Carolyn Benedict,
  
  Select the column range that you want to highlight. I am considering column I1:I1000.
  
  The first rule for the item that is 1 week past its due date (red color):
  
  =and(isblank($I1)=false,days($I1,today())<=-7)
  
  The second rule for the item that is 1 day past its due date (yellow):
  
  =and(isblank($I1)=false,days($I1,today())<0)
  
Mitch February 12, 2020 At 4:39 am

I’m a bit stuck, trying to apply this to an invoicing schedule.

In Cells G1:S1 I have monthly dates in the form of (01/01/20, 02/01/20, 03/01/20, etc.)

In Cells A2:A20 I have customer names.

In Cells G2:S20 I have either blank cells or a dollar value indicating the amount to be invoiced in a given month (which is indicated by the value in cell 1 of that column)

I want to highlight cells in the schedule where there is a dollar value and also where the date at the top of column is within a certain time frame (orange is date is 30 days or more in the past, yellow if date is within 30 days from today, blue if date is 30 or more days away).

Many thanks.

- Prashanth February 12, 2020 At 3:16 pm
  
  Hi, Mitch,
  
  My answer is based on my understanding of the problem from your query.
  
  Select the range G1:S20 and apply the below formula in the conditional formatting as the custom formula.
  
  =isblank(G1)=TRUE
  
  Color should be set to ‘White’. This is to remove the ‘unwanted’ highlighting of the blank cells by other rules below.
  
  For another three rules, each time select the range G2:S20, not G1:S20. This is an important point.
  
  Highlighting rule for ‘Orange’.
  
  =days(G$1,today())<-30
  
  Yellow.
  
  =AND(days(G$1,today())>=0,days(G$1,today())<30)
  
  Blue.
  
  =days(G$1,today())>=30
  
  See if these rules work for you?
  
Jindy February 9, 2020 At 7:06 pm

Great info, thanks!

I’m still a bit stuck… Trying to highlight an entire row based on a date in column C.

Green if today is before the date by any number of days.

Orange if today is before the date by less than 30 days
(the day is approaching)

Red if today is equal to or after the date

I appreciate any help!

- Prashanth February 10, 2020 At 2:48 pm
  
  Hi, Jindy,
  
  The custom formulas that highlight entire rows in the range A1:Z as per the required approaching/approached dates.
  
  Note: Include only the rows to highlight like A1:Z10 to improve the performance.
  
  Green:
  
  =and($C1>0,days(today(),$C1)>=30)
  
  Red:
  
  =and($C1>0,days(today(),$C1)<=0)
  
  Orange:
  
  =and($C1>0,days(today(),$C1)<30)
  
  Best,
  
  Prashanth KV
  
  - Breanna January 24, 2023 At 1:12 am
    
    Hello Prashanth,
    
    I am unable to get these formulas to work. Could you review my sheet?
    
    - Prashanth January 25, 2023 At 5:59 am
      
      Hi, Breanna,
      
      I’ll try. When you share the file with me, remove any personal details in your dataset.
      
Tiffany December 6, 2019 At 3:14 am

Hi there, I am using this for a form, which captures the timestamp.

If the start date of a contract is within 14 days of the timestamp, how can I get the start date to highlight?

Essentially, I’d like for the conditional formatting to be based on the timestamp date instead of TODAY. Thank you!

- Prashanth December 6, 2019 At 9:20 am
  
  Hi, Tiffany,
  
  Assume A2:A contains the DateTime/Timestamps and the contract start dates are in C2:C.
  
  Select C2:C and insert this formula in the custom formula field of Conditional Formatting.
  
  =and(C2>0,days(C2,A2)<15)
  
  Best,
  
  - Jon Berry September 14, 2020 At 4:12 am
    
    Hello, Prashanth.
    
    I’m trying to do something similar, but I just want to highlight the row if the date field (column G) isn’t the same as the date in the Timestamp (column A).
    
    I tried this formula, but it didn’t do anything:
    
    =and(G2>0,days(G2,A2)>0)
    
    Help?
    
    - Prashanth September 15, 2020 At 12:46 pm
      
      Hi, Jon Berry,
      
      The formula as per my understanding of your problem.
      
      =and($G2>0,int($A2)<>$G2)
      
      (Date in A2 not equal to the date in G2)
      
Lisa November 6, 2019 At 2:48 am

HI. I am curious as to how to apply an “if” statement to a date range.

Example:

I have a date that a second call is needed. I want that date to highlight red if it’s within 10 days (I have to call this person in the next 10 days).

I used “=and($L2>today(),$L2

- Prashanth November 7, 2019 At 10:18 am
  
  Hi, Lisa,
  
  Just try adding one more condition within the AND operator.
  
  Best,
  
Jose Rodrigo November 6, 2019 At 2:32 am

Hi, I would like to make a condition that if “This month” get green. If not get red. Preference, named.

Example: OCT (10/05/2019) is red | NOV (11/05/2019) is green | DEC (11/12/2019) is red. Then… next month the DEC gets green and NOV red.

- Prashanth November 7, 2019 At 9:44 am
  
  Hi, Jose Rodrigo,
  
  Assume the Date value in question is in cell A2. Then, use the following three custom formulas in conditional formatting.
  
  Current Month to Green: =month(A2)=month(today())
  
  Previous Month to Red: =month(A2)=month(today()-1)
  
  Next Month to Red: =month(A2)=month(today()+1)
  
  I hope these custom rules help?
  
  Best,
  
Mike Koopman October 23, 2019 At 7:11 pm

Hello,

Scenario:

My truck delivers to me on Wednesdays. If the day is a holiday, it will deliver the following Wednesday.

So, I need a formula that works backward from a date.

Example:

I need the material for a project that starts on Friday, January 3, 2020. It takes me 3 days to build it, so I need the material in my shop by Tuesday, December 31, 2019.

That would mean that I need the material delivered on Wednesday, December 25, 2019….but the truck doesn’t run on December 25th because it is Christmas.

I can’t seem to put together the formula that would deduct a week if the resulting date is a holiday.

Second scenario:

If the resulting date is January 1st….that would mean that I have to deduct 7 days from that day, and the result is December 25th, so simply deducting 7 days doesn’t resolve the issue.

- Prashanth October 24, 2019 At 10:28 am
  
  Hi, Mike Koopman,
  
  Please follow the below instructions on your Sheet. You may require to make necessary changes in the formula as per your Sheets’ locale setting.
  
  Project start date in Cell D2: Fri, 3 Jan 2020
  
  Material required 3 days before the project start date. In Cell D4 enter this formula: =D2-3
  
  Enter the holidays in F2:F. Here is the Sheet with formula and example.
  
  Example Sheet
  
  Best,
  
- Prashanth October 25, 2019 At 10:30 am
  
  I have updated my previous comment and included an example sheet.
  
  Also here is a guide to understanding part of the formula.
  
  How to Find a Particular Previous Day from a Date in Google Sheets
  
jose October 23, 2019 At 3:54 am

Hi,

How do you create a list of dates on a bi-weekly range? Week starts from Oct 16 next week is Oct. 30 so on and so forth… Thanks

- Prashanth October 23, 2019 At 10:08 am
  
  Hi, Jose,
  
  The following formula will generate 10 dates starting from 16th October 2019 and the date difference (each date) will be 14 days.
  
  =ArrayFormula(to_date(sequence(10,1,date(2019,10,16),14)))
  
  Best
  
Blaine Reed September 30, 2019 At 5:29 pm

Trying to find out if I can format a date so that it shows a different color as it reaches a specific date?

Example:

Termination date = 11/30/2019

Today’s date = 9/30/2019

Given that the date is within 2 months of the termination date, I would like the cell to change to an orange color.

Example 2:

Termination Date = 11/30/2019

Today’s date = 10/30/2019

Given that the date is within 1 month, the cell color would change to red.

Thanks!

- Prashanth September 30, 2019 At 5:52 pm
  
  Hi, Blaine Reed,
  
  Rule # 1: Highlight cell B2 in red if the date in B2 is within one month.
  
  =days(B2,today())<=30
  
  Rule # 2: Highlight the cell B2 in orange if the date in B2 is within two months.
  
  =days(B2,today())<=60
  
  Keep the rule orders as above in conditional formatting.
  
  Best,
  
Jack Harrington April 16, 2019 At 1:16 am

Forgive me if this has been made clear, but I’m trying to custom condition a column so that any date that is two weeks or older is highlighted. Thank you!

- Prashanth April 16, 2019 At 11:04 am
  
  Hi, Jack,
  
  This custom formula will highlight the cells in the range A2:A, if the dates are older than 14 days.
  
  Settings Under the Conditional Formatting Table:
  
  Please set the “Apply to range” field to A2:A and then enter the above formula in the “Custome formula is” field under the “Format rules”
  
  Best,