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

  1. I have tryed some of the above examples with no luck. I have a spread sheet with names in colum A and dates in colum C if the dates are more than 3 months old I want to highlight the names orange, and if the dates are 6 months or greater I want the names to be red. any help?

    Thanks

    1. Hi Chris,

      Select the cells in column A you want to highlight (not including the column headers) and create the following rules:

      Orange: =AND(TODAY()-$C2>90, TODAY()-$C2<180)
      Red: =TODAY()-$C2>=180

      Assuming that row 2 is your first row with data.

      Please note, the formula operate on days, not months, because the number of days differ from month to month.

  2. Hi Svetlana

    Please can you help me, I make use of a spread sheet to monitor and track all certificates expiry’s for all of my employees, I would like the cells to automatically change colour if the date has expired + going to expire in 0-30 days, 0-60 days, 0-90 days.

    Thanks so much.

    1. Hi Etienne,

      Assuming that your dates are in column A, you can use the following formulas:

      Expired:=$A2<TODAY()

      Expire in 0-30 days: =AND($A2-TODAY()>=0, $A2-TODAY()<=30)

      Expire in 31-60 days: =AND($A2-TODAY()>30, $A2-TODAY()<=60)

      Expire in 61-90 days: =AND($A2-TODAY()>60, $A2-TODAY()<=90)

  3. I need a rule for column C that highlights the cell if the date is 6 months or more in the past.

    1. Hi Dan,

      In this case, you can operate on days, not months, because months have a different number on days.

      For example the following formula highlights dates that are 180 days or more in the past:
      =TODAY()-$C2>180

  4. Hi Svetlana,

    I have a spreadsheet with date entry in range column (B10:B54) and I want that whenever Christmas date (24 Dec) and New year Date (31 Dec) comes irrespective of the year, both the date cells should get highlight.

    Thanks in advance

    1. Hi Navneet,

      Try creating a rule for cells B10:B54 with this formula:
      =AND(MONTH($B10)=12, OR(DAY($B10)=24, DAY($B10)=31))

      1. You are genius. It worked. Thanks a lot.

  5. I have task list with start and end dates. I'm trying to get Excel to be able to color fill the cells to the right of the dates based on the duration of the tasks.

    For example, Task 1 starts 3/16/15 and ends 4/3/15. The columns to the right are the Fridays of each week (3/20, 3/27 etc). For task 1, how can I get Excel to color fill the cells for 3/20, 3/27 and 4/3 and then stop.

    I tried Excel bar chart, but that didn't produce the intended results and requires a lot of reformatting. Thanks for any help you can provide.

    1. Hi Tom,

      You can do this by creating a rule based on the following formula:

      =AND(C2>=$A2, C2<=$B2)

      It assumes that row 2 is your first row with data, column A is the start date, B is the end date, and C is the left-most column with Fridays.

      You need to apply this rule to all "Fridays columns", not including the column headers.

  6. Hi! Okay so I've been given a matrix to complete and I AM STUCKED BECAUSE IM NEW WITH EXCEL.
    So, What i have is a planned date (D5), Duration (G5) and End Date (H5) and what i need is the Reviewed date which is on (J5) Is there formula if i want the values at J5-J100 containing the date to turn red when it is completed but over due
    And green is completed on time.

    1. Hi Estelle,

      If my understanding is correct, an item is considered over due if the Reviewed date (J5) is greater than End Date (H5). And you want to highlight only those cells that have a Reviewed date set (empty cells in column J are not highlighted).

      If so, then all you need is create a rule for cells J5-J100 with the following formula:

      =AND($J5<>"", $J5>$H5)

  7. I have a spreadsheet with titles from A1:G1, and data from A2:G283

    In column G i have drop downs with Yes or No options.

    I would like a conditional format which will highlight the rows (e.g from a2:g2) in red which have "no" in the G cell at the end of that row, and Green if "yes".

    What can I use for this?
    Thanks!

    1. Hi Tomocon,

      Simply select all the rows you want to highlight (without the titles) and create 2 rules with the following formulas:

      Red: =G2="no"
      Green: =G2="yes"

      1. Hi Svetlana,

        This seems to only highlight the first cell of the row. not the whole row.

        1. There seems to be a problem with cell references. Try this formula:
          =COUNTIF($C4:$G4, 0)>=4

          And make sure the rules applies to B4:H4 (you can actually include more than one row if needed).

          The row will be highlighted in there are 4 or more zeros in C4:G4, in any cells, don't know how to limit it to consecutive cells only :(

          1. Thanks Svetlana for the quick reply!

            In relation to my first question, the formula you suggested, will only highlight the first cell in the selected row.
            even though i have selected the whole row and made the conditional format on this?

            Any ideas on why?
            Thanks Tom

            1. Hi Tom,

              Oops, my bad. I mentioned incorrect cell references in my formulas but forgot to fix them. Sorry!

              In the formulas, please fix the column using the absolute reference (with the $ sign):

              Red: =$G2="no"
              Green: =$G2="yes"

              1. Hi Svetlana, Back again!

                I have a sheet, which has a data validation in Column I.
                I want to hide a row, If i select "Fully Sent" in this validation on that row.

                Any VBA codes i have found have hidden the entire range when I select "Fully Sent" in one row. I need this to only hide the row where "Fully Sent" is selected.

                Any ideas?

                Data validation in Column I
                Rows 4:92
                Other validation options "complete" "Pending"

                Thanks :)

              2. Thanks Svetlana!

                No worries at all! ... if you ever figure out the consecutive number issue please email me! thanks!

      2. Thanks Svetlana!

        So simple! I feel stupid!

        I have another one, I'm drawing a blank on how to do!

        I have a basic attendance sheet, for a weekly meeting.
        I would like to highlight a row of people who have not been present for 4 or more consecutive weeks.

        So highlight B4:H4 if "0" shows 4 or more consecutive times in C4:G4

        Thanks ! :)

  8. oops. I just added my email. sorry

    1. Hi Sandra,

      No problem :) Assuming that your date is in column C, and row 2 is your first row with data, select all the rows you want to highlight (without the column headers!) and create a rule with the following formula:

      =$C2<DATEVALUE("6/30/2015")

  9. hi ...
    can i vary content of entire column using universal formula based on contents of another column ???

    waiting for reply

    1. yes

  10. PLEASE HELP ME IN THIS ISSUE
    01-07-2013 A1
    01-10-2013 A2
    14-12-2013 A3
    01-10-2014 A4
    17-10-2014 A5
    01-03-2015 A6

    Now I want to fill like this automatically below cells D4 and E4:
    D4 E4
    01-07-2013 31-07-2013
    01-08-2013 31-08-2013
    01-09-2013 30-09-2013
    01-10-2013 31-10-2013
    01-11-2013 30-11-2013
    01-12-2013 13-12-2013
    14-12-2013 31-12-2013
    01-01-2014 31-01-2014
    01-02-2014 28-02-2014
    01-03-2014 31-03-2014
    01-04-2014 30-04-2014
    01-05-2014 31-05-2014
    01-06-2014 30-06-2014
    01-07-2014 31-07-2014
    01-08-2014 31-08-2014
    01-09-2014 30-09-2014
    01-10-2014 16-10-2013
    17-10-2013 31-10-2013
    01-11-2014 30-11-2014
    01-12-2014 31-12-2014
    01-01-2015 31-01-2015
    01-02-2015 28-02-2015
    01-03-2015 31-03-2015

    1. HI, FRIENDS I AM SUBMITTING MY PROBLEM LIKE THIS.
      DATE PAY Rs.
      A1: 13-07-2014 15000
      A2: 17-10-2014 17000
      A3: 01-12-2014 19000

      ABOVE SERIES IS TO MAKE SALARY BILLS IN FRACTION OF A MONTH AND
      Now I want to fill like this automatically below cells D4 and E4:

      D4 CELL E4 CELL PAY Rs.
      13-07-2014 31-07-2014 15000
      01-08-2014 31-08-2014 15000
      01-09-2014 30-09-2014 15000
      01-10-2014 16-10-2014 7742 ( FOR 16 DAYS @15000 P/M)
      17-10-2014 31-10-2014 8226 ( FOR 15 DAYS @17000 P/M)
      01-11-2014 30-11-2014 17000
      01-12-2014 31-12-2014 19000

      D4 IS ALWAYS FIRST OF THE MONTH UNTIL FRACTION COMES AND E4 IS ALWAYS END OF THE

  11. hello! so I have a column were the names of certain permits are, and another for the date it was issued and another for the date of expiry. I want to have my cells with the names turn orange when there's only 2 months before it expires and green if it is still far from expiring, red when it has already expired. Thank you!!!

    1. Hello Lyka,

      Select your column with the names and create 3 conditional formatting rules using the following formulas:

      Red:
      =AND(C2<>"",$C2

  12. I am trying to highlight the days between start date and end date for a given task. tried conditional formatting does not work.

    I tried to just high light holidays. in one worksheet Match works, the same formula does not work else where strange.

    Can someone help in excel, how to go about this.

    1. Hello Raj,

      It's difficult to advise anything without seeing your data. If you can post your sample workbook on our forums and describe what data you want to highlight, our support team will try to help.

  13. I have a spreadsheet that contains various dates stating when a task was accomplished that happened in the past. I need a formula that will fill the cell to various colors based on when they will be due for the same task but a year later ie. 90 days out to be green, 60 days out to be yellow and 30 days out red. The value in each cell only states when the task happened as opposed to when the next task will need to occur (essentially adding one year to the date in the cell). How can this be done? Thanks in advance

    1. Also, within 1 week or overdue to be black, sorry about that, thanks.

  14. I am tryimg to write a formula that says if the value of a specific cell is a 1 or 2, add 60 days to the date of another cell and place that date in a third cell. but if the value is 3 add 30 days to the date.

    So if A1 is a "1 0r 2" take the date in cell A2 and add 60 days to it and place the value in A3. If the value of A1 is a 3, take the date in cell A2 and add 30 days to it and place the value in A3

    1. Hi Mike,

      The following nested IF formula works a treat. Just copy it into the cell A3 and check if it returns the result you want:
      =IF(OR(A1=1,A1=2), A2+60, IF(A1=3, A2+30,""))

      Please make sure the cell A3 has the date format (you can simply copy the format of A2 into A3).

      Also, if the cell A1 contains any value other than 1,2 or 3, the formula will return an empty string.

  15. Hi,

    I need help with conditional formatting. I have two columns that have dates. e.g. Col A (Entry Date) has date 02 December 2014. Col B (Exit Date) has date 01 December 2014. I want conditional formatting in all the cells in Col B when the date is less than the date in Col A.

    Hope it makes sense.

    Many thanks,
    Sam

    1. Hi Samuel,

      Select column B and create a conditional formatting rule using this formula:
      =B2<A2
      Assuming that row 2 is your first row with data.

  16. Hi I have been using the following formulas however for some reason it highlights the cell above not the cell with the date in it. =AND($A2-TODAY()>0,$A2-TODAY()<=30)

    1. Hi Teri,

      Using A2 in the formula implies that row 2 is the 1st row in the range to which the rule applies, please make sure it is really so. If you want to apply the rule starting with row 1, then replace $A2 with $A1 in the formula.

  17. Hello,

    How can I get something like this
    29/11/2014 00:00
    29/11/2014 00:15
    29/11/2014 00:30
    29/11/2014 00:45
    29/11/2014 01:00
    And so on, to get after 96 quarters 30/11/2014, and afterwards 01/12/2014?

    Thank you in advance!

    1. Hi Siva,

      If you need to have time with a 15-minute interval, please do the following:

      1. Enter the starting time in cell A1.
      2. Type this formula =A1+1/(24*4) into cell A2.
      3. Apply the Date format to cell A2 identical to A1.
      4. Copy the formula from cell A2 down to the other 94 rows.

  18. Hi,

    Can someone please let me know if this is possible:

    Change the shade/color of the cell based on WHEN the value changed? For example, I have a cell with a drop down menu. Is there anyway I can highlight that cell if the value of that drop down menu hasn't changed in 2 weeks? Note that field in the cell is NOT a date, it is simply a text string.

    Thanks!

    1. Hello Shahbaz,

      Regrettably, formulas won't help you with your task. Most likely you need a special VBA script.

      1. That's what I figured, thanks.

  19. I would like to make cell E4 put an X in the cell when cells F4:J4,L4,N4,U4,X4:Y4 have a date put in their cell.

    I have a spreadsheet to track training and E4 will show me that the orientation training has been completed should the other cells have a date in them.

    I was trying to use an IF function like this, however, the X will not remove if the date is deleted from the cells.

    =If(F4:J4,L4,N4,U4,X4:Y4=DATE),"X"," ")

    is there a better function to use?

    I have also tried to use conditional formatting but am unfamiliar with excel at this level.

    Thanks.

    1. Hello Josh,

      The shortest formula will be as follows:
      =IF( IFERROR(F4*G4*H4*I4*J4*L4*N4*U4*X4*Y4,0) >0, "X","")

      For the conditional formatting rule, you can use this formula:
      =IFERROR(F4*G4*H4*I4*J4*L4*N4*U4*X4*Y4,0) >0

      If you need to check each cell for the date, not simply for the number, I am afraid, the formula would be too long and complex.

      1. Thank you!

  20. Respected sir,

    How calculate ( old date + current date )in ms excel.

    I need my staff working days calculate daily automatically...

    Thanks For advance

    1. Hello AKBER,

      Just enter =A2+B2 and apply the Number format to the cell with the result.

      Or, you can use the datedif function to count the number of days between the dates:
      =datedif(A2,B2,"d")

  21. I'm going to create a Drop down box in excel, I have set of data (each data have single cell) followed
    100 Sales
    200 Finance
    300 IT
    400 Marketing
    500 Safety
    From this I need to display number only (eg. 100) My request, when I select 100 Sales, cell will display only the number 100, How it is possible to get.
    we need to use data validating and conditional formatting. please help me.

    1. Hello Naina,
      First off, let me check if my understanding of your task is correct. You have a dropdown list with entries like "100 Sales", in some cell, say A1. And you want to display "100" in another cell, say B1 when "100 Sales" is selected in A1.

      If so, just enter the following formula in cell B1, which retrieves the values from A1 until the first space:
      =LEFT(A1,FIND(" ",A1))

      1. Thanks for your reply.
        Is it possible to display in check box itself. Eg. If I select the check box text "100 Sales" after selecting It should be display "100" only. because this is for printing purpose, I need the numbers only 100, 200, 300 remaining's are reference purpose. otherwise any other option to do this.

        Note: I need the numbers only, corresponding word only for reference not for print. I hope you will be understand this.
        Thanking you and I'm waiting for your reply!

  22. Hi,

    Conditional formatting is much interesting. Can u plz tell me how to sort out the contents present in one column with the contents present in another column. I wants to highlight the contents present in one column only which are different from the contents present in another column. How can I do it?

    Waiting for your reply

    Regards,

    Farhan Khalid

    1. Hello Farhan,

      You can create a rule to highlight cells in column A whose values are not the same as in column B with this formula:

      =A2<>B2 (assuming that row 2 is your first row with data)

  23. Hi Svetlana,
    I'm hoping you can help - you seem to be extremely knowledgeable when it comes to Excel and conditional formatting - Very advanced stuff!

    I'm trying to set conditional formatting where the dates in the future so they will turn automatically in a particular colour, more than 2 months green, 2 months and less (amber) and 1 month and less (red).

    I can't seem to find it in the formula drop down list for these particular timescales.

    Your help will be very much appreciated.

    Let me know if you need any more info.

    Many thanks again

    Vincenzina

    1. Hi Vincenzina,

      Regrettably, you cannot create a rule that will work based on months, since the number of days in each month varies.

      Alternatively, you can create the rules based on the number of days, like these:

      Green (in more than 60 days): =$A2-TODAY()>60

      Amber (in 60 to 31 days): =AND($A2-TODAY()>=31, $A2-TODAY()<=60)

      Red (in 30 days and less): =AND($A2-TODAY()>=1, $A2-TODAY()<=30)

      The current date and past dates won't be colored.

      1. Hi Svetlana,

        many thanks for your swift reply.
        I have tried to set this formula but it I get an error message saying 'You cannot use relative references in Conditional Formatting criteria for colour scales, data bars and icon sets.

        Is there another way I could set the formatting?

        Many thanks

        Vincenzina

        1. Hi,

          Sorry, I managed to figure this out.

          Many thanks again!

          Vincenzina

  24. Greetings Svetlana,

    Your tips are very helpful! I've been trying unsuccessfully to add one of the previously explained formulas to a spreadsheet I have, but no luck.

    I'm in need of a formula that I can use with custom colors. I added the =TODAY() to my A1, which gives me today's date every time I open it. Now what I'm trying to do is add 3 different colors to a cell lets say "I4".
    From today's date to 90 days = custom color; from 91 to 120 days = custom color; and from 121 to 180 days = custom color all of them within the same cell. Hopefully, I explained myself correctly and any info you can provide will be greatly appreciated. Thanks in advance!

    Regards,
    Ivan

    1. Hello Ivan,

      Try creating the rules for I4 with these formulas:

      From today to 90 days (including today): =AND($I4-$A$1>=1, $I4-$A$1< =90) From 91 to 120: =AND($I4-$A$1>=91, $I4-$A$1< =120) From 121 to 180: =AND($I4-$A$1>=121, $I4-$A$1< =180) Instead returning the current date in A1, you can use the TODAY() function directly in the formulas, e.g. From today to 90 days: =AND($I4-TODAY()>=1, $I4-TODAY()< =90)

  25. Hi

    Thanks for the very helpful information you publish. I've been trying to work out how to do a specific task in XL2013 and seem to keep mucking it up. please help me

    I have a mix of dates spread across many rows and columns. Based on the =today() I what to format as follows

    any date from today to 36 months ago to be green
    any date from 36 months ago to 60 months ago to be yellow
    any date greater than 60 moths ago to be red.

    i keep getting errors and not all colours showing

    I also have a problem with all empty cells being coloured and i would like them to stay white.

    Can you please help me

    Thanks in advance
    Kevin

    1. Hello Kevin,

      I don't know a way to create a rule that will work based on months, since the number of days in each month varies.

      Alternatively, you can create the rules based on the number of days, like these:

      Green (from today to 36 days ago): =AND(TODAY()-$A2>=0, TODAY()-$A2<=36)

      Yellow (from 37 to 60 days ago): =AND(TODAY()-$A2>=37, TODAY()-$A2<=60)

      Red (more than 61 days ago): =$A2-TODAY()>=61

  26. I don't think this has been answered here.

    What if you want a range of cell each with a range of different dates yet you want them all follow the same rules regarding their change in colour leading up to the date specific related to each individual specific cell?

    So one cell may have a date of 1/1/15 and another may have a date 20/1/15 they may turn different colours as you get closer to the date each time you open up the spreadsheet, but the earlier date will change colour sooner than the later one.

    1. Hello Oliver,

      You can achieve this by creating a few rules with different colors for your dates. For example:

      Yellow (due in 1 to 20 days): =AND($A2-TODAY()>0,$A2-TODAY()<=20)

      Red (due in 21 to 40 days): =AND($A2-TODAY()>20,$A2-TODAY()<=40)

      And so on.

  27. Hi Svetlana, Can you please help me?
    I have 2 columns (B&D) that have dates in them and I want column D to turn green if column B is column D. I got the red condition code set up correctly but the green condition code (=B8<=D8) works to a point. If column B does not have any data in it, it still turns column D green. How do I fix this so if column B is blank column D will not highlight? Thank you!

    Jeff

    1. Never mind I found the answer. Here is the formula I used:
      =$B2>DATEVALUE("1/1/1990")

  28. Thanks for that Svetlana.
    I have 300+ operative all of whom have had training for various courses and all of which expire at different times so I require something to help track when they are close to expiring and once they are they have expired.

    The forumla I had used was:
    =$B$2-45<TODAY() To change to orange 45 days before certificate had expired
    =$B$2<TODAY() To change to red once certificate had expired

    Thanks for any help in advanced I use excel regularly to create tables and charts but am really not familiar with formulas.

    1. Hello Robert,

      The problem proves to be in cell references. Your formulas are correct, just use $B2 instead of $B$2 (absolute column and relative row references) so that Excel can correctly apply the rules to all cells.

      You can find more information about cell references in conditional formatting rules in this article:
      Absolute and relative cell references in Excel conditional formatting

  29. I have got a vast spreadsheet giving various dates of when peoples certificates are due to expire,I have got two formulas I wish to use one notifying me 45 days before the expiry date given and then another one once the date has been passed. I am able to get this to work for an individual cell but I am then unable to transfer this across the rest of the cells without doing them all individually. I have tried to use format painter but that does not carry over the conditional formatting.

    1. Hello Robert,

      If you want your conditional formatting rules to get applied to the entire column, e.g. highlight all the dates in column A that meet the conditions, then select the range of cells you want to color, say A2:A100 and re-write your formulas for the first of the celected cells using absolute column and reletive row references, e.g. $A2.

      If you want something different, please give me more details about your data structure and the formulas, and I will try to help.

  30. Great article! I really suggest if the reader is unfamiliar with conditional formatting, start with the beginning examples and work your way down to functional solutions for more complicated problems. Thank you!

    1. Thank you for your kind words, Chris! I am happy to know my article was helpful.

  31. how can i create in excel that make expire date for one week in red mark

    1. Hello shoukath,

      For me to be able to suggest a proper formula, please provide more details about what is deemed an expired date.

      If it is simply more than 7 days back from now, then you can use this formula (where A2 is the first cell with data):
      =TODAY()-$A2>7

      If you want something different, please clarify.

      1. I have some dates in column B. In column A, I would like 15 days before one text will show like "LC need". Is it possible by a conditional formula? Pls help me.

  32. I have a set of dates... I need to change the date color if today - 2 days one color, and 2 days back to 5 days back one color, 5 days back to 7 days back one color, more than 7 days back one color, How its possible to do? Please

    1. Hello Naina,

      You can do this by creating a set of conditional formatting rules with the following formulas (where $A2 is the first cell with data):

      Today to 2 day back:=AND(TODAY()-$A2>=0,TODAY()-$A2<=2)

      3 days back to 5 days back: =AND(TODAY()-$A2>=3,TODAY()-$A2<=5)

      6 days back to 7 days back: =AND(TODAY()-$A2>=6,TODAY()-$A2<=7)

      more than 7 days back: =TODAY()-$A2>7

      1. It's Working good, Thank you very much

  33. Hi

    I have been trying out the formulas but i just cant get what i wanted. im trying to keep track on deliveries and need a warning before the actual delivery date.
    Cell A1 = todays date

    i want it to be:
    orange : 15 days before date(or any number of days i can modify to)
    yellow : on the day itself till
    red: after 15 days onwards

    thanks.

    1. Hi Joey,

      Select the dates you want to format, say in column B, and try creating the rules with the following formulas, where B2 is the top-left cell of the selected range:

      Yellow: =AND($B2>=$A$1, ($B2-$A$1)<15)
      Red: =$B2-$A$1>=15
      Orange: =$A$1-$B2=15 - exactly 15 days before the date
      or
      Orange: =AND($B2<$A$1, ($A$1-$B2) <=15) - from 1 to 15 days before the date

      1. wonderful. perfectly working. thanks alot!

  34. Hello

    I want to highlight my cells in different colours depending on the date. For example (and I'm using British dates sorry) if I have cell 1A with a date 17/10/2014 I would like it to become yellow three months after the date and red six months after the date.

    Thankyou very much

    1. Hello Finlay,

      Sorry, I don't know a way to calculate exactly 3 or 6 months because the number of days varies from 28 in Feb to 31, say, in Dec.

      Instead, you can highlight dates based on the number of days after a certain date. For example:

      Red (after 180 days): =TODAY()-$A2>=180 (it should come 1st in the rules list)

      Yellow (after 60 days):=TODAY()-$A2>=60

  35. Hello,
    I am trying to colour code for various dates and can't seem to get the formulas to work. The intent it to show any date that is equal to today (or in the past) as green, dates within the next 30 days as yellow, dates 31-60 days as orange, and anything 61 days or longer as red. I have tried creating a new spreadsheet as well as leveraging an existing one, and can't get the formulas you show above to work as a test. Any advice and assistance would be greatly appreciated.

    1. Hello Cody,

      Try creating the rules with the following formulas (assuming that your dates are in column A and row 2 is the first row with data):

      Green: =$A2<=TODAY()

      Yellow: =AND($A2>=TODAY()+1,$A2<=TODAY()+30)

      Orange: =AND($A2>=TODAY()+31,$A2<=TODAY()+60)

      Red: =$A2>=TODAY()+61

  36. Can someone help me with the formula of picking the highest number settlement on a weekly basis with data that shows multiple amounts settlemend on a daily basis

    1. Hi Sam,

      Please give us more details, i.e. what columns you have and what kind of values they contain (data sample would be even more helpful), and will try to help.

  37. I have a spreadsheet with a constant date in D8. Then I have dates calculated based upon that date. I need a formula that counts 10 days from that date, but if it falls on a Saturday or Sunday, then I need Monday's date to appear.

    I will also have to work in a holiday table so that if it falls on a holiday, then the next day after the holiday should appear.

    Might you help?

  38. In some Excel work sheets i am trying to insert rows, but the insert row option is disabled, but the option is enabled on on some some sheets. whats the probelm?

    1. Most likely these worksheets are protected. To check this, select the sheet, then go to Review > Protect Sheet.

  39. n excel file, when i am trying to move one sheet from one file to another excel file it shown a message as
    "A formula sheet you want to move or copy contains the name 'sss', which already exists on the destination worksheet. Do you want to use this version of the name?
    * To use the name as defined in the destination sheet, click yes.
    * To rename the range referred in the formula or worksheet, click no, and enter a new name in the conflict dialog box."
    it ask the same question 20 to 40 times to move a sheet.
    and after move the sheet and make changes and save it with another file name and close it. when i open the same file the format totally distrubed just shown text with format.

    1. Sorry, I've never run into a similar issue and therefore cannot advise anything.

  40. In excel file, when i am trying to move one sheet from one file to another excel file it shown a message as
    "A formula sheet you want to move or copy contains the name 'sss', which already exists on the destination worksheet. Do you want to use this version of the name?
    * To use

    1. Hi,

      Thank you for the great assistance you all are providing in this blog.

      My question is, I have a column in excel that contains (varying dates in certain cells and blank cells with dates yet to be entered).

      I am hoping to create a formula for the entire column that initiates a green fill in the cell that contains a date (30 days after the date range specified). I hope this makes sense.

      Your advice would be greatly appreciated.

      Kind Regards, Paul

  41. Ma'am,

    I am trying to highlight last month, this month, and next month with various colors. For instance, last month would be black filled, with bold white font. This month will be red filled with normal font, and next month would be filled yellow with normal font. I was using the conditional formating of "last month/this month/next month" but it is highlighting things from last and next year. How can i achieve what i am trying to do? My dates show just the month and year. i tried to use the less than "=NOW()+ 30" and equals to "=NOW()". Which is working, however, i do not know how to highlight for the previous month. Any help is appreciated.

    -CTR2

    1. Hello John,

      Please use the following formulas:
      This Month
      =month(A2)=month(today())

      Next Month
      =(month(A2) + 1)=month(today())

      Previous Month
      =(month(A2) - 1)=month(today())

  42. Hi,

    I am trying to create conditional formatting that will show 30 days before training will be required again on an annual basis.

    In cell B2 (C2, D2, etc.) I have the date that training took place. The next scheduled training for each training type would be 1 year later. 30 days prior to the 1 year expiry, I would like the date to turn red or the cell fill to be red so it will highlight that re-training is going to be required soon.

    Is this possible to do? Thank you for your help.

    Employee Name H&S at Work WMHIS
    XXXXXX 05/05/14 02/07/14

    1. I am also trying to create conditional formatting that will show 30 days before training will be required on an annual basis nut for mine training re-certification isn't a year from the date taken it is June 15th of every year. Is it possible to make a formula for this. My boss does not want expiry dates on spread sheets she just wants dates to turn red a moth before re-certification is required.

      Is this even possible? Thank you

    2. Hello Jane,

      Select all your columns with dates and use the following formula for your conditional formatting rule:
      =AND(TODAY()-B2 < 366, TODAY()-B2 > 336)

      1. Hi Dear,
        I want col S(Fee defaulter) to be
        1) Red fill
        if col E(Ending date) is over and Col Q(remaining Fee is greater than zero).
        2)and want light red fill
        if Col E(Ending date) is today and Q(remaining Fee is greater than zero)
        3)and if both the conditions are not met then want green fill
        Thanks in advance

      2. It works! Thank you very much! I tried other methods shown on other sites with no success. I appreciate your assistance.

  43. Hi, I have a date in A1 and a date in B1. How would I highlight B1 IF its date occurs after (or more recent than) the date in A1? I assume you could use conditional formatting for this, but I'm unsure of the formula. Thanks.

    1. Hi Eunice,

      Try creating a conditional formatting rule for cell B1 using this formula =$B1>$A1

      If you need to highlight other cells in column B as well, select them all before creating the rule.

  44. Hi,

    I've been trying to modify the formula based on your Example 2 but I still failed. Scenario: I want to highlight the cell in red if Status (Column C) is not "Closed" and the Due Date (Column M) is already exceeded. For example, Status is "In Progress" and Due Date is 10-Sep-14 but today is already 25-Sep-14. So, the formula I used was this: =AND($M2:$M500>DATEVALUE(M),$C2:$C500”Closed”) I have also formatted the Column M to be in Date format.

    So what went wrong? Hope you could explain. Thank you very much!

    1. Hello Perrin,

      In conditional formatting, the formula is written for the top-left cell of the range to which you apply the rule. And Excel adjusts the formula for all other cells correctly, like when you copy the formula to other cells manually. So, most likely you need the following formula:
      =AND(TODAY()>$M2, $C2<>"Closed")

  45. Hi,

    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 occur before the dates in A. (e.g. if B is 4/30/2014 and A is 5/30/2014, then highlight cell B; if B is 5/24/2014 and A is 4/25/2014, do not highlight cell B.) Is that possible?

    1. Hi Lawrence,

      You can create a rule for column B with the same formula that is used for comparing numbers:
      =$B2<$A2 (assuming that your table has headers and row 2 is the top row with data)

  46. If u can please help me out of this matter. i want to put a formula that puts the expiry date of 1 year counting 365 days. i.e. if a date 01-01-2014 is written in cell(A) and i wish to have the date exactly after 1 year (365 days)in Red. URGENT PLEASE

    1. Hello Imran,

      You can use this formula to get the expiry date:
      =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))

      Where A2 is the cell with the original date.

      1. I have the same problem and this formula isn't working. any other suggestions?
        Many thanks

        1. Hello Beckie,

          I think I did not make myself clear in the previous response, my apologies. The above formula calculates the expiry date based on the value in cell A2. To highlight the expired dates, you can create a conditional formatting rule with this formula:

          =TODAY()>$A2+365

          Please see the results in this sample worksheet. If you are looking for something different, please clarify.

          1. Hi Svetlana,

            I am trying to recreate the above response to highlight the expired dates. I used conditional formatting using your formula =TODAY()>$A2+365 and this indeed worked. However, this only worked on the specific cell "A2." I'm trying to have the entire column checked for dates that are a year old. I tried highlighting the entire column but I was not successful. If you can please assist I would greatly appreciate it. Thanks!

  47. I have a spreadsheet that has a date in column A1 - it is a today date formula.
    I then have dates from Jan 2014 - May 2015 shown as each day (roster schedule), I have conditional formatting set up within the spreadsheet for various schedules (O/T/ leave etc) however I am wanting to highlight a single column based on what today's date is so "today" is easier to find when scrolling back and forward organising the schedule.

    Any suggestions?

    1. Is the above described condition possible to format? I have a similar need to where I would like a cell (L38) to change color when a different cell formatted as =Today() equals 12/1 of any given year. Meaning, I want the L38 cell to highlight yellow each year when today's date = 12/1. Additionally, I would like the highlight to stay active for seven days.

    2. Hi,

      I would like to add some formatting on cerain cells which contain dates. For example, I have a due date on colum A and a scheduled date on colum C. Now if the scheduled date is within 30 days of the due date, the scheduled date colum has to turn red.
      I would also like to highlight column n to red if column c is populated with dates and those dates are within 30 days of column A.

      Any help would be appreciated.

      Thank you,

      1. Hi Denis,

        Select column N (from N2 and below) and create a conditional formatting rule using this formula:
        =AND(C2<>"",A2-C2<30)

        Then select column C and create another rule with the same formula.

        1. I have a slightly different scenario than above. I have a date in column E and a date in column F. I need to highlight the date in column F if it is less than the date in column E.

          Thanks for any help you can provide.

        2. hello mijya excel help chaye agar ap help kar sakti ha to repli

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