Replies to This Discussion

Permalink Reply by millenial removing Bernie tattoo on October 28, 2010 at 5:45am

Conditional formatting.

Select the cells with dates, go into the conditonal formatting dialogue, set to if less than "=today()"

• ▶ Reply

Permalink Reply by igz on October 28, 2010 at 5:58am

ok...found that. now how do i format properly?

use:
schedule date for service calls.

purpose:
need this formatting to indicate expired date/eta (ex: 3 days from creation date/follow up date)

• ▶ Reply

Permalink Reply by cutifly on October 28, 2010 at 6:11am

nice ...

envane x said:

Conditional formatting.

Select the cells with dates, go into the conditonal formatting dialogue, set to if less than "=today()"

• ▶ Reply

Permalink Reply by millenial removing Bernie tattoo on October 28, 2010 at 6:12am

Dunno how your dialogue looks like, I run an old version of XL, but for example, if you want to highlight an expired time, for example you:

1) Select the field of dates and times you want to check.
2) Go into the CF dialogue.
3) Select "Cell Value is" + "less than" + either "=now()" or "=today()" or a cell containing the current time/date. I don't know how you are keeping your dates/times formatted, I know little abotu how excel does this.
4) Select the "format" button, then set the format you want (e.g. make the background of the cell red)

• ▶ Reply

Permalink Reply by igz on October 28, 2010 at 7:02am

do i need to do that per cell or can assign to spreadsheet?

envane x said:

Dunno how your dialogue looks like, I run an old version of XL, but for example, if you want to highlight an expired time, for example you:

1) Select the field of dates and times you want to check.
2) Go into the CF dialogue.
3) Select "Cell Value is" + "less than" + either "=now()" or "=today()" or a cell containing the current time/date. I don't know how you are keeping your dates/times formatted, I know little abotu how excel does this.
4) Select the "format" button, then set the format you want (e.g. make the background of the cell red)

• ▶ Reply

Permalink Reply by millenial removing Bernie tattoo on October 28, 2010 at 7:30am

iggi said:

do i need to do that per cell or can assign to spreadsheet?

envane x said:

Dunno how your dialogue looks like, I run an old version of XL, but for example, if you want to highlight an expired time, for example you:

1) Select the field of dates and times you want to check.
2) Go into the CF dialogue.
3) Select "Cell Value is" + "less than" + either "=now()" or "=today()" or a cell containing the current time/date. I don't know how you are keeping your dates/times formatted, I know little abotu how excel does this.
4) Select the "format" button, then set the format you want (e.g. make the background of the cell red)

You need to assign it to the cells with the dates in them you want to check. You can select an entire column or row or area and only assign it once. If you choose the entire spreadsheet, every single cell in it will be checked for the condition.

• ▶ Reply

Permalink Reply by igz on October 28, 2010 at 7:54am

column(s) is what i meant.

im still doing something wrong. arg

• ▶ Reply

Permalink Reply by John Sirovatka on October 28, 2010 at 8:18am

The other option is to ad a column with an Ifthenelse statement. =if(A2>today()+3,"Past Due",A2+3)

This will put Past Due in the cell if the date in cell A2 is more that 3 days ago. Otherwise, the cell will return the due date. Be sure to format the new cell (E2) as a date or you will get a number not a date.

• ▶ Reply

Permalink Reply by Joe Studer 8.0 mi on October 28, 2010 at 9:57am

John:

The following site ( http://www.exceltip.com/ ) is an excellent resource for Excel related questions. I use it frequently.

Good luck!

• ▶ Reply