Automatically fill cell in a colour if it has any date format in it

**BBoyAl** · 04-09-2012, 03:00 AM

Hi, Good Morning.

I am trying to find a way to condition a group of cells so that if any date appears in a cell it changes to a colour (the colour initially is not important to me).

I have searched hard in the conditional format settings but the date functions are predefined to fill the cell if it has todays date, yesterdays, past 7 past month etc. I have tried to write a formula but have not been successful.

I am using 2007 but will need to be compatible with Excel 2003.

Any help or ideas are very much appreciated. Thanks.

**Fotis1991** · 04-09-2012, 03:58 AM

Hi

Do you use this column, only for dates?

Or, also for text, or numbers?

**BBoyAl** · 04-09-2012, 11:31 AM

Hi,

Sorry for the delayed reply.

When the cell needs to change colour I will put a date in. Before that it will have text in it and it will fill another colour. To clarify, when I put the date in the cell there will be no other numbers or text.

Thanks.

**Fotis1991** · 04-09-2012, 02:32 PM

Hi

If i understand well, you don't have numbers in this column.

Then in Conditional Conditional rules, you can type something, like this.

PHP Code:


=AND($A1<>"";$A1<TODAY()+150000)

If, you have numbers in this column, then this will not work.

Hope that helps you.

**Marcol** · 04-09-2012, 02:59 PM

@ Fotis

Doesn't this do the same thing?

Please Login or Register  to view this content.

**BBoyAl** · 04-09-2012, 04:45 PM

Thank you both for these formulas. I have tried them quickly and with a little work from me on the way I am inputting my data it may work.

Unfortunately I have run out of time to continue trying this evening. However in the next day or so I shall find out. I will post again to let you know how I get on.

Thank you very much again, have a good week.

**Fotis1991** · 04-10-2012, 02:22 AM

@Marcol

..Doesn't this do the same thing?

Yes. does the same thing. Your suggestion It's much better and shorter and obviously (i)not thought of this. So ? What, you mean?

**Marcol** · 04-10-2012, 05:32 AM

Dates, potentially, mixed with numbers are never easy to handle and it is always better to avoid this scenario.

Some restrictions can be added to limit the chances of "errors"
e.g.

Please Login or Register  to view this content.

So any number greater than or equal to 36526, ("01/01/2000"), including decimal numbers can be read as a date.
Similarly

Please Login or Register  to view this content.

restricts the range of numbers to 365265 > 43830.
Obviously you can replace DATEVALUE("01/01/2000") with its' serial date, but this way is easier for others to read your intentions.

Data Validation can also help to control this situation.

**Fotis1991** · 04-10-2012, 05:47 AM

Aha...Yes. You're right.

Just cleaver! I'll keep it, in my mind.

Automatically fill cell in a colour if it has any date format in it

LinkBack

Thread Tools

Rate This Thread

Display

Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Re: Automatically fill cell in a colour if it has any date format in it

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions