Comments on: How to conditionally format dates and time in Excel with formulas and inbuilt rules

See how to apply Excel conditional formatting to dates. Learn how to use formulas to highlight weekends and holidays, format cells when a value is changed to a date, shade upcoming dates and delays, conditionally format dates based on the current date, and more. Continue reading →

Comments page 11. Total comments: 1251

  1. hi is there a way to create a formula to automatically highlight cells when the figures in a certain cells are unchanged after lets say 6 months. for instance if you keep records manually of 20 bank customer's balances and you want the cells of inactive customers (for 6months) with an unchanged balance to change colour automatically. How can this be achieved?

    1. Hello!
      If your table contains data, for example, at the beginning of each month, then it is very easy to determine that 6 digits are the same.

  2. I have used the conditional format cells based off of date using colors Red for overdue dates, Yellow for coming due in 30 days and Green for current. Now that I have all that, Column A is a list of names. I am wanting to know if there is a conditional formula to change the name color to green is all cells in their row are green, yellow if there is yellow cells in their row and red if there are red cells in their row. Is there a way to do this? Thanks for your time.

  3. H, Trying to set up a conditional format for a column of dates, (expiry dates that may need updating). I wish to highlight all dates prior to todays date. ie. each day if there is a date 'out of date' it will turn red.

    Thought I had set it up to do this as above instructions however it is changing other dates to red also?

    Advice appreciated.

    1. Hello!
      Pay attention to the following paragraph of the article above — Conditionally format dates based on the current date

  4. Hello,

    I want to color only fridays..

    2. =(WEEKDAY($A8,15)>1)-1

  5. Hello, I am trying to change the color of a cell when it is coming due, overdue and then when it is current. The date is coming due (orange) the first of every month. The date becomes overdue (red) when it is the 16th of each month if not completed. The date, or cell, is current again (green) once it's completed the first of the next month. Not every cell will be overdue of course, only if a current date is not entered by the 15th of each month. Any help would be greatly appreciated!

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. For each condition, write down a separate formatting rule as described in the article above.
      I recommend reading this guide: Apply several rules to one cell.
      I hope it’ll be helpful.

  6. Hello,
    I am trying to figure out if this date (column P) is within one year of that date (column C):

    Renew date: 7/31/21
    RX date: 5/31/21

    is the RX date within 365 days of the renew date? If not within one year, can it be highlighted in red so I know to stop. I cannot us an RX that is move than one year old (I cannot use an RX from 7/29/20 form example)
    is this possible ?

  7. Hello,

    I am trying to highlight rows where the Implementation Date in Excel column P is past 7/31/2021. I used the formula '=$P2>=DATE(2021,7,31)'. I also converted the Date to its numerical value in a column at the end of the spreadsheet. Conditional Formatting in both cases highlights incorrect rows (before 7/31) and skips rows that meet this specific criteria. Both ways highlights the same, incorrect rows. Do you know why that happens? Please advise...

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. If your date is written as text, then the formula will always return TRUE. Check this.

  8. Hiya! I am a teacher trying to organize whether a student is under the age of 9 years as of "TODAY" so that I can use the checkbox TRUE or FALSE as they'll need testing or not. For example, if Student A is under the age of 9 years old (even by a day) as of today, then checkbox is TRUE. (They will need testing). How can I writ eout this formula prettyt please?

  9. in an excel cell, I set the expired date is 4/30/2022. when the date 4/30/2022 comes, I want the in warranty credit value of another cell to set to zero permanently. What formula could I use? please help?

    1. Hi!
      In this cell, you can use the formula with the IF function with date. Read this article.
      If you need to change the content on the cell that has some value in it, you’ll need to use a VBA macro.

  10. Hello,

    Hi, I'm trying build a recurring maintenance schedule for list of equipment per recommended maintenance matrix within 12 months. maintenance frequency are every 2 months, every 6 months, every 12 months. I would like a conditional formatting formula to color code cells per predefined maintenance duration in days per maintenance frequency stated above within a 12 months period. So far I have column in days that span over 12 months, rows - predefined maintenance duration/maintenance frequency/equipment and task Start and Finish dates per equipment. thanks for your help in advance

    1. Ablebits.com Team - Any luck with my help request?

      1. Hi!
        According to your description it is difficult for me to understand your data.
        To add 2 months to a date, you can use the formula

        =DATE(YEAR(A2), MONTH(A2) + 2, DAY(A2))

        Conditional date formatting is described earlier in this article.
        Give an example of the source data and the expected result.
        It’ll help me understand it better and find a solution for you.

        1. thanks for the acknowledgement. See below example as requested. I tried simplifying with just one Equipment.

          Source Data: Equipment A - Task Duration (Cell C5 - Start date Aug 2 2021, D5 -End date, Dec 31 2023), Cell E5 (Maintenance Frequency – Every 60 days), Cell F5 (maintenance duration – 4 days), Cell G5 (# of personnel to perform the maintenance – 3).

          Expected Result: Under a list of dates that span from Aug 2, 2001 to Dec 31 2023 (a cell representing a day) – Cell H4 to xx4, create a dynamic cells using conditional formatting to show recurring maintenance schedule for Equipment A – every 60 days perform a 4 days maintenance using C5 as start date, next maintenance will be (C5+F5) +60 days. Last day of each maintenance frequency should initiate the 60 days counter for the next maintenance cycle. This sequence should end at day = D5. Only maintenance duration should be color coded and number of personnel (G5) to be inserted each color coded cell.

          Thanks for you assistance in advance.

  11. This was of great helps thanks!

  12. Hi,
    I am looking for a formatting that would highlight cells that are past todays date, but less than 60 days past the expiry then a different colour if over 6 days past expiry.
    if the date was over 60 days ago =red
    if the date expired but is less than 60 days
    the cells have the expiry date in already so if it's past todays date.

    thanks

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. If you have a specific question about the operation of a function or formula, I will try to answer it.

  13. Amazing - just exactly the formulas I was looking for. I know I should keep a book with all these in, but google just seems far easier!

  14. Hello,

    I hope u can help me please
    i looking for a formule to highlight a cell (with a date) 14 days before before that specific date
    example:
    date in that specific cell is 14/02/2021 i would like it to highlight 14days before that specific date without regard to today's date (if that is possible :)
    thank you

  15. hello!

    I have a column of dates (Column A), and a column next to it (Column B) which return the quarter, i'm using the formula: ROUNDUP(MONTH(A2)/3,0), the value given is always 1, if i did not fill anything in column A
    and whenever i fill a date in "Col A", "Col B" will return the correct quarter number
    i need column "B" to be seen empty, or all grey without anything showing, and when i enter the date, and i get a quarter value, the color changes and the font color changes to be seen by the user

    I hope i was clear enough

    thank you!!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(A2<>"",ROUNDUP(MONTH(A2)/3,0),"")

      Create conditional formatting: Format only cells that contain — No Blanks
      I hope I answered your question.

  16. This site is great, but I think I don't see the formatting I'm looking to do.

    In my Personnel Tracker, I track when personnel transfer away and the due dates for products that are needed for them to transfer. I'd like for A) the product column to give me the date 45 days before the transfer date, and B) to turn red when the current date is inside that window (the product is late). Example:

    F3 - date of transfer - 22-Feb-2021
    H3 - Eval due date - "45 days prior to F3"
    As of todays date (4-Feb-2021) I'd like that column to have automatically turned red.

    Please advise as to how you think i could format this. Thank you.

      1. Thank you for your response. Both formulas work for H3; the issue now is that I need to conditionally format all of the cells in column H so they highlight according to their corresponding date entered in column F just like H3 does.

        Please advise as to how to get the cells in column H to turn red based on their corresponding cell in column F.

          1. That ended up confusing me even more... HAHA! I ended up creating a TODAY date cell and running a "Format only cells with less than or equal to that cell. Works great. I have learned a lot though; thank you for your assistance!

  17. In Excel, I need a conditional formatting formula to calculate 5, 10, 15 etc. year anniversaries for quarterly recognition after anniv date has passed. Ex: Start Date 2/1/2016 will be recognized in the 2nd quarter newsletter has having reached their 5 yr anniv. I want the date to format color coded during their "anniv quarter" & then return to black when quarter has ended until they reach the 10 yr anniv & the same thing happens. I can get them to change color for the correct anniversary milestone but can't figure out how to turn them to black after the quarter has ended until they reach the next anniv. I'm using the formula =TODAY()-C1)/365>=5 and it's working but the date stays formatted to the assigned color forever until the next anniversary date. I need it to revert back to black at the end of the anniv quarter. Any advice?

      1. Y'all have a great website! Very informative. Thanks for the link to the article. I think I've read about 20 of them, but can't seem to find the formula I need.

        I have the 5 yr increment anniversary dates color coded & they all work. But I only want them to stay color coded for the quarter that they are in. At the end of the quarter, I need them to go back to black (default) until their next anniv date. When I added a second rule, using the same formula, that set the cell back to normal format and placed it below the first one as you suggested, it overwrote all of the other anniversary date formulas that follow it & turned them to black as well. Even clicking the "Stop if True" box did not help.

        Is there a different formula to use for the "return to default" rule? Is there a formula that will turn off the formatting at the end of the quarter? Thanks, aimee

        1. Hello!
          Please try the following formula:

          =AND((TODAY()-C1)/365>=5, INT((MONTH(C1)+2)/3)=INT((MONTH(TODAY())+2)/3))

          I hope this will help

  18. Hi team,

    How to highlight a date in a cell if it past more than 15 days.For example I want to highlight a cell with date value 01/15/2020.I want to highlight this cell once its past 15 days?

  19. When I am trying to highlight a row on yesterday date using conditional formatting (Formula: "= $A5=Today()-1") in pivot table it Highlights the row of day before yesterday and when applying the same conditional formatting on simple table it is highlighting right row "yesterday date". I don't know why.

    If anybody knows the solution, please respond.

  20. I would like to create a formula that lets me turn a cell red for a date is within 30 days of today. I am making a spreadsheet with different parts that have different expiration dates and I need to have them turn red when they have only 30 days until they expire, yellow when they have 60 days to expire and green when they have more than 60 days to expire. How would I do that?

  21. I have been trying to create a formula based on dates entered in row H (work allocated) I would like the cell "Technician ""B" to change if cell remains blank (no name entered) 14 days AFTER the date that has been entered. cells have been formatted to represent dates displayed in "H"
    g for
    secondly I want to change the color of "c" job paid if it exceeds 25 days after date entered into H

    ive been strugglin some time the formulas seem to count based on current date and not the work allocated date

    thans for any help you can provide, I reference your page frequently

  22. Hi I am looking to conditional format my plan in Excel (I know its probably easier in MS project but where is the fun in that)... I have start and end dates in rows and the week in columns. I am looking to conditionally fill in the week based on the start and end dates in the row.

    Is this possible in Excel?

    thank you

    Sandeep.

    1. never mind..figured it out

  23. I have a spreadsheet of when a patient was seen in the clinic (column G). It also contains a column with their next due date for their appt(Column I) and another for their actual scheduled appt date(column Q)(some are blank in the column)
    I need Column I and Q to highlight in Red if they are MORE than 6 months from column G.
    Since its not based on TODAys dates I'm not sure how to format it.

    1. Hello!
      Follow the guidelines in this section above. Instead of TODAY, use a cell reference with the date you want.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  24. I want to copy text from particular cell based on date

    Say cell A1 represents today Monday 23.11.2020 then in Cell B1 I want text 123690 and so on..

    Like Below table
    24/08/20 Mon 123470
    25/08/20 Tue 123458
    26/08/20 Wed 234569
    27/08/20 Thu 345670
    28/08/20 Fri 145678
    29/08/20 Sat 256789
    31/08/20 Mon 147890
    01/09/20 Tue 147890
    02/09/20 wed 125890
    03/09/20 Thu 123690
    04/09/20 Fri 123470
    05/09/20 Sat 123458
    07/09/20 Mon 345670
    08/09/20 Tue 145678
    09/09/20 wed 256789
    10/09/20 Thu 367890
    11/09/20 Fri 147890
    12/09/20 Sat 125890

  25. On conditional formatting want to set a rule to highlight cells with dates after a cetain date. Example highlight cells with months, in different color per month, if possible, afer 30 June 2019. If not, then highlight dates after 30 June 2019. Thanks. Look forward to your swift response.

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use the DATE function to compare the value in a cell with the desired date — DATE(2019,6,30)
      I hope it’ll be helpful.

  26. Hello Svetlana
    In the excel table I have a column that contains many different numbers from 0 to 3000 or more
    I want to format the numbers. Each number takes the color of a line and the borders of a cell in a color that differs from the other number and is equal to the same format if the number is repeated
    Thank you!

  27. Madam,

    daily weekly monthly activity tracking

    I want to know how to create daily weekly monthly activity details which has to show going expire date and yet to complete within these days(Ex: 2days it has to complete).

    Please am searching answer for this past 3days am not getting answer in the google

    1. Hello!
      Please describe your problem in more detail. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  28. In a column I have incremental dates which is incremented by 3 days from it's previous row. These 3 days are days required to complete any particular task which is independent with each other. At a time I can perform only one task irrespective of sequence. Lets assume the dates are as follows to complete the tasks:
    01-Jan
    04-Jan
    07-Jan
    10-Jan
    13-Jan
    16-Jan
    19-Jan
    22-Jan
    Above dates can be obtained by simply adding +3 to previous cell and drag the formula downwards by this way I can get the date when my work will be finished.
    Now lets consider the activity in row 2 & 5 are complete, the moment I put Done in any cell next cell should automatically updated with previous date + 3 days and accordingly all dates in all column. What will be the formula to auto calculate end date to finish my work in each cell. For example
    01-Jan
    Done
    04-Jan
    07-Jan
    Done
    10-Jan
    13-Jan
    16-Jan
    Pls. suggest

  29. In a column I have incremental dates which is incremented by 3 days from it's previous row. These 3 days are days required to complete the task which is independent. Lets assume the dates are as follows:
    01-Jan
    04-Jan
    07-Jan
    10-Jan
    13-Jan
    16-Jan
    19-Jan
    22-Jan
    Above dates can be obtained by simply adding +3 to previous cell and drag the formula downwards by this way I can get the date when my work will be finished.
    Now lets consider the activity in row 2 is complete, what will be the formula to calculate end date to finish my work. For example
    01-Jan
    Done
    07-Jan
    10-Jan
    13-Jan
    16-Jan
    19-Jan
    Pls. suggest

    1. Hello!
      I don't quite understand your explanations. But you can use the ISNUMBER function to determine if a cell contains a date.

      =IF(ISNUMBER(A1),A1+3,"")

      Hope this is what you need.

    2. Pls. ignore this question, I have send new question for easy understanding.

  30. Hi

    I have a 4 week school timetable (Week A1, A2, B1 and B2) on one tab that links into another tab with the whole staff timetable on it. When the staff initials are added into a cell it automatically shows their timetable. What I am trying to do is highlight in green today's lessons in that 4 week timetable structure so teachers can see which lessons they have each day.

    To help me with this I have a third tab with the dates of each day in each week. So, today, for example, is Wednesday of Week B1. How do I conditionally format the relevant timetable on the '4 Week Timetables' tab to automatically move the colour coding to the respective part of the timetable?

    My logic goes something like this:

    When today's date equals what is on the 'Whole Term Dates' tab, cells d2, d7, d11 (etc.), then make the '4 Week Timetables' tab, cells h8:j8, green.

    I hope this makes sense and that you are able to help. Thanks very much for any time you spend in this. I really appreciate it.

    Regards

    Simon

  31. Hello, I have a sheet where it highlights a products expiry date if it is within 30-60 days of todays date Yellow, Red for 1-30 days Etc. This is set up as a conditional format based on another cell calculating the days. How do I get the formula within the conditional format rules so I can entirely remove the Column doing the calculations to improve the speed of my document? Thank you.

  32. I have a spreadsheet that keeps track training. One requirement is to complete certain parts within 2 years of employees hire date. I have the start date in one cell, under the employee's name and I have a column for date complete. Want to fill the date complete cell with colors depending on if they have gone over their 24 month due date, if they are within 6 months of their due date and when they have a year left on their complete date. I believe I need and If AND formula where I can say if the date complete cell is blank and the hire date is either greater than or equal to the 730 days, 550 days or the 365 days, it fills with the appropriate color. Unable to get my formulas correct to do so. Can you help?

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      Pay attention to this section.
      Please specify what formula you used and what problem or error occurred.

  33. Hello,
    I'm trying to have the cells for column G turn green if there are dates entered in columns E and F. If there are no dates entered I want the cells in column G to be red. If there is one date entered in either cell E or F, I want the cells in column G to yellow.

    Thank you for your help.

    1. Hello!
      To conditionally format a cell with a date, you can use the formula

      =LEFT(@CELL("format",A1),1)="D"

      It returns TRUE if any of the standard date and time formats are applied in the cell.

  34. Hi
    I need help!

    I'm trying to come up with an Excel dashboard whereby the end result will be traffic light.
    The input for this traffic light which to be displayed on the dashboard will be project completion (%) vs it's Go-Live/Handover date.

    The traffic light on the dashboard is meant to tell you whether you are on track or not when the project completion is measured against the time you have left.

    I've tried using several formulas but none worked and I'm no expert in Excel. Hope some of you can help me out here!

    Thanks!

  35. Hello! I am creating a calendar in excel (and trying to avoid using vba, since I know nothing about the programming side of things!). What I am trying to do now, is use conditional formatting to highlight(/grey out) dates not in the current month. I saw on another site this formula:=MONTH(B5)MONTH($B$6), where B5 is the month dropdown list.

    Currently, my spreadsheet has this in it:
    1. dropdown lists for year in B2, and month in C2.
    2. A formula to change the dates based on the selected year and month, which is copied to all the "calendar boxes":
    =DATE($B$2,MATCH($C$2,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),1)-WEEKDAY(DATE($B$2,MATCH($C$2,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0),1),2)

    3. I also have this rule:
    =B6=TODAY() which applies to my "calendar boxes", range B6:H41. It highlights the current day in blue.

    Any help would be much appreciated!

    1. Hello Katie!
      If I understand your task correctly, the following formula conditional formatting should work for you:

      =IF(AND(MONTH(B1)=MONTH(TODAY()),YEAR(B1)=YEAR(TODAY())),TRUE,FALSE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  36. Hi! Can anyone help me with this concern? :) I was trying to find the formula where the cell will be highlighted in the specified colors below when the ff conditions are met:
    EG: The exact date or deadline is July 24, 2020.
    2 days before deadline (Jul 22) = yellow
    1 days before deadline (Jul 23) = light red
    deadline (Jul 24) = red
    overdue (Jul 25 onwards) = dark red

    Is there any way we can do that in googlesheets? Thank you for the help!

    1. Hello!
      I hope you have studied the recommendations in the above tutorial.
      There are answers to all your questions.
      Describe in detail what problem you have, and I will try to help you

  37. DATE :15/07/2020

    S.NO DATE DC NO FINANCE DC NO CUSTOMER NAME VEHICLE MODEL CASH MODE DOWNPAYMENT CHEQUE AMT DO NUMBER

    please help one this. i have mentioned colums in excelsheet. for me if date is more than 5 days if cheque amount not received then it should be popup in red colour otherwise in green color. please send me formula in my email id.
    Thanks

  38. Hi Alexander, Thanks for the guide, Really Helpful!!
    I have a question regarding conditional formatting dates which have passed due, only if the the task is not close or not completed.
    For Info Start Date - Column C, End date - Column D, Task Status Column H, Today's Date - Cell J1, Date format dd.mm.yy.
    Many Thanks In Advance
    Becki

    1. Hello Becki!
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

      1. Hi Alexander,
        Apologies for the vagueness in the original post.

        please see below sample data:

        Task Name Start Date End date Status today's date =TODAY()
        task 1 29.06.20 29.06.20 completed
        task 3 23.06.20 25.06.20 closed
        task 4 01.07.20 04.07.20 at risk
        task 5 04.07.20 06.07.20 in progress

        I want to highlight the end date when it is passed today's date, but only if the task status is not Completed or closed.
        Many Thanks
        Becki

        1. Hello Becki!
          Use this formula for conditional formatting

          =IF(AND($D1 < TODAY(),$H1 <> "closed",$H1 <> "completed"),TRUE,FALSE)

          I hope my advice will help you solve your task.

          1. HI Alexander, If there more to the formula than posted?

            Thanks
            Becki

            1. Hello Becki!
              Use this formula for conditional formatting

              =IF(AND($D1 < TODAY(),$H1 <> "closed",$H1 <> "completed"),TRUE,FALSE)

              I hope my advice will help you solve your task.

          2. HI Alexander,
            Is there more formula?
            Many Thanks
            Becki

  39. Hello,

    I am a case manager and complete needs assessments on a certain example 6/22/2020. I then have to bill on that date each month. How would I create a formula to become red 5 Days before that date each month. As I would want that cell to become red on 7/17/2020, 8/17/2020 and 9/17/2020. But then be a normal color the rest of the month? Does this make sense. Any assistance would be helpful. Thanks- Stacey

    1. Hello Stacey!
      If I understand your task correctly, to make the cell turn red every 17th day of the month, use the formula in the conditional formatting rule

      =IF(DAY(TODAY())=17,TRUE,FALSE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  40. Hello Alexander,
    i have that has column C as start date and column D as end date. i want to highlight the cells from G12 to AD12 which is a two year span but divided in months.

    so based on the day i input in column C start, and D end. it highlights the cell under the month. Note, i am not using days in the highlight section, only months. but using days in C and D.

    i used this formula for days only, but i need days to work with months.
    =AND($G$10>=$C12, $G$10<=$D12)
    C12 and D12 is where i input the start and end dates (days) G10 to AD10 is where the months are located (01-January-2020, 01-February-2020, 01-March-2020, etc.)

    Appreciate the support and thank you in advance.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Поясните подробнее, что означает: "i used this formula for days only, but i need days to work with months."
      Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.

    2. i figured it out, after days of troubleshooting. i made a simple mistake in the code. instead of this =AND($G$10>=$C12, $G$10=$C12, G$10<=$D12)

      and now it works the way i wanted it.

  41. Hello, I have a spreadsheet with forklift certificate training and expiry dates in two adjacent columns. The first column is the date of the training and the second column is the date that the training needs to be refreshed. I would like the second column to highlight 30 days before the date in the cell, every date in this column is different as no two training sessions occur on the same day, so I have 22 rows of different dates in the second column, it is these I would like to highlight 30 days before the date I am an excel beginner so any help would be greatly appreciated

    1. Hello John!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Explain: "I would like the second column to highlight 30 days before the date in the cell". Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

      1. Hi Alexander, thanks for the reply,
        so i have two columns with 22 rows and each row has a different date in it, the date in the first column is when the training occurred,
        the second column has the dates that the training expires, so in column 2 it is the same date as column one + three years (training is valid for three years)
        e.g 1st column has 01/02/19 in the cell
        and the second column has 01/02/22
        What i'd like to have is the date in the second column to highlight one month before the date shown e.g on 01/01.22 i'd like the cell in column two to turn red.
        Basically id like the dates in column two to turn red one month before the dates in the cells to remind me that the date of expiry of the training is approaching!
        I hope that makes more sense!
        Thank you

        1. Hello John!
          I hope you have studied the recommendations in the above tutorial. You'll need to set the conditional formatting rule for column B with the following formula applied:

          =AND(TODAY() > EDATE(B1,-1),TODAY()<B1)

          And choose red as a color to fill the cell with.

          I hope this is exactly what you need.

  42. Hi guys,
    Could you please help with conditional formation for a day occuring (for example 26th) of each month to be highlighted. Well appreciated.
    Also if some one knows how to sort date in pivot table for the Invoice period from 26th of each month. As everymonth is a different number of days, I can't use the period with fixed number of days. Kind of desparate to complete my task.

    Eddy

    1. Hello Eddy!
      If I understand your task correctly,use a formula for conditional formatting

      =DAY($B2)=26

      B2 - the first cell of the date range to be highlighted.

  43. I need a spreadsheet to highlight cells in column c if the cell is greater than 16 and if column a is a Monday, Wednesday or Friday

    1. Hello Melanie!
      If I understand correctly, you can use the formula for conditional formatting

      =IF(AND(C1 > 16,OR(A1="Monday",A1="Friday",A1="Wednesday")),TRUE,FALSE)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  44. Hi - I have 2 date columns (E baseline date) (F revised date) how can I set formatting that if (F) is equal to (E) green; if (F) is 15 days over then amber; if (F) is 30 days over then red;
    I have used 'AND' and 'IF' formula and I get partly cells in (F) green and nothing else.

    Help please!

    Thank you

    Sara.

  45. Please help! I have a sheet where a particular item expires every 2 year. What I'm trying to do is have the conditional formatting come into effect when the 23 month marks hits; and I have no idea if this is even possible. The original date is in the format dd-mmm-yy and is starts in the F4 spot. Any help would be much appreciated. Thanks!

    1. Hello Jeff!
      Please go to Conditional Formatting- > New Rule -> Use a formula to determine which cells to format and set the following formula for column A:

      =EOMONTH($A1,23) > TODAY()

      Hope this is what you need.

  46. Thank you, I was able to combine and redefine your formulas! You made my life much easier.

  47. I would appreciate it if someone can assist me with a date formula :
    I have 2 cells A1 and A5
    In cell A1 is todays date then Cell A5 should shows current month and Year if todays date is between the 1st and the 15 of current month but if todays date is between the 16 and end of month Cell A5 should change to next month and related year.
    For instance
    A1 20/04/2020 then A5 should be
    May20
    If A1 14/04/2020 then A5 should be Apr20
    Any help would be highly appreciated

    Leon

    1. Hello Leon!
      If I understand your task correctly, the following formula in cell A5 should work for you:
      =IF(DAY(A1)<=15,A1,DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
      If it is necessary to change the date format without changing the date itself, Custom Format in A5 should be used. Please go to Format Cells, choose Number -> Custom Format and set "mmmyy"
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  48. i have a table for security report with date 1 as starting date and the whole month is triggered by change of month. i used date value and EOMONTH FORMULAS TO ESTABLISH THE CALENDAR. So each date relies on previous date because there is a formula. above the cells is names of days from Monday to Sunday which changes when i change the month because the boxes have formulas too but i format them to "ddd" only to show day name. for this am unable to highlight weekends with conditional formatting for "sun" & "sat". how can i make it.

  49. I need help. I have a start date in column B and end date in column C. Column E to P are the months of the year. I want column E to P to reflect the start and end date with colour blue, for eg if i hv January in column B, and June in column C, then only columns E to J turn blue. How should I do it? Am totally new with Excel!

    1. Hello Flora!
      You may use Conditional Formatting- > New Rule -> Use a formula to determine which cells to format and set the following formulas:
      For column E:
      =$C1 > DATE(2020,1,1)
      For column F:
      =$C1 > DATE(2020,2,1)
      For column G
      =$C1 > DATE(2020,3,1)
      etc.

  50. I have a spreadsheet with a column that is a date format. I am needing a way to set a conditional rule for invalid dates ie 09/31/2020 to highlight the cell red.
    With this I need it to trigger if the data is copied and pasted from one sheet to another or a way once I have all the data in the new sheet that I can run a macro to trigger all the rules on all the cells.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)