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 4. Total comments: 758

  1. I have a training spreadsheet that I need to have recognize training expiring next month, this month, and expired either in previous month or months. I know can do dates occurring for this month and next month but need more then one month back. I can't remember the formula to highlight something over 30 days past.

    1. Hi!
      Have you carefully read this blog post? It contains answers to your question.

      =AND(TODAY()-$D2>=0,TODAY()-$D2 > 30)

  2. hi,
    i'm new to excel, i'm trying to figure something out. i have a list of people who need to do recurrency training every 3 years. i have the date they did las training and used a formula to automatically fill in +3 years. what i want to find out how to do is, i want the cells to be green if their training is current, yellow 6 months before training is due and red if the date has passed. can you help me with this please?

    1. Hello!
      To write down a conditional formatting formula, check out this tutorial: How to add and subtract dates, days, weeks, months and years in Excel.
      Create rules with these formulas:

      =DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2)) > TODAY() yellow
      =DATE(YEAR(A2) + 3, MONTH(A2), DAY(A2)) > TODAY() red
      =DATE(YEAR(A2), MONTH(A2) + 6, DAY(A2)) < TODAY() green

      I hope I answered your question. If something is still unclear, please feel free to ask.

  3. I have a row with a cell dedicated for each day of the month. I'd like to average the sums in the cells based on the current date. The average should include the amounts of all cells from days in the past (including empty ones) and exclude the cells with dates in the future.
    As an example: Let's say that it's the 4th day of the month and two cells contain 10 and two cells are empty. We know the average is 5 but using =IFERROR(AVERAGE(B5:AF5),0) forces me to input a "0" in cells dated in the past.

      1. Today is the 5th day of the month. Day 3 of the month (cell D5) contains 126.53.
        Following your lead I tried using =AVERAGEIF(B5:AF5, "<="&TODAY(), B5:AF5)
        That formula returns 126.53 because there is nothing else to average unless I input "0"s into cells B5, C5, E5, and F5.
        How can I tell Excel to output the average 126.53 based on the day of the month (without using zeros on the days gone past)?

          1. =AVERAGEIFS(B5:AF5,B4:AF4,""">=12/31/2021",B4:AF4,"<="&TODAY()) shows some promise but results 126.53 for an average of 5 days when it should result 25.31. (If I use zeros in the other 4 cells the correct average results.)

            1. Hi!
              To take into account empty cells in the AVERAGEIFS formula when calculating the average, try the formula

              =SUMIFS(B5:AF5,B4:AF4,"> ="&DATEVALUE("12/31/2021"),B4:AF4,"< ="&TODAY())/COUNTIFS(B4:AF4,"> ="&DATEVALUE("12/31/2021"),B4:AF4,"< ="&TODAY())

              I hope it’ll be helpful.

            2. Correction to above:
              =AVERAGEIFS(B5:AF5,B4:AF4,""">=12/31/2021",B4:AF4,"<="&TODAY())

  4. i have a spreadsheet with a list of employee's and their different types of training. i have used conditional formatting to highlight the cell in yellow when the expiration date is 30 days away and red on the expiration date. how do i get the cell to stay red then change to no background color until a new expiration date is entered ?

    1. Hello!
      Create another conditional formatting rule that will take effect as long as the date cell is blank.

      1. I am using a very large spreadsheet with names, dates, merged cells. I made a column selection that holds mostly date values but also names. How can I conditional format the columns that it only changes the date cells to a certain color?

        I am using =(beginning cell)<=TODAY()-365 but blank cells and name cells are also being modified. I want it to affect only date cells.

        1. Hello!
          Try adding a CELL function to the condition, which will determine the format of the cell.

          =AND(LEFT(CELL("format",D1),1)="D",D1 < = TODAY()-365)

          I hope it’ll be helpful.

  5. Thank you for this amazing support and guidance
    I am trying to use a yearly date range. For example, I have employees who have completed First Aid Courses and I need a reminder as when the qualification will expire onto a training matrix
    Would this be possible. (I have over 200 employees so impossible in Calendar.
    Thank you in advance

    1. Hello!
      If the validity period expires, for example, after a year, then the conditional formatting formula can be applied to the cells in which the date of completion of the courses is written:

      =D2+365>TODAY()

  6. I have a question. I have a certification grid using values linked to another spreadsheet. I have those linked correctly, but what I now want to do is create a conditional format for the date value in the cell +650 days (because it is a certification of every 2 years). This will tell us when they are 90 days away from needing to recertify, where the cell value is the date they last certified.

    Ex. Bill certified 1/20/2020, I want to change the color of the cell so that 650 days after that date, sometime in October, the cell will turn yellow indicating they are coming due for recertification. Then another condition for red cell, when they are 30 days away (700 days after).

    Is this possible with the current rule sets in Excel?

    1. Hello!
      I hope you have studied the recommendations in the tutorial above.
      Use conditional formatting formula -

      =D1+650

  7. Hi, I have a gantt chart that I have a date (say 7/10/2021) in col E and the number of day (say 5) in col F. I need to have the number of days highlighted in different colours depending on the category (say painter, green highlight) in col C - with the start date on the date entered in col E. I can't seem to figure out the formula for the conditional formatting rule to achieve this. Are you able to assist?

    1. Hello!
      You can learn more about conditional formatting based on another cell value in Excel in this guide. I recommend watching this video.
      I hope I answered your question. If something is still unclear, please feel free to ask.

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

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

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

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

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

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

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

    1. Actually, I think I figured it out:

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

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

      Thanks

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

  16. Hello,

    I want to color only fridays..

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. 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. Pls. ignore this question, I have send new question for easy understanding.

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

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

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

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

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

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

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

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

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

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

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

  43. Please help (what am I missing)?
    I have a spreadsheet to track my time. I am trying to highlight a column based upon the current day.
    My dates cover 2 weeks and go across with the hours worked listed in cells below. My dates are formatted "dd" (i.e. for 11/5/2019 is shows 05). The dates are in row 2.
    I used conditional formatting ("use a formula to determine..." ) with the following formula: (I have selected E2:R33 area) E$2=today() and then I have selected the format of a yellow fill color.
    It is not working and I cannot figure out why? What am I missing or doing incorrectly? (NOTE: I have tried reformatting the date and even entering in today's date and still does not seem to work.)T.I.A.

    1. Try $E2-Today()=0

  44. I am having an issue that I cannot find the answer for:

    I am formatting my cells to display color simply by TODAY() date (Red for ).
    The problem is, it isnt changing colors with the date. IF it does change color, the date is wrong (itll highlight RED when the date is clearly several months in the future or vise versa).
    Date Calculation is set to Automatic, Number drop down is on GENERAL. It just wont do anything at all. Current format is mm/dd/yyyy.

    Can somebody ANYBODY, tell me what I am doing wrong?!

    1. Hi jon, did you receive a response or discover a solution to your problem? I am also having the same issues.

  45. I have a spreadsheet with a Expired due dates in one column . I'm trying to get the cells to turn yellow when its 60 days before the due date , and turn red when its expired .
    My expired due dates are in column G .
    Name Course Expired /Due date
    Jane Doe Annual Training 26/09/2019
    John Doe Orientation 01/02/2020

    Hoping you can help me. Thanks in advance .

    1. Dear all,
      Hope my all friend everything, but i am not ok, we are facing in problem in excel file format issue, please help me about this, Problem: one cell have amount date wise color change issue, how can it's possible, plz help me friend,,,

      Kamal
      From Bangladesh.

  46. Is there a way to copy conditional format to each row.
    I have a spreadsheet which shows date someone last attended I wanted it to go one colour if the date is between 180-269 days, highlight a different colour if date is 270-364 and a different colour if more than 365

    I managed to do it on one line but when I tried copying it it used the data from first line

    1. Copy the cell with the conditional formatting you want and use the Format Painter to paste the format to the cells where you need the conditional formatting.
      If it's a lot of cells, select the block of cells and then click manage rules in the conditional formatting tab.
      Make sure it's set at "for this worksheet" and not "current selection".
      Click the up arrow next to the conditional formatting you want to apply and select the cells in the worksheet in which you want the conditional formatting to appear. Click apply and ok.

  47. Hi -
    I am trying to format a cell that if the date in the cell is after todays date and after cell A17, it should highlight. Any ideas?
    Thanks in advance!

    1. were you able to figure this out? I am looking to do the same thing.

      Thank you!

  48. Hello,

    How do I change the color of a field when it's date is "overdue" based on the date in another cell. Let's say the date in B2 is 12/3/18 and a field is automatically populated to show a due date 7 days away in B3 is 12/10/18. How do I get that cell (B3) to turn red if the current date is equal to or pass that date (showing that it is now late). The date in B2 will never be the same.

    I hope that makes sense.

    Thank you

    1. Hello, Del.

      If you need to set a conditional formatting rule to change the cells' color based on another cell value, you may find this article helpful.

  49. Trying to make a conditional format based on a cells value of the date that has been inputted into that cell and have program shade yellow for +5 years beyond that date and then red for +ten years beyond that date. Example would be if I input 10/12/2015 in E4 I would like it to shade yellow at +5 yearson 10/12/2020 and then red +10 years at 10/12/2025.
    I would like to ba able to do this for any date that I input since I will be inputting various dates into my cell in column E.

    1. Hello,
      It looks like the following steps will do the trick for you:
      - Select the necessary range and go Conditional Formatting -> Manage Rules -> Add new.
      - Choose the "Use a formula to determine which cells to format" option.
      - Apply the formula like the one below:
      =YEARFRAC(F2, $E$4) >= 10 and =YEARFRAC(F2, $E$4) >= 5

      - Set the necessary color and hit OK.

      Hope this is exactly what you need.

    2. Hi Did you manage to find a solution for this? I have a similar problem trying to identify employees 5 years in the business 10 years in the business etc etc

    3. I need to do exactly this - were you able to find a solution?

  50. Help!!!!

    I’ve created a timeline in excel and want to have the conditional format fill in the color based on the start and end date. The headers I have are for the start of the week. Example the top header would show 10/22/18, 10/29/18, 11/5/18.

    And my Start date for a task would be 10/22/18 and end date of 10/31/18. So I need the cells under 10/22/18 and 10/29/18 to be highlighted. How can I do this?

    1. Hey Rosii,
      Did you get figure this out? I need to do a similar function and I am struggling.
      I basically have three columns: Allocation, Start and End Dates. So, If I choose a start date as 01/01 and End date as 06/01 at an allocation of 100% , then I need a function that will allocate 160 hours in six columns from Jan to Jun.

      Please help!!

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)