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 10. Total comments: 1245

  1. I am trying to use conditional formatting to change the colour of a cell if the time is over 4 hours, 5 hours, 9 hours for the spredsheet to correctly determine break eligibility. I've currently got the cell F4 calculating the difference in time (E4, D4), and when i go to format G4 to change colour depending on the value in F4 it wont recognise the value as it is in Time format. is there a way around this? Happy to provide screenshots.

  2. Hi there,

    I reconcile spreadsheets monthly. A report is generated the 3rd friday of every month.
    17-Sept-21 is the last date of the report. It is in cell D3. This number will get updated every month.

    Now I have 30 spreadsheets to reconcile. I don't always get to them every month.

    I want to used conditional formatting. I really would like the Icon set to appear in the Cell D beside the last reconciled date in that cell for that spreadsheet number.

    green check if is is within 30 days
    yellow ! if is is 60
    red X if it is 90.

    I had it on friday then my spreadsheet crashed and didn't auto save and I can not figure it out again.

  3. Hi

    Thank you so much your all your conditional formatting articles helped me a lot. I didn’t find an answer to only this question:
    I have a teacher working in two branches can we combine two conditional formatting rules (duplicate n between) or any other formula to prevent me giving the same time slot or in between times to same teacher in a different branch

    Ex br 1 Mr.X. 13-17

    br 2. Mr.X. 13-17 or in between times likes 15-16 ( a colour to indicate the mistake) since during that time he’s already working.

    Thank you

  4. Hi Alex,

    I hope you can help me with the following issue:
    In my school, I have created a form in which teachers can use to fill out when a student has detention. Teachers have the ability to click on the first column to select a date, put the student’s name on the next column, and so on.
    In the past, I’ve made it so that if a certain number of students are put on the list for a given day, the row turns red. Our detention room only has 12 seats, so I didn’t want teachers assigning more than 12 students each day. So, when a teacher selects the same date on a 13th row, the whole row would turn red, which they would automatically know that the detention room is full and same that entry for the next day.
    The problem I’m having is that I forgot how to create this again. Someone messed with my settings and I can’t remember how to duplicate this again.
    How can I get this done??
    Thank you!!

    Victor

    1. Hello!
      This formula for the rule of conditional formatting highlights the color of all values, the number of which in the column is greater than 12.

      =COUNTIF($A$2:$A$1000,A2)>12

      I hope my advice will help you solve your task.

  5. Hello!
    Thank you for sharing...great article!

    How do you use conditional formatting in identifying date and time conflicts? Date in one column and time (24:00 format) in another column?

    Thank you!

  6. Hi,
    How do I put conditional formula for time.
    Eg : if the Time is 10:09:33
    then I want to put conditional formula as if time is before 10am or after 10 am..
    Please help

  7. Excellent blog!!! It was very helpful and was able to solve few of the problems using the techniques listed here. Thanks a ton and keep sharing your knowledge.

  8. I've tried running the "How to highlight dates within a date range" formatting but I'm finding that the last date in the range is coming up in the wrong colour. So for instance I want under 10 days listed red, 11-15 in orange and anything over 15 days green, however I'm finding that the formula works except for the last one in the list. For example I'll have 5 27.08.2021 then 4 31.08.2021. The first four of the 27.08.2021 change to the right colour but the 5th one is getting assigned the colour of the 31.08.2021. Hope that makes sense!
    Any suggestions as to what I am doing wrong?

  9. Thank you Alexander for ALL of your help! I do have one more question. If I used the "today" format, and I want it to calculate 1 year AND 1 month from then, would I use 395 instead of 365 at the end of my formula?

  10. I’m building a gantt chart and I’m trying to add an icon in conditional formatting to represent my milestones but I have not had any luck is there a formula that I could use to get it to work??

  11. Morning,

    I am trying to create a project management calendar. We have start and end dates for certain tasks (8/16/21 start date and 9/17/ 21 end date). Task description is listed in column A, start date in Columb B, and then end date in column C (all listed in row 3 and under, depending on the amount of task). In D-AA we have a "Calendar" set within row 2, of every Monday for the next year.

    The goal is to conditionally format the cells in column D-AA and under row 2 (so row 3 and under) to highlight a color (i.e red) during the date range. So every task will have a different start and end date aka the colors will change in every cell between D-AA

    1. Hello!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  12. Hello,
    I need to highlight cells based on a date within the cell NOT todays date. I want a cell to highlight based on 30 days from the day in the cells within a column.

    Example: I want to have each manager over their department know when their employees annual renewals are due. I would like it to be based on JUST the month and the day NOT the YEAR since that year is listed by their original hire date. My plan is to take the year out listing only the month and day so that if it is 30 days past that date it will turn red

    1. Hello!
      Use the formulas from the paragraph - How to highlight dates within a date range.
      Use the date cell address instead of the TODAY function.
      If this is not what you wanted, please describe the problem in more detail.

      1. Alexander,
        Thank you for responding. This is not what I need as I explained previously, I am looking for dates that are 30 days away from the date that is in a cell. That is why I explained that I cannot use the "today" function. I do not have 2 dates listed so I cannot use a "date range" function, I don't believe.

        Example:
        Column A Employee #
        Column B Employee Name
        Column C Years Employed
        Column D Date of Hire MONTH AND DAY ONLY
        Column E Job Code

        I am looking for Column D to change a color when it is 30 days past the date entered in that column.
        Also I want the format to look and ONLY a MONTH and a DAY I do not want the year to be part of this as I believe it will not work since our employees tenure ranges from 1 year to 40+.
        I am VERY green when it comes to Excel so could you give as much detail as possible? Please and thank you!

        1. Hi!
          How do you want to define 30 days if you only have a month and a year? If the date is only a month and a day, then it is written not as a date, but as text.

          1. So, there has to be a year attached?
            If so, I will use the current year but, I want the 30 days to be 30 days PAST the day that is in each cell located in Column D.

            For instance if D says 8/21/21Then by 9/14/21 I would like that cell to be red

            1. Hello!
              If the date is written in column D, then you can use the recommendations from this article above. In the formatting condition, instead of the TODAY function, use a cell with a date

              =F1-D1>30

              I hope my advice will help you solve your task.

              1. Alexander,
                I'm sorry but, would you give me step by step instructions on how to insert this formula? As I explained, I am very green with spreadsheets. What is f1? Is that assuming I am using another column? Also, is this a conditional formatting formula? Does it go in a specific box?

              2. Hi!
                In the formula

                =$F$1-D1>30

                F1- the date from which you will compare all cells in column D.
                How to create and use a conditional formatting formula is detailed in this article above.

  13. Hi,

    I would like to simply highlight any cell with a date of today or older - older than a month too

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

  14. Looking to highlight months 1, 2 and 3 months out, so ex I would like a cell to highlight red if its says JUL and it is currently JUL and highlight Yellow If the cell says JUL and it is JUN, so highlighting cur month as well as the 2 months prior to end of current month, the formula set up now is broken and I can not figure it out

    =MONTH(EOMONTH(TODAY(),3))
    =MONTH(EOMONTH(TODAY(),2))
    =MONTH(EOMONTH(TODAY(),1))

    I am getting 0 highlights and I am unable to get it working

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

      =MONTH(A2) <= MONTH(EOMONTH(TODAY(),1))

      I hope it’ll be helpful.

  15. Hi I'm trying to show a RAG to a cell if a date scheduled was achieved or not. I understand how to add a conditional format however I have to make a new rule for each cell in the column. For example in column A the dates are different in each row in column B a formula is used to show the date due and then in column c the actual date completed is inserted. I would like to highlight if this matches the due date using RAG and can only achieve this if I add a new rule to each cell in each row in column C. I'm looking for a way to copy the rule into each of the cells that will auto generate column C to look across the row at cells, c1 to look at b1, c2 to look at b2 and so on.. pls help!

  16. Hi,

    I have two columns. One contains starting date and another contains exact 6 months from that starting date(Note: few dates are in the past). Now I want to highlight the dates which are greater than today. How to do that?

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

  17. Hi. Thanks for these examples. I am trying to highlight a cell based on two criteria:
    1. If the date is within 15 days of today
    2. If the cell in another text is "TBD"

    This is what I have but it isn't working:
    =IF(W2=TODAY()<15,IF(K2="TBD",1,0),0)

    Am I missing something or do I have something written incorrectly?

    Thank you

    2. Actually, I think I figured it out:

      =IF(W2-TODAY()<15,IF(K2="TBD",1,0),0)

      I just changed the "=" to "-".

      Thanks

  18. 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.

  19. 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.

  20. 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

  21. Hello,

    I want to color only fridays..

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

  22. 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.

  23. 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 ?

  24. 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.

  25. 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?

  26. 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.

  27. 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.

  28. This was of great helps thanks!

  29. 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.

  30. 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!

  31. 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

  32. 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.

  33. 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!

  34. 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

  35. 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?

  36. 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.

  37. 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?

  38. 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

  39. 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

  40. 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.

  41. 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

  42. 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.

  43. 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!

  44. 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.

  45. 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

  46. 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.

  47. 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

  48. 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.

  49. 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.

  50. 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.

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 :)