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

  1. Help! I really hope you can respond to this and help me!

    I need a formula(s) to apply conditional formatting based on various dates in a column both on the same tab and from a previous tab in the same workbook. Column A is "Date Received" (where the date will vary by when something was received). Column B would be Date Created.
    The basics of the formula I need would be if Column A date is greater than 1 week (or 7 days, whichever is easier), format the cell yellow, if greater than 2 weeks (14 days), format the cell red.

    Specific Example: If a date isn't entered in Column B within one week (or 7 days, which ever is easier), the cell in Column A will turn yellow, if no date is entered in Column B within 2 weeks (or 14 days) the cell in Column A will turn Red. If a date is entered in Column B the cell in Column B with the date can turn green.

    The ideal formatting would be to add icon sets with with the stop lights instead of highlighting the cell with a color. I believe there is an option to enter a custom formula to apply it with the icon sets, correct? If so, I will do that using the formula above. If not, it's not a deal-breaker.

    One other thing, how do I reference a column from another tab so it will add and show two weeks after the date in a column of the other tab. Example - Column A "Quote for Approval" in Tab 1 has a date entered for when the quote was submitted. We need Column B in Tab 2 to add 2 weeks (14 days) and put that in Column A from Tab 1. The conditional formatting for that date in Tab 2 would be based on the original date in Tab 1 but would be the same concept of >7 days, >14 days.

    I really hope this makes sense. Please let me know if you have questions and thank you very much for having the forum!!!

  2. Hi Alexander Trifuntov (Ablebits Team)
    I'm trying to make a scheduling template for 18 employees. I can't figure out how I can auto-highlight the scheduled time (interval of 9 hours) when I put the shedule in a different cell, then also apply a different color when I type in a break time in a different cell as well.

    F4 - schedule (9am-6pm)
    F4 - 1st break (11 am)
    G4 - Lunch start (1pm)
    H4 - Lunch End (2pm)
    I4 - 2nd break

    Trying to apply the formatting to K4-BF18, every cell from J4 is considered a 15 min interval.

    Also, I would like to change the color of each cell within the time range when agent's status in J4 is absent or late. I hope you can help me. Thank you so much in advance!

    1. Hi!
      Sorry, I do not fully understand the task. What formatting do you want to apply in K4? Why are there two values in F4? What does it mean "agent's status in J4 is absent or late"?

  3. When we entering date 1/1/30 in excel its returning 1/01/1930 or 1/1/40 its returning 1/01/1940.I am expecting 01/01/2030 or 01/01/2040 in this case.Is it possible to fix this using conditional formating.I need to fix this in excel only.Can you please let me know best possible way.

      1. Thank you Alex. My cell has value 31-DEC-30 ,Converted cell to date format DD-MON-YYYY .Its converting to 31-DEC-1930. Getting year wrong in Excel 2016.I have column formula =IF(ISBLANK(X89),"",DATEVALUE(X89)).

        Issue:

        30 through 99 is interpreted as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998.

    2. AlexChanged system date settings.Still it’s not working.

  4. Hello. I have three columns of dates (all on the same row). I need column C to highlight green if the date is before both dates in columns A and B, highlight yellow if it’s before only one date (A or B) and past the other date (A or B), and highlight red if it’s past both dates A and B.

  5. I need to figure out how to have a date change color after the date has reached 6 months and a different color for when its been 1 year. This is dealing with date of employment and the 6 month and year bench marks are for raises.

    The date conditional formatting only goes back a month.

    Would also be amazing if when the color changes that it sparks an email to the hiring manager.

    1. Hi!
      Please re-check the article above since it covers your case. Sending an email automatically is possible with a VBA macro.

  6. I need to conditional format cells so that they are automatically coloured in when the date changes in the first column.

    I have used the eomonth formula in a col before the calendar date rows start. If I change the date on cell b10 then the eomonth formula gets updated in cell e10.

    I need the the next section which the dates Jan to Dec per year sections to highlight cell accordingly to the date in cell b10.

  7. Hi, I'm trying to set up conditional format for a specific time range between 0-10 hours , Kindly assist with best approach.

    Thanks in anticipation

  8. Thank you for taking the time to put this together. It is helpful for highlighting holidays. However, if a holiday falls on a Saturday or Sunday my agency observes the holiday on the Friday prior, or Monday after. Is there a way to have excel highlight the Friday prior to a Saturday holiday, or the Monday after a Sunday holiday?

  9. Hello --

    Not sure if I'm following the right thread. But I'm in Google Sheet and I have the same exact problem.
    I wanna highlight cells that match another set of cells.

    For example : cell E4:O4 contains time - and below them are E8:M399 with time as well. And I want to highlight the time in cell E8:M399 to match E4:O4..

    I'm using =match(E8,indirect("SCHEDULE & COVERAGE!E4:O4"),0) applied to range E8:M399

    It actually works to all cells except if the time from E8 starts with 12am or 0:00
    It works if the time 12am or 0:00 is in the middle for example J8..

  10. Hi,

    How do I highlight a course date that is due to be renewed. Let say, I attended a course on the 1/11/2022. And the course has to be renewed after 3 years which is 1/11/2025. What can I do to highlight the course date if the actual date is 3 months before the renewal date i.e > 1/8/2025? Thank you

  11. Hi,
    I have a Gantt chart issue and I’m wondering if you could help?

    I have a Task Start Date Column (C), End Date Column (D), and Progress % (E). If the Task Progress (Cell E1) is still 0% after the task start date (Cell C1), is it possible to highlight the start date (Cell C1)?

    I hope that makes sense.

    Thanks

  12. Hi,

    How do I highlight the cells that are within the 9 hour shift per 1 hr interval. I already have the count which decreases when an employee ended the shift.

    Example: there are 5 employees logged in by 3pm. And within 3pm, it consist of the shift from 6am to 2pm. I would like to check if it is possible to highlight those 5 employees.

    1. Hello --

      Not sure if I'm following the right thread. But I'm in Google Sheet and I have the same exact problem.
      I wanna highlight cells that match another set of cells.

      For example : cell E4:O4 contains time - and below them are E8:M399 with time as well. And I want to highlight the time in cell E8:M399 to match E4:O4..

      I'm using =match(E8,indirect("SCHEDULE & COVERAGE!E4:O4"),0) applied to range E8:M399

      It actually works to all cells except if the time from E8 starts with 12am or 0:00
      It works if the time 12am or 0:00 is in the middle for example J8..

  13. I am needing to compare due dates and completion dates. I am specifically trying to have it automatically generate a green for on time and red for overdue. This is so that I may be able to easily notice trends within specific departments. I am having difficulty with dates that are the same as the due dates and with not having to format each entry. I would appreciate any and all assistance. Thank you.

    1. Hi!
      Have you tried the ways described in this blog post? Please describe your task in detail if they don’t work for you.

  14. I need a condition like previous month of 9th to current date is this possible in excel and every time previous month will be changed like this is october then the previous moth is september if the month is december then need to extract data from november 9th to current date

  15. Hi - I may be in the wrong place for this question, so apologise if I am.
    I am pasting a number of dates from a genealogy programme into an Excel worksheet. Some of the dates are day/month/year, some are month/year and some are just year. How do I format the column so they appear as they should do?
    If I format for day month year, then the "year only" dates are miss-interpreted, eg 1924 becomes 07/04/1905
    If I format for number, 01/07/1916 becomes 6027.
    Is there a conditional format solution to this?
    Thanks
    Jeremy

    1. Hello!
      1924 is a number, not a date. To get the date, you can use these recommendations: How to convert number to date in Excel. To convert a year number to a date, use the DATE function. For example,

      =DATE(A1,1,1)

      If you have a lot of different values that need to be converted to date, try Text to Date tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      1. Alexander thanks - I understand what you are saying but I can't find a simple way of formatting the column to display a consistent date when the input data is in 3 different formats (as described). I'll have a play and eg see if I can display all dates as simply a year eg 1950. I believe I may be able to do this by format/number/custom/ then type in yyyy?
        Jeremy

  16. Hi Alexander,
    In my column G I want all contract dates, expiring in less than 6 months highlighted in conditional formatting. I already found some codes but I'm receiving a "There's a problem with this formula." error. Maybe is it because I'm working on a macbook?

    Can you please help me? Thanks

  17. Hi!
    How can I format the cells containing hour info to indicate it started or ended according to the current time.
    Example;

    NOW() : 10:30
    START END
    event 1 11.11.2022 08:40 09:30 start and end should be coloured
    event 2 11.11.2022 08:40 11:30 start should be coloured
    event 3 11.11.2022 10:40 12:30
    event 4 11.11.2022 14:40 16:30

    1. Hi!
      You can find the examples and detailed instructions in article above.
      The NOW() function returns the date and time. To use time only, apply the formula

      NOW()-INT(NOW())

      Use conditional formatting rule:

      (A1-INT(A1))<(NOW()-INT(NOW()))

      I hope this will help.

  18. I have used conditional formatting on a spreadsheet. In it I have it set to figure the number of days since a unit has been cleaned and color code it to let me know how close it is to needing to be re-cleaned. My problem is that in the units that are rented, I did not populate the date of cleaning so it has no date to judge by and has put in a date of 6/28/1900. I am sure this is set up in the system but I want to find out how to set the cell up so when the date is left off it does not calculate anything and leaves the date blank. The formula I have used is: =(D+180) D is the ce3ll where I input where each unit was last cleaned. I want the cell to populate only if there is something in the D column.

  19. How can i entire row fill with color by conditional format when put in there random date in excel?
    Plz give some clue & more easier if you send some snapshot..?

  20. If we have a start date with timing and end date with timing how to highlight colors very close to end time.

    1. Hi!
      Have you tried the ways described in this blog post? Please re-check the article above since it covers your task.

  21. Hello sir,
    I'm preparing sheet name issued material list and i want to apply condition formatting on this sheet
    ex. i issued one shoes or helmet on current date n the reissued time is 6month
    i want to apply condition on current date
    the color will be change when it expire . can u plz help me

  22. Hi Alex,
    Great post!
    I would like to alternate colors of date range based on fiscal year from June to July (following year).

    Many thanks in advance.

  23. Hi all,
    How do I change the colour of a value based on how the number of that value in the column?

    Eg if date 01/01/22 appears in a column 5 times it will turn orange. But when that date appears 8 times it will turn red.

    Thanks

  24. Hello,
    I have created a spreadsheet and applied conditional formatting to my date column to highlight all future dates using the example above '=$B2>Today()'. For some reason it isn't picking up all of them.

    In one instance, when I sort by date it will highlight all future dates with the exception of the last row. That row will highlight if I add a future date to the row directly under it but the new future date will not be highlighted.

    If I resort by job number (different column) some of the previously highlighted dates are now not highlighted.

    I've confirmed the date column is all formatted correctly. I'm using Office 365 if you think that might be causing the problem.

    Thank you in advance for any help you can provide.

    1. Hello!
      The row number in the conditional formatting formula must match the number of the first row of the conditional formatting range. For example, for the range B1:B100 or B:B, you need to use the formula

      =$B1 > Today()

      Your formula "=$B2 > Today()" will work for a range, for example, B2:B100.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  25. We have dates in column D. We would like to add a condition when it is 1 week past the date it changes colour, then again when 2 weeks past date to a different colour and again when its past 3 weeks. What formula would use per week?

    Thanks,

    Kasey

    1. Hi!
      Please re-check the article above since it covers your task. Pay attention to the following paragraph: Conditionally format dates based on the current date.

  26. Hi,

    So I have a task list, and I want to use conditional formatting for two things. Number one, I want to highlight a cell that has been left blank in a range past a due date to complete the task. I have that due date represented in a cell in the same sheet, in case I need to make reference to that date in the conditional formatting formula. Additionally, I want to also highlight cells that reflect a date past that same due date, within the same range.
    I am having the hardest time, because all of the formulas I find use a moving date, usually in relation to today, or weekends, or holidays. I believe what I need should be much simpler than those. Help!

    1. Hi!
      Have you tried the ways described in this blog post? Pay attention to the following paragraph of the article above - Conditionally format dates in Excel based on the current date. If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

    2. Ok ok... I solved the the second part of my question with a pre-made, greater than formula. Still... the first formula I still don't know how to do it.

      As a reminder, I need the cells in a column to light up if the date on a cell that I will be updating every month has passed, and the cells in that column are blank, indicating the task has either not been completed, or the employee has not recorded their task. Please help.

  27. i have put this formula in my excel conditional formation but it show me date of one day before Sunday. =WEEKDAY($B3,2)>6
    please can you help me out.

  28. Hi All

    I really need your help.

    I have an inventory in and out log. It logs date an asset was issued and date it was returned. I have everything in a drop down menu and i need the following. these assets are loaned out on a daily basis and returned on a daily basis. (Or supposed to be) there are other assets issued on a semi permanent basis
    My drop down items on another sheet are (data validation):
    Issued (Conditional Formatting Turns the Cell Yellow if issued is selected)
    Returned (Conditional Formatting turns the cell Green if returned is selected)
    Semi-Permanent (Conditional Formatting turns the cell orange if semi-permanent is selected)
    Lost (turns red when selected)
    Stolen (turns purple when selected)

    If the cell with the date issued is today, the cell containing the selected status "Issued" turns yellow. Issued is a drop down item on another sheet.
    If the cell with the date issued is older than today and the status of the asset still says issued I want the cell to change from yellow fill saying Issued, to Red fill and say "OVERDUE" in bold white. OVERDUE is not a drop down item on another sheet.
    Essentially every night at midnight i want the status of the assent issued.

    Perhaps i should add OVERDUE to the drop down items but im really not sure

    1. Hello!
      The value in a cell with a drop-down list cannot be changed automatically. To change the value in a cell to “OVERDUE” according to the conditions, use the IF AND combination. You can change the color of this cell with conditional formatting.

  29. Hi,

    I have a table which contains two columns with dates. I want to highlight in one of the columns the dates that are greater with 3 or more days than the dates în the other column. How can i do this?

    Thank you.

  30. Hello,
    I am trying to create an expense reimbursement sheet for my office.
    I have a date column (to indicate when the expense was incurred), that people will manually fill.
    I would want a "Period" section that automatically fills out depending on those dates.
    i.e. An employee incurred expenses 03/01/2022 and 04/05/22. How can the period section automatically change to "03/01/2022-04/05/2022."?
    Thank you so much!

      1. Thank you!
        So I have done that, the issue is that the second "A2" will be changing depending on the number of items, that they will enter, so it sometimes will be A3 or A4, or A5, etc. I have also tried doing a Min Max and concatenate, but I feel I'm missing a part on that second part.

  31. Hello there!

    I am trying to highlight cells using different colors, based on how far away they are, both upcoming and having passed.

    Scenario: I have a deadline for an agreement listed in my spreadsheet. The day is coming up and I would want it to turn yellow when it is 2 weeks away and red when it is one week away or less.

    Scenario 2: I have a date that has passed and I would want it to turn say orange when that date is 3 weeks ago, then red when it is over a month ago.

    I have found things that seem like they should work, using formulas like =TODAY()-C4>30 but I can't seem to make them work.

    Please help. Thank you.

    1. Hello!
      Have you tried the ways described in this blog post? Unfortunately, I can't create conditional formatting in your table. If you want to highlight other cells based on the cell with the date, then I recommend that you study this instruction.

  32. (duplicate and corrected)

    I stopped reading at September 2021. Appreciate the time you take to answer the inquiries. Thank you. I couldn't find how to answer my question in some of the replies and am not familiar with Excel or formula use except little by little over time. My question is:

    I have been using the COUNTIF to find specific text within a cell. I am *now trying to do the same with the date, specifically the last 30 days. So within a text, there will be a date followed by a varied comment. I am trying to yellow fill and red annotate that comment for the last 30 days.

    If you can assist, please advise.

    -THANK YOU

    1. Hello!
      To find specific text in a cell, it's best to use the SEARCH and FIND functions. Your explanations are not very clear. But to set the background of a date cell with a specific color, use this article's recommendations. However, it is not possible to change the color of only part of a cell.
      If date and comment are written in the same cell, then you need to extract the date from the text: Regex to extract date from string.
      Describe your problem in more detail and maybe I can help.

  33. Hi, i need help to create a conditional formatting rules that automatically highlights that date before 3 months , like if its 14/01/23 it highlights it when 14/11/22 . Can you help me , i did read the tutorial but i dont understand , can you give the simple sample rules.

    1. Hi!
      Pay attention to the following paragraph of the article above - How to highlight dates within a date range.

      Formula to highlight future dates more than 90 days from now: =$A1-TODAY()>90

      You can also add 3 months to the current date and compare it to the date in the cell:

      =DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))>A1

      The following tutorial should help: Subtract dates in Excel; add days, weeks, months or years to date.

  34. Hello,

    Used the formula listed in this article:

    =AND(TODAY()-$D2>=0,TODAY()-$D2<=7) - highlight all rows where the End Date (column D) is within the last 7 days. You can use this formula to track the latest overdue payments and other delays. (I changed it to 120 days to see upcoming due dates within the next 4 months)

    But it only highlights the cell, not the row? How do I change it to highlight the row?

    Thank you!

  35. Hello,

    I hope this is easy and straightforward...I have a date column and I'd like to create another date column, but I'd like it to show the date that is 4 months ahead of the first date column. This would be part 1 of my question.

    Part 2 would then be, how can I get the row to change colour, once the date in this new column is going to occur in the next four months?

    Explanation: What I am trying to do is have rows be highlighted when a specific item is coming due within the next four months so that it will be visually flagged.

    Thank you for your time!

      1. Thank you! :-)

  36. hi,

    Im not sure why the following conditional formating formula doesnt work. Cell tresults in the table are references and formulas. No errors are reported when saving but nothing gets highlighted. I was expecting the 5th row (04/05/22 08:47 23/05/22 15:57) to get highlighted

    =AND($G5>NOW(),$H5<NOW())

    Colum G Column H
    Start Date Date Calc
    04/05/22 08:47 04/05/22 08:47
    04/05/22 08:47 04/05/22 08:47
    04/05/22 08:47 04/05/22 08:47
    04/05/22 08:47 04/05/22 08:47
    04/05/22 08:47 23/05/22 15:57
    23/05/22 15:57 02/08/22 23:48
    02/08/22 23:48 13/10/22 07:38
    13/10/22 07:38 23/12/22 15:28
    23/12/22 15:28 04/03/23 23:19
    04/03/23 23:19 15/05/23 07:09
    15/05/23 07:09 10/12/40 16:46

    2. ahhhh pls ignore the comment/question. just had the wrong way arround.

  37. Hi There! I've built two sheets in the same workbook: first, a list of teammates, their start date of travel, and the end date of travel. Second, I built a sheet with a calendar similar to yours above with "tasks," but instead it has team members in the first column. I would like the calendar to highlight the dates each team member is traveling referenced from the first sheet. Do you know how I'd do this? Thank you!

  38. TaskAID Associate Start Time Start Time (Mins) End Time (Mins) Duration (secs)
    ab1 1 def1 4/20/22 6:30 6:30:00 AM 6:30:16 AM 16
    ab2 2 def2 4/20/22 6:30 6:30:00 AM 6:30:16.5 AM 16.5
    ab3 3 def3 4/20/22 6:30 6:30:00 AM 6:30:16.2 AM 16.2
    ab4 4 def4 4/20/22 6:30 6:30:00 AM 6:30:18 AM 18
    ab5 5 def5 4/20/22 6:30 6:30 AM 6:30 AM 19
    ab6 6 def6 4/20/22 6:30 6:30 AM 6:30 AM 22
    ab7 7 def7 4/20/22 6:30 6:30 AM 6:30 AM 19.5
    ab8 8 def8 4/20/22 6:30 6:30 AM 6:30 AM 18.7
    ab9 9 def9 4/20/22 6:30 6:30 AM 6:30 AM 15.4
    ab10 10 def10 4/20/22 6:30 6:30 AM 6:30 AM 15.9

    Want to create a stacked bar chart (gantt chart) kind of chart with start time on the x-axis and a line or block starting at time t (start time) and extends till the start time + duration and a gap of time t1 (start time of next task - stop time of previous job by the same individual
    need to plot the same row for multiple individuals who performed multiple tasks

  39. I've been using a 12 month calendar on one of my worksheets in my document. The dates used to "gray" out as the date passed, but for some reason it no longer does this. I didn't create the document and the person who did no longer works here. I don't see any conditional formatting in the worksheet, so I'm not sure how to fix it. Any help would be greatly appreciated.

    1. Hi!
      Pay attention to the following paragraph of the article above: Conditionally format dates in Excel based on the current date. Use these recommendations and set the desired color for dates less than the current one.

  40. Hi - I was hoping someone could help.

    I am trying to highlight rows based on months. So... if there was a date between 6/1/22 and 6/30/22, I need it to be a certain color, etc. Different colors for each month. I've read through conditional formatting and do not see how I add a date range.

    Thanks for your help!

  41. Greetings smart people!

    I've tried applying the information gleaned from this article, but I'm having some difficulties.

    I have a "graphics board" that I enter data into daily, wherein outstanding graphic requests are listed containing the Order Date, Due Date, the Approval Date for a given order, and other information.

    What I'm trying to do, is to highlight any rows where the Approval Date (Column G) is equal to or less than 2 days from the Due Date (Column H), not including weekends.

    As a bonus note, I'd ALSO like to automate additional formatting for the "Order Date" field (Column D), where affected rows are further modified if the values are equal to or less than 3 days from the Due Date (again, not counting weekends.) If I can fix the formula for the Approval vs. Due Date argument, I'm sure I could duplicate it for the other two columns.

    Grateful for any help you folks can provide!

    Thank you,

    Miranda

  42. Hello! If I needed to highlight any cell on my worksheet that contained any date, how would I do that?

  43. I have read and looked at this thread and by far it is very detailed and more comprehensive than any other resource out there.
    I have been trying to build conditional formatting formula for a sequence of logical arguments working with 2 columns that have different dates. My table is a blank table that needs filling in.
    I need conditional formatting to highlight the entire row based on logical arguments based on the 2 dates from 2 columns, for instance if one date is earlier than today and the second date from column is an empty cell, to be yellow and the rest of the table not to be formatted. I sort of figured out 2 arguments with and formula and dates on the other column, but I need if possible to create if that particular cell is blank.
    If not how could I link the argument to an additional column that has text as yes it no.
    In conclusion I will need 4 formulas and not sure which one is taking priority and hie to have 4 colours on my table.
    Thank you very much!
    Best regards
    Nico

  44. I'm working on trying to conditionally format 1 column using a 3 color scale based off the dates within those cells. These are all shipping deadlines in, lets call it column C, which is linked to other columns based off of inbound ETA's. The minimum value would be 3 days or less to the date within said cell, midpoint would be 7 to 4 days within said cell and the maximum would be anything greater than 8 days. I've read this post a few times and can't exactly find a situation that I can model a formula off of but more than likely I'm so confused I just don't see or get it. The today function wouldn't work for this because the current date isn't a factor - the cells in question I've just added a formula to tack on 15 days from another date in a separate column which is doing a lookup in a different sheet. Point is the dates are constantly changing so not sure how to set up the formula / what technique to use to format.

    1. Hello!
      Create three conditional formatting rules for your conditions separately for each color. All examples are in this article. Instead of the TODAY function, use a reference to your cell with the date. I hope it helps.

  45. So I have a large excel sheet full of training dates expiry's
    I am trying to have it so 3 months before the training expiry the cell goes orange and on the day and after goes and stays red and green if the training is otherwise ?
    could you help please i really really need this would make my life so much handier for work

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. Pay attention to the following paragraph – How to highlight dates within a date range. There is an answer to your question. Create a separate conditional formatting rule for each condition.

  46. Good afternoon sir, the situation is like this, on the top it's categorized as per month from January to December, and I have a date and due date on the left side, by subtracting the date and due date, how can I make the difference of date and due date appear on the exact month with conditional formatting?

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

      1. Good morning sir from the Philippines.

        Yes sir, the formulas are working but it will take time for me to do that, because I have a series of dates and due dates on the left side, if I drag it all down it will be okay for that certain month, but if I drag the formula to the right since the months starts with January the formula will not work, for the formula is just for January only.

        My worksheet on the left side column A the name of the product, B and C are the dates and due dates respectively, for Columns D onwards are the months starting January to December. What I wanted to do for example, the due date is on January 7 and the date will be January 9 so the difference will be 2 days, making the formula it will pop 2 days under the month of January, but for the other list with other months doesn't fall for the right month with their difference in days.
        That's my problem sir, the differences made from other products list doesn't fall for the right months as the products list with their due dates.

        Thank you for the help in advance.

        Your help is always appreciated...

        1. Hello!
          I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

          =IF(MONTH($C2)=COLUMN()-3,$C2-$B2,"")

          Write this formula in D2 and copy it to the right along the row. The MONTH function will determine the desired month.

          1. Good morning sir, what I meant to that statement was, the difference of the given date and due date should fall under the months given. For example,
            Due date is January 7, date finished is January 9 their difference is 2 days right, so the 2 days difference should fall under the Month of January.
            But if the next product's due date is February 9, and it finished February 12 their difference would be 3 days and should fall under the month of February.
            The difference of the series of products have different Due Dates that should fall under its respective months.
            My point sir is when I drag the formula down and from left to right, it doesn't go that way, the difference of the day doesn't fall on the exact month. If the due date's on January the difference should fall on January but if it's on April the difference of the day should fall on April too, from the column D - Column O (January to December)

            I will try the method you have given.

            Thank you for the support and it's always appreciated.

  47. Hi, I am trying to highlight dates that are a week from due and then others that are past due. When creating those rules it asks to highlight the cells. Some of the cells are not currently in use but could be added to later, is there a way to only highlight the ones that are in use and the blank cells are white until there is a date entered?

    1. Hello!
      Add a condition to the conditional formatting formula that the cell must not be empty. For instance,

      =AND(B1-A1<7,B1<>"")

  48. Help with filling of todays Date. If I fill something in column B todays date must fill automatic in column A.
    Please assist

  49. So I have a list of a bunch of different Dates and i need them to turn green after 7 days have passed from that date. Ex: the original date is 1/12/22 so the box should turn green on 1/19/22 what would be the formula for this ?

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

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

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

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