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 27. Total comments: 1244

  1. Hello Swetlena,

    Is there any way in which I can use Red & Green coloue together in one column to reflect the expiry date.

    The green colour fades more towards the red colour as the expiry date approaches?

    Regards,
    Akhil

    1. Hello Akhil,

      You can select the range with dates and create a conditional formatting rule with the "2-Color Scale" format style. Select green color for the minimum value and red for the maximum value, choose to use formula for the maximum value and refer to the cell with the expiry date, e.g. =$B$11.

  2. Hi Miss Svetlana,

    I have a problem on my report. My Boss want to see the my data and highlighted using conditional formatting for the clients have no record or data for past six months and my report was format by month. I hope you can help on this.

    Thank you.

    1. Hello Adrian,

      When you create a conditional formatting rule, choose to "Use a formula to determine which cells to format" and try the following formula:
      =A2>EOMONTH(TODAY()-210,0)+1

  3. I have a problem with conditional formatting "greater than value".
    I'm trying to create a new rule wherein the values are results of time difference and of "if" formula.
    This is how it goes. =if((7:05 AM-7:00 AM)>0,(7:05 AM-7:00 AM),"0:00) but the formula bar contains =if((K11-M11)>0,M11-K11,"0:00")
    The answer should be 0:05 mins and I tried conditional formatting it.
    The conditional formatting rule is like this;"Cell Value>0:00","AaBbCc"(this are formatted in red font color),Applies to cells from "=$O$11:$O$16".
    The answer in from the formula bar should turn red(0:05) and it did, except the problem is it turned all the other cells answers (0:00) into red.

    1. Hello,

      You can use a simple formula =M11-K11 first and set the cell format to time -> 13:30. This way you will see minutes in the format you need. Then create a conditional formatting rule based on the following formula:
      =MINUTE(M3-K3)>4
      It will highlight all values that are over 4 minutes.

  4. Hi Svetlana
    If training dates are entered within a range of cells for the current month (if we are in Oct, then all dates will be October dates), and all cells are formatted so that dates entered are red and look like dd/mm/yyyy, how can I conditionally format the red dates for October to change to black 5 days into the new month so that any new November dates entered at the start of the month remain red, while the rest change?
    Essentially, we need a gap between the end of the month and when the previous month's dates change so that we can send reports of previous month's activities.
    Would really appreciate your help with this.
    Thanks :)

    1. Hello Lynette,

      Please try to create a new conditional formatting rule, choose to "Use a formula to determine which cells to format" and enter the following formula:
      =OR(MONTH(A2)=1, A2<=EOMONTH($A$2,0)+5)

      Please replace column A with the column where you have the dates.

      Click "Format" to change the found dates to black font and click Ok.

  5. Hi, I have random dates of the year in a column now I want to highlight the higher/last date of each month but not able to find any condition need help.

    Ex-
    8-Feb-15
    9-Feb-15
    10-Feb-15
    20-Apr-15 (Highlight this)
    1-Mar-15
    2-Mar-15
    10-Mar-15 (Highlight this)
    17-Apr-15
    18-Apr-15
    1-May-15
    5-May-15
    6-May-15
    8-May-15
    9-May-15 (Highlight this)
    10-Jan-15
    11-Jan-15
    24-Jan-15
    25-Jan-15
    26-Jan-15 (Highlight this)
    8-Feb-15
    11-Feb-15
    24-Feb-15
    25-Feb-15 (Highlight this)

    1. Hello Bhardwaj,

      You need to create a conditional formatting rule, choose to "Use a formula to determine which cells to format" and enter the following formula:

      =IF(C2<>"",LOOKUP(2,1/(C2-DAY(C2)=C$2:C$100-DAY(C$2:C$100)),C$2:C$100)=C2,FALSE)

      Here C2:C100 is the range with your dates.

      Select format for the cells and click Ok.

  6. I need to calculate the days between a start date (A1) and today but to stop calculating when an end date is entered in cell (A2) so (A3) will show how many days have passed but when I open the file after the end date I don't want the calculation to continue going forward.

    1. Hello Dale,

      Please try one of the following formulas in A3 to calculate the number of days between the start date and today, or between the start day and the end date if it is entered in cell A2:
      =IF(A2="";TODAY()-A1; A2-A1)
      Or
      =IF(ISBLANK(A2); TODAY()-A1;A2-A1)

  7. HI,
    HOw to print the current date in a cell based upon the change in the color of the cell?

    1. Hello Pruthi,
      If we understand your task correctly, you can use the following VBA code that one of our developers wrote for you:
      - Open your Excel file;
      - Press Alt+F11 on your keyboard;
      - Double-click the sheet name in the list and paste the code below, but change the reference to your data range and the cell with the necessary color:
      Private Sub CommandButton1_Click()
      Dim color
      Dim i As Integer
      Range("J2").Select
      color = Selection.Interior.color
      Range("F2:F11").Select
      For i = 2 To 11
      Range("F" & i).Select
      If Selection.Interior.color = color Then
      Selection.Value = Date
      End If
      Range("G" & i).Select
      If Selection.Interior.color = color Then
      Selection.Value = Date
      End If
      Next i
      End Sub

      - Click Run

  8. Hi Svetlana,

    I want to use conditional formatting to show (via color highlighting) if a date entered into a cell is within the current calendar year (at any time the spreadsheet is opened). Can you tell me the formula I should use?

    Thanks!

    1. Hi Kevin,

      You can create an Excel conditional formatting rule based on a formula similar to this:

      =YEAR(TODAY())=YEAR($A1)

      Where A1 is the cell containing your date.

  9. Hi I am a total loss here

    I have a column (D5) with Certificate Test/Start dates (YYYY/MM/DD) I then have a column (E5) with months eg. 3,6,12,24 etc, these indicate the months before the certificate needs to be "retested".

    These month values are variables so I can't hardcode a value like Cell Value less than =NOW()-365 (This is for a year validity certificate) and =NOW()-(E5*30.5) meaning month x days, this doesn't work or I am just screwing up the format.

    I want to do a conditional format on D5 that uses E5 to flag D5 as RED (expired), YELLOW (30 days before expiry) and GREEN (still valid)

    Can anyone please help :(

    1. Hi Nick,

      You can use the following formula to calculate the expiry dates:
      =DATE(YEAR($D5), MONTH($D5)+$E5, DAY($D5))

      And than you can create 3 rules with the following formulas based on the expiry date (say in column G):

      Red:=$G5-TODAY()>30

      Yellow:=AND($G5>TODAY(),$G5-TODAY()<=30)

      Green: =$G5-TODAY()>30

      If you don't want an additional column with expiry dates, you can replace G5 in the conditional formatting formulas with the Date function, e.g.:

      Green: =DATE(YEAR($D5), MONTH($D5)+$E5, DAY($D5))-TODAY()>30

  10. Hi,

    I have a date, order #, and arrival date columns

    is there a way to highlight the arrival date column if it is empty after 24 hours from the respective order date?

    For Example:
    order # 3 is placed on 10/04/15 and arrival date is not mentioned until 10/06/15 then it should be highlighted in a different color on 10/05/15

    1. Hello Lijina,

      Supposing that the order date is in column A and arrival date is in column B, and row 2 is the topmost row with dates, select the arrival date column (B) beginning with row 2, and create a rule with the following formula:

      =AND(TODAY()-($A2+1)>0, $B2="", $A2<>"")

  11. Hello,
    I want to highlight an entire row in which only one cell (H2) contains a date. I would like my whole row to be highlighted red when the date in this cell(H5)is two days away from today (coming up in 2 days).

    Thank you very much

    1. H2 again sorry, not H5

      1. Hello Nicolas,

        Select the entire rows that you want to highlight beginning with your first row with dates, e.g. A2:I100. And create a rule with one of the following formulas:

        Date in H2 is exactly two days away from today:
        =$H2-TODAY()=2

        Date in H2 is 1 or 2 days away from today:
        =AND($H2-TODAY()>0, $H2-TODAY()<=2)

  12. Hi,

    I am trying to format cells so that if one date is entered it will be yellow, if two dates are entered it will become orange and if three dates are entered it will become green. Dates (Uk style! :)) will be all listed in the same cell either:
    - dd/mm/yy (yellow)
    - dd/mm/yy, dd/mm/yy (orange)
    - dd/mm/yy, dd/mm/yy, dd/mm/yy (green)
    Do you know if this is possible please and do you have any tips on achieving this?
    Many thanks in advance!

    1. Hi Georgie,

      Because two or more dates entered in the same cell turn into a usual text string, you can write formulas based on how many commas a certain cell contains, if any.

      For example, if your dates reside in column A beginning in row 2, you can use the following formulas:

      Green (3 dates): =SEARCH(",*,", $A2)
      Orange (2 dates): =SEARCH(",", $A2)
      Yellow (1 date): =$A2<>""

      Please note, the rules order is important, so make sure the green rule is the first in the list of rule, and the yellow rule is the last.

  13. It was very useful using your tips..
    Thank you..

  14. I have a date column to identify when individuals leave. I would like the color to change from green being >90 days out, <90 days out- yellow, and <30 days out red. How do I accomplish this?

  15. order date un/st back order estimated arrival
    12 21/9/15 unit yes
    13 21/9/15 stock no
    14 22/9/15 stock yes 24/09/15
    15 23/9/15 unit no

    estimated arrival time column is conditional formatted with =AND(c2="unit down", d2="yes", ISBLANK(e2))
    which is if the order is unit and back order is yes and estimated arrival time is not mentioned then it highlights in red.

    is there a way I could also add a condition with the above mentioned one...which will also highlight in red if the estimated time is not filled even after 24 hours from the date the order has been placed (column B)?

  16. Hi, I have random dates of the year in a column now I want to highlight the higher/last date of each month but not able to find any condition need help.

    Ex-
    8-Feb-15
    9-Feb-15
    10-Feb-15
    20-Apr-15 (Highlight this)
    1-Mar-15
    2-Mar-15
    10-Mar-15 (Highlight this)
    17-Apr-15
    18-Apr-15
    1-May-15
    5-May-15
    6-May-15
    8-May-15
    9-May-15 (Highlight this)
    10-Jan-15
    11-Jan-15
    24-Jan-15
    25-Jan-15
    26-Jan-15 (Highlight this)
    8-Feb-15
    11-Feb-15
    24-Feb-15
    25-Feb-15 (Highlight this)

  17. Hello, I am employed as a Training coordinator and am trying to have cells in excel turn yellow when an employees' certification is within 30 days of expiry. And then turn red when the certification is expired.

    This is based off a certification that is valid for a 3 year duration.

    Any help with this would be greatly appreciated.

  18. Hi,

    I would like to set up 4 columns that change colour based on the date in the another column.

    E.G if column E is the 1/5/15 I would like column F to turn yellow 30 days after the 1st then Red 10 days after that.

    Once a date is entered in column F I would like G to turn yellow 30 days later and red another 10 days after that.

    once a date is entered in column G I would like H to turn yellow 30 days later and red 10 days after that.

    Once a date is in H column I would like I to turn yellow 30 days later and red 10 days after that.

    Your help is much appreciated.

  19. Please correct if condition formula.

    =IF(H2<"6:00:00","Standard",IF(H2<"8:00:00","Prime",IF(H2<"10:00:00","Standard",IF(H2<"18:00:00","Prime",IF(H2<"20:00:00","Standard",IF(H2<"22:00:00","Lean","Error"))))))

  20. how to separate particulars time format data. i want to use if condition formula. please reply formula in my e-mail ID.

    Exm.
    10:00:00 AM to 18:00:00 PM (Standard Time)
    18:00:01 PM to 22:00:00 PM (Lean Time)

    Data
    01:00:00
    02:00:00
    03:00:00
    04:00:00
    05:00:00
    06:00:00
    07:00:00
    08:00:00
    09:00:00
    10:00:00
    11:00:00
    12:00:00
    13:00:00
    14:00:00
    15:00:00
    16:00:00
    17:00:00
    18:00:00
    19:00:00
    20:00:00
    21:00:00
    22:00:00
    23:00:00
    00:00:00

    10:00:00 AM to 18:00:00 PM (Standard Time)
    18:00:01 PM to 22:00:00 PM (Lean Time)

  21. Hi, I have a list of persons with their birthdates.
    I need to check only on a certain month.
    For example: if the birthdate is in October of any year, I need to take an action.
    For any other birthdate, no specific action is needed.
    Therefore I want to conditionally format with a color so I can filter or sort on the color.
    Thanks for your cooperation.

  22. Can I apply 2 conditional formats to the one cell such as:

    1.If the cell value changes from $0.00 to a higher amount the text colour will change to red, this conditional formatting I have successfully set.

    2. the text colour of the same cell as above will change to blue if the date changes to a date in the current month in another cell? I have tried a few different formulas, nothing seems to work.

  23. Hi, I have random dates of the year in a column now I want to highlight the higher/last date of each month but not able to find any condition need help.

    Ex-
    8-Feb-15
    9-Feb-15
    10-Feb-15
    20-Apr-15 20-Apr-15
    1-Mar-15
    2-Mar-15
    10-Mar-15 10-Mar-15
    17-Apr-15
    18-Apr-15
    1-May-15
    5-May-15
    6-May-15
    8-May-15
    9-May-15 9-May-15
    10-Jan-15
    11-Jan-15
    24-Jan-15
    25-Jan-15
    26-Jan-15 26-Jan-15
    8-Feb-15
    11-Feb-15
    24-Feb-15
    25-Feb-15 26-Feb-15

  24. Hi. I am trying to create a tracker for all employee certificates. Columns represent each employee and rows define the dates of expiry of certificates. I would like to change the color of the cells when the expiry date is getting closer to today's date. Orange for dates within 15-30 days, red orange for 0-14 days and red for expired dates. I also want to highlight in green the valid dates. I have tried doing it using the formula below but when I test it, the whole row becomes orange and not just the cell. I don't know what I am doing wrong. Please help. Thanks.

    =AND($A$2-TODAY()>15,$A$2-TODAY()<=30)

  25. I use template Pro, I need help this.
    Saturday and Sunday are excluded from workday in Gantt chart pro, but I want to exclude only Sunday. How can I changed this?

  26. how to calculate number of days less than 1 month from column with dates. I couldn't find a formula for this. Is it so simple as it is not here?

    1. Hi Monica,

      If you want to highlight the past dates within 1 month from today's date, you can create a rule with the following formula:
      =TODAY()-$A2<=30

      If you are looking for something different, please clarify.

  27. Also, not sure if this matters but I applied it to rows instead of columns and that seemed to help as well.

  28. SOLVED IT!! This formula works! Posting in case someone else needs it. :-)

    =AND($E2<DATEVALUE("2/7/2015"), $F2<DATEVALUE("2/7/2015"))

  29. Second formula: =AND($E2>DATEVALUE("2/7/2015"), $F2>DATEVALUE("2/7/2015"))

  30. It just keeps combinging my formulas....trying two separate comments.

    First formula: =AND(42041<$E2, 42041<$F2)

  31. Not sure what happened with my first comment but it combined my formulas together. The first one I tried was: =AND(42041<$E2, 42041DATEVALUE("2/7/2015"), $F2>DATEVALUE("2/7/2015"))

  32. I need help with the following issue. I have a spreadsheet with two columns that contain dates and times in this format: 02/06/2015 12:00AM. All of the cells in column E have values but some of the cells in column F are blank. What I need is for columns A-D to be conditionally formatted when the value of column E AND column F occur before 2/6/2015. I have tried these formulas and they both work on some rows but not on all rows and I cannot figure out why.

    First formula I tried: =AND(42041<$E2, 42041DATEVALUE("2/7/2015"), $F2>DATEVALUE("2/7/2015"))

    Both of the above formulas will conditionally format some rows correctly but they both also conditionally format some rows incorrectly and I cannot figure out why Excel can calculate the formula correctly sometimes but not other times. There is no difference in the data types between the rows where the formulas work and the formulas do not work.

    Any help is greatly appreciated.

  33. Just needed some help with conditional formatting. I have two columns both with dates - Column A and B. I want to highlight only the dates in B which Date occur 10 days after the date of Column A

    1. Hi Alwin,

      You can create a rule with the formula =$B2-$A2=10 (row 2 is supposed to be the first row with data.

      1. Thanks for your reply
        while I am using this formula it is showing TRUE/FALSE. Actually I need to highlight that column with a specific color and also need to see the date.

        1. To highlight the column, you need to create a conditional formatting rule with the above formulas. You can find the detailed steps to create a rule in this tutorial.

          1. Hi Svetlana Cheusheva,
            Still I am unable to do this. Please help me to highlight the column B
            Column A Column B
            01-04-2015 11-04-2015
            02-04-2015 17-04-2015
            03-04-2015 23-04-2015
            04-04-2015 09-04-2015
            05-04-2015 15-04-2015
            06-04-2015 21-04-2015
            07-04-2015 27-04-2015
            08-04-2015 13-04-2015
            09-04-2015 19-04-2015
            10-04-2015 25-04-2015
            11-04-2015 01-05-2015
            12-04-2015 17-04-2015
            13-04-2015 23-04-2015
            14-04-2015 29-04-2015
            15-04-2015 05-05-2015
            16-04-2015 21-04-2015

        2. I need to highlight the Column B.

  34. I need to be able to enter a date and then calculate backwards counting only school days. Example: field trip is October 1, 2015. Field trip packet is due 30 school days prior to trip. I need to exclude weekends, holidays and non-student days.

  35. HI,
    I have been trying to figure out a formula that changes the color of the cell to red if the date in the cell is <=10 days away from today's date. i.e if the date in the cell reads 07/12/2015 and I opened the spreed sheet today 07/22/2015 the date on that cell should be highlighted in red.

    1. Hi Manny,

      You can create a rule with the following formula, where A2 is the top-most cell with your dates:
      =TODAY()-$A2>=10

  36. Good day,

    I have a column with dates and I need them to be highlighted if they do not fall in given windows.
    For example:
    Window 1: 1-May-15 till 12-Jun-15
    Window 2: 26-Jun-15 till 31-Jul-15
    Window 3: 14-Aug-15 till 18-Sep-15
    Window 4: 2-Oct-15 till 6-Nov-15
    So I need to know if 07/22/15 does not fall in between any of these windows and have it highlighted if it does not.

    Thank you for your consideration.

  37. Hi,

    I'm trying to highlight a row depending on the difference in 2 dates.
    e.g. if the difference is greater than 1 month then the row needs to go red, if less than a month then the row needs to go green.
    Is there a formula that can do this?

  38. I have 3 columns in excel:
    - Plan Duration Days (e.g.: 15)
    - Start Date (e.g.: 1-June-2015)
    - Available days / week (e.g.: 3)

    Expected Completion Date: 1-July-2015

    Can I calculate expected completion date from the excel formula?

  39. Hi,

    I'm running a transport system, and I need to track where my trucks are at a particular point in time.

    I have an ETA (Expected Time of Arrival) for all the drivers according to their destinations.

    Now, I need a conditional formatting rule to show (in colors) how long a driver spends on his journey at these three points using the commencement date.1. Workshop 2. In-transit 3. Customer Outlet

    Eg. Customer A outlet takes 5 days to arrive. The driver spends two days in the workshop (I need this column to show green), he spends another 3 days in-transit (this should still show green), and he arrives at the customer's location on the sixth day (this will show amber) as he is behind target.

    Regards,

    Reagan

  40. Hello-

    I have read through all of the questions on this page and didn’t see this specific question asked. I would greatly appreciate your assistance!

    I have an excel worksheet- starting in column and row D6 (start date), E6 (End date), and F6 (Frequency). Across the rows at the top we have weeks starting in column and row G4, H4, I4, etc = (July 13, July 20, July 27), etc.

    My first conditional format- to color in the weeks where a communication was sent, worked. That formula was: =AND(G$4$D6) and it applied to: =$G$6:$AM$38

    This worked well for the rows with the frequency of daily or weekly. Where we are struggling with is conditionally formatting rows to fill cells with monthly repetition.

    Currently, the formula I have: =AND(G$4$D7) is for a specific row i.e. applies to: =$G$7:$AN$7 and this highlights the specific cell that corresponds to the week of the first date that the communication was sent out.

    My first question:

    1) How do I create a specific conditional formatting rule using the =AND(G$4$D7) formula that fills in a cell every 30 days or 1 month and also corresponds to the correct week, leaves the remainder of the cells blank for that row, and that also ends when the end date in column E says? Will I have to have 2 separate formulas for each row with a monthly frequency? One for filling in the cells, the other for shading the remainder blank? Also, I should be able to specify in one rule that the formula applies to row 7, 10, 15, 22 for example in the “Applies to” section without having to make a new rule for each row, correct?
    2) What is the order in the rules manager that these rules should be placed? Should the original formula for all cells be placed at the top or bottom?

    Thank you in advance for the help.

  41. Ok think I have a mission imposable here, I need to do an “if, than conditional formatting”. So column “A” is the item “B” is the Due date and “C” is the delivery date. So I need the date in Column “B” to be Green if “C” is blank and “B” is greater than 120 days out, Yellow if “C” is blank and “B” is 60-120 days out and Red if “C” is blank and “B” is less than 60 days out. Is this even possible?

    1. Hi Chris,

      Of course, it's possible. If my understanding is correct, "greater than 120 days out" means more than 120 days from the current date. If so, you can create 3 rules based on the following formulas (assuming that row 2 is your top-most row with data):

      Green: =AND($C2="", $B2-TODAY()>120)
      Yellow: =AND($C2="", $B2-TODAY()>=60, $B2-TODAY()<=120)
      Red: =AND($C2="", $B2-TODAY()<60)

  42. I want cells to turn red if date is within 30 days of maturity date, turn green if within 15 days and turn yellow if within 3 days.

    For Ex if Maturity date is 07-Apr-2016, I need that cell to be red if date(current) is 07-Mar-2016 and so on.

    Please advise formulae to be used for above.

    1. Hi Nick,

      Assuming that the Maturity date is in cell A1, you can create 3 rules based on the following formulas:

      Red: =AND($A$1-TODAY()<=30, $A$1-TODAY()>15)
      Green: =AND($A$1-TODAY()<=15, $A$1-TODAY()>3)
      Yellow: =AND($A$1-TODAY()<=3, $A$1-TODAY()>0)

  43. Hi,

    I'm trying to build a gantt chart and I'm stuck with conditional formatting. I'm trying to fill up the total number of working days excluding the weekend.

    Basically I have,

    [dur][start][end=(start)+(dur)-1][completion-%]

    My current formula,
    This is to highlight the date
    =AND(AND(J$4>=$F8,J$4<=$G8),$A8"")
    This is to highlight the completion percentage
    =AND($A8"",$H8>0,J$4>=$F8,J$4<=($F8+$H8*($G8-$F8)))

    Is there a way to use skip the weekend?

  44. Hi,

    I have a large spreadsheet with a column containing dates, and need to have a column next to this one which names which year the date falls into.

    Eg, if a date is between 01/08/12 and 31/07/13 the 2nd column needs to say "year 1", 01/08/13 - 31/07/14 is "year 2" and so on.

    I'm sure this should be fairly simple I just cant work it out!!

    1. Hi Roy,

      You don't need conditional formatting in this case. Use a nested IF function similar to this:
      =IF(AND(A1>=DATEVALUE("1-Aug-2012"), A1<=DATEVALUE("31-Jul-2013")), "year1", IF(A1<=DATEVALUE("31-Jul-2014"), "year2", ""))

      1. Hi Svetlana,

        That's brilliant, I suspected I was doing something fundamentally wrong!

        Thanks for your help!
        Roy

  45. Hi,

    I'm creating a table for Targeted Dates and Targeted Times, based on Initial Dates and Initial Start Time. How do I format a cell, if using 24hr clock, if a time (midnight) falls into the next day? I need the cell to calculate to the following day, based on the time, but it should also remain the current date if time falls within the current time frame. Hope this makes sense.

  46. Hi,

    I'm creating a resource plan in which I would like to highlight that a resource is available for project work only 3 days a week.
    E.g.:
    Mr. A works every Thursday on A task (full year)
    Mr. A works every Friday on B task (full year)
    Mr. A is available for a project from Monday - Wednesday for project/sprint work

    I have hard coded formatting for initial 2 logic but not able to apply conditional formatting on 3 days a week. Based upon inputted dates, the formatting is coloring the whole duration however I don't want to apply formatting on Thursdays and Fridays.

  47. Hi -
    I am trying to create a visit schedule. I have formatted all of the cells to be in the date format. The initial cell is blank; however, when I insert formulas below to calculate other visit dates that are based on the initial cell a random date will populate. I would like all cells to remain blank until the initial cell is populated with a start date of my choosing. Please advise.
    Study Visit Visit Date
    Day 1
    Day 15 15-Jan-00
    Day 30 30-Jan-00
    Day 45 14-Feb-00

  48. and that is using todays date as the expire date.

    1. Hi Stacey,

      Select the cells in column C that you want to color, begining in C3, and create a rule with the formula =$C3<=TODAY()

  49. I am trying to find a formula to have the cell go red when it hits expiration date, but keeping all others that aren't expired to remain white. there will be only one date entered in c row 3.

  50. hello i wanna learn how overdue date move another sheet automatic and deleted current sheet

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