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

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

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

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

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

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

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

  7. Hello,

    I want to color only fridays..

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  42. Hi,
    i reviewed your all formula but not found any of them as i was looking for.
    can you let me know what is the formula i can use for getting 3 color in one cell. like below.

    yellow color - 10 days early of due date.
    Red color - if the due date has passed.
    Green color - If can meet the due date.

    Thanks /Zaman.

    1. You can use Home>>Conditional formatting >> Manage Rules >> New Rules >> and choose "Format only cells that contain" and choose Cell Value for less than 10 days , equal to 10 days , less than 10 days
      Hope this help to you

    2. You would use the formulas to highlight future dates, but apply 3 different rules to your columns.
      Red would be =$K3=1, $K3-TODAY()<15)

    3. Hi Zaman, im just wondering if you had any luck working this formula out? I am also needing a formula to highlight cells early of due date

  43. ALREADY EXISTING FORMAT - 13-Aug-18
    REQUIRED NEW FORMAT - 13-08-2018
    PLEASE GUIDE US HOW TO CHANGE THIS FORMULA

    1. If this is already formatted as a date, select the cells or column that you want to format, select "Format > Format Cells", select the "Custom" option, and enter the format "dd-mm-yyyy"

  44. I have a Spreadsheet where i have to populate the cells for the arrival date of guests.

    At the top of the Sheet is the Date the report is for. i.e 7 June

    Then further down the columns I populate the names of guests / membership number and arrival date 7 June- which is the same as the date of the report.

    what is the code I can use so that when I change the date of the report it auto populates the arrival date of the fields beside all the guests who are arriving on the same date?

    1. Chris:
      If all the guests' arrival date will be the same date as the date for the report, you can enter the cell address of the cell from the top of the sheet, let's call it cell A2.
      So, in cell B12 where you want to enter a guests' arrival date the formula would look like:
      =A2.
      If you want to enter this address in the first cell that holds names and dates and then copy it down the column lock that address like this:
      =$A$2
      Incidentally, enter the names and dates in separate cells. Don't enter different data types in the same cell.
      You may even want to enter the first name and last name in two separate cells. This way you can search and analyze the records easier.
      Many of the questions here on AbleBits have to do with different data types having been entered in the same cell and now it has to be untangled. All that extra work and aggravation can be avoided by setting up the data entry correctly.
      If you want to get fancy you could create a Master List of all the members' names and then use a drop down or lookup field to enter their names. It should be faster and less prone to error. Oh, yeah, there are several options for lots of possible fun with this data.

  45. Dears
    I would like to make date ranges = Some text or month.
    Example : 5/01/2018 - 4/01/2018 = January
    5/02/2018-4/02/2018=February
    Kindly help me.
    Input two date ranges - Out put required "Text" (Bill of the month of)

    1. Sorry Date Column Singe only.
      Example
      01/01/2018 = December 17
      02/01/2018 = December 17
      03/01/2018 = December 17
      04/01/2018 = December 17
      05/01/2018 = January 18
      upto to
      04/02/2018 = January 18

      1. Khursheed:
        Where 4/1/2018 is in cell K25 and the date you want to compare it to is in L26 the formula looks like this:
        =IF(L26<=$K$25,"December 17","January 18")
        You can change the cell addresses to suit your needs.
        Note the cell that holds the 4/1/2018 is in K25 and in the formula is an absolute reference indicated by the $. This means that as you copy the formula up or down the column the cell to compare the dates to will always be 4/1/2018.
        The formula reads like this, If the value in L26 is equal to or less than the value in K25, then display December 17 otherwise display January 18.

  46. Hi,
    Need Help

    I want to know the expire date of the certificate is the following formula for conditional formatting is correct?

    =AK4>EDATE(TODAY(),3)

    1. Pradnya:
      I think it would work better if you put the value for TODAY in another cell and referenced that cell. For example: =AK4>EDATE(AK3,3) would work better. Where the cell AK3 holds the formula TODAY().

  47. Hi,

    I have a column with tenant names, a column with move in dates, and a column with Lease expiration dates.

    Without adding an additional column, is it possible to have the column with the move date turn yellow 2 months before the 3 years anniversary without the actual move in date changing? I also would like this to happen every 3 years.

    1. Becky:
      Yes, you can do this and some other date related things, too.
      To highlight the move date:
      Select the cell(s) containing the signing date(s)
      Select Conditional Formatting from the ribbon or wherever it is in your version of Excel
      Choose New Rule based on formula
      enter =TODAY()+60 in the field
      Choose the formatting of your choice then OK out and you're finished.
      If you want to see in another cell what the status of your tenant's dates are, enter this in another cell where A42 contains the signing date of the lease:
      =IF(A42<TODAY()+60,"2 Mos","OK")
      If you want to see the day and date their lease expires, enter this in another cell:
      =EDATE(A42,36) where "36" is the number of months from the signing date.
      When you enter all of this in the various cells, your lease sheet should display a lot of info.

  48. I am needing to indicate whether a guests arrival date occurred within 7 days of their booking date. The data resides on two separate sheets (one sheet for the guest name and booking date and another worksheet for the guest name and arrival date.A yes/no answer or the actual # of days between the two dates would be amazing.

    1. Nikki:
      Sheet 1 Cell A1 "Name" Heading
      Sheet 1 Cell B1 "BookDate" Heading
      Sheet 1 Cell A2 is the guest's name
      Sheet 1 Cell B2 is the booking date
      Sheet 2 Cell A1 "Name" Heading
      Sheet 2 Cell B1 "Arrival Date" Heading
      Sheet 2 Cell A2 is the guest's name
      Sheet 2 Cell B2 is the arrival date
      Sheet 2 Cell C2 =DATEDIF(Sheet1!B2,B2,"D")
      Sheet 2 Cell D2 =IF(C2<=7,"Yes","No")

  49. I have a spreadsheet that I'm creating for a friend. They hire out various things and take multiple payments. I would like to highlight the cells net to the date when its the current month EG cell I5 has the date but i would like H5 to change colour when its in the current month the date is entered (DD,MM,YYYY). I'm pretty certain this is possible but every time i try i seem to get 1,1,1900 and it conditional formats to this date

    1. This will work.
      Enter this into H5
      =IF(MONTH(I5)=MONTH(TODAY()),"Current Month","")
      Then in Conditional Formatting window enter
      Cell is equal to
      Then in second field choose Is Equal to
      Then in third field Enter ="Current Month"
      Then choose formatting of your choice

      1. Thank you Doug that works great. Sorry for late reply

  50. I'm trying to compare the difference in scheduled and actual times. So a person is scheduled to report to work at 9:00a but the clock in at 9:05. I need a column to show Late and On time when those two times are compared.

    1. Hello,

      You can use a formula like this:

      =IF(A1>TIME(9,0,0),"Late","On time")

      where cell A1 is “9:05”

      Hope it will help you.

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