excel help

j2011

In excel 2003 how can I make a column that has text in it and formated as text have a blue background if the date is before today?

I can do it if the column is a column of dates but not if it is text.

Ashley3283

If the date is formatted as text and not date/time then Excel is not going to know that it is a date if you see what I mean.
If they were formatted as dates then you would conditional format the column then use formula is =A1<NOW(). But sounds like you already know this?

WhiteChristmas

Try something like =(1*a1)<now()

esuhl

You should be able to get the results you want if you put this formula into the "conditional formatting" settings of cell A1, then copy the formatting to the other cells in that column.

=IF(DATE(YEAR($A1),MONTH($A1),DAY($A1))<TODAY(),TRUE,FALSE)

Hope that helps...

12bdebt3

You need to use conditional formatting: http://www.timeatlas.com/5_minute_tips/general/how_to_use_conditional_formatting_to_highlight_data

or VBA but that's too advanced for what you seem to need.

Dave_C_2

Not guaranteed, you could try formatting the text as a date. Select the column or the range of dates then Format>cells>date then pick the date format nearest to the text. It's worked for me in the past.
Don't forget to make a copy of the excel file first - just in case.

Thereafter it's a simple conditional format.
In a cell say C1 enter the value =now()
Highlight range of cells or column Format>conditional format then in the boxes Cell value is > Greater than > =$C$1 finally select the pretty colour of your choice.

Failing that please post a few examples of the text that represents dates and we'll try to help.

Dave

johnmc

A date is actually a number, interpreted by Excel. You can do arithmatic with numbers.

Text is not something you can use that way.

"10 April 2011" is less than "1 January 2011", for instance.

So, no, there's no way you can do that.

stevemcol

You can make another cell equal to the text cell, then hide it if you want. Format the second cell as 'date', then conditionally format the first cell based on the date in the second.

Stompa

johnmc wrote: »

A date is actually a number, interpreted by Excel. You can do arithmatic with numbers.

Text is not something you can use that way.

"10 April 2011" is less than "1 January 2011", for instance.

So, no, there's no way you can do that.

The DATEVALUE() function will convert a textual date to the appropriate number though.

So conditional formatting set to:

'Formula Is' =DATEVALUE(A1)<TODAY()

should work just fine, assuming the dates are not in some really odd format.