How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9666
Experience:  Worked on Macs and PC's for 20+ years.
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

I would like a cell to change color, when a date I enter into

This answer was rated:

I would like a cell to change color, when a date I enter into the cell, is reached or exceeded.





1) which cell do you want to change the color of

2) what is the date that cell should not exceed (or in which cell is that date)

3) which color do you want to change it to




Customer: replied 5 years ago.
would you like a copy of the basic speadsheet I want to develop? This spreadsheet will provide me a high level look at the progress of matters I am responsible for. As one item reaches conclusion, I will add another to the spreadsheet. There will be seven columns, and 15 or more rows. Each cell in a row will have a date entered which I would like to change to the color red when the date in the cell is reached or exceeded. The cell would remain red until I delete the date and enter the word "completed"

Ok so you want a general answer rather than me helping you set up the specific conditions?



Customer: replied 5 years ago.
Well, I am not able to write the formula which will provide the result I need. I have tried to add conditional formats to the cells, but none have worked for me.

If you want me to help you with that formula you need to provide me with specific details. I asked you a few questions but I have not received the answers yet



Customer: replied 5 years ago.
Can you provide me the conditional format formula necessary to provide the result I need? There will be over seventy cells, each cell will have a different date I am monitoring.

I can, but i doubt you will be able to make use of it as I do not have any info about the date in question, or in which cell it is located.


I suggest that you send me your file so I can set it up for you and ensure that it will work. But if you insist that I give you the formula(s) here I will need to get an accept to make sure I get compensated if it turns out that you cannot make it work


Customer: replied 5 years ago.
how do I send you the file?

Ok, go to and upload the file there (no need to sign up). You will then get a page that has the download link. Copy the download link and come back here and paste it in your reply.


If the file has sensitive information let me know before you upload it, or just try replacing the sensitive data with dummy values.


Customer: replied 5 years ago.
Nothing sensitive, I will put the data on the spreadsheet when it is completed.

Ok I am looking at your file now. Cells E4, F4 and G4 have conditional formatting which turns the cell to red if the date in that cell is greater or equal to the date in C1


Do you want to change the rules for these cells, if so what is the new rule that you want to apply?



Customer: replied 5 years ago.
I want the rule to apply to cell C3 through cell H18. I will enter a date into each cell. The cell will remain white until the date in the cell is reached or beyond. When the cell turns red, I will be alerted that a due date has been reached or exceeded.

I want to track reports which have various due dates. If a due date is reached or exceeded, I want to be alerted.

Take the example on row 4.
cell B4 shows the date I received a report.
cell C4 shows the date the investigation is due (future date). If the investigation is not completed by 1/24/11, the cell will turn red, The cell will remain red each day past 1/24/11, or until I remove the date and replace the word "completed" in the cell, and so on with cell D4, E4, F4, G4, H4 and each row thru row 18.

I may need an additional page, so I would like to be able to copy the formula, or page as needed in the future.

As you can see, my formula does not provide the effect I desire.

This is an interesting process, difficult to explain what I need.

I hope this helps.??

Ok here you go


The cells in the range C3:H18 will turn red if the date in them has been reached or exceeded


Hope this is ok. Please have a look at it and let me know if it needs any adjustments or if you have any questions, and thank you for clicking Accept if you are satisfied with the solution



John D and 3 other Mac Specialists are ready to help you
Customer: replied 5 years ago.
John D.,

Thank you very much. Sorry I could not respond sooner and accept answer. The formula acts as I desire. Thank you for your kind assisstance.

T. K.

Great! You're welcome and thanks for the accept and bonus