Date change
Posted by Scott Currie on December 28, 2000 7:03 AM
I am looking for some help....I am trying to create a function that will do the following:
I have a column with a service date (12/28/00)-and-
a column with a service interval (say every 3 months)
I want to change 12/28/00 to RED when the true date is 14 days or less of the 3 month date.
Example:
If work was done on 12/28/00 and next service is 3/28/01, when I am at 3/20/01; 12/28/00 will go RED.
Any help would be GREATLY appreciated.
Posted by Aladin Akyurek on December 28, 2000 8:33 AM
Assuming the service date is in A1 and the date for the next service in B1, go to cell A1, activate Conditional Formatting, and choose "Formula is". Then type the formula
=$B$1-$A$1<=14
Activate "Format" of the Conditional Formatting and choose the Font tab. Choose the Color.
Aladin
Posted by Scott Currie on December 28, 2000 8:54 AM
Aladin,
I tried the above and it did not work. Wouldnt the (TODAY) function have to be in the function at some point? I want the actual date to reference that 12/28/00 date somewhere along the line.
Eg:
12/28 date of service Column A1
2/28/01 new service Column B1
If the actual date is 2/15/01 I want 12/28 in column A1 to go RED.
Posted by Aladin Akyurek on December 28, 2000 9:30 AM
: I am looking for some help....I am trying to create a function that will do the following: : I have a column with a service date (12/28/00)-and- : a column with a service interval (say every 3 months) : If work was done on 12/28/00 and next service is 3/28/01, when I am at 3/20/01; 12/28/00 will go RED.
I was merely showing the mechanism. For it to work, the values in A1 and B1 must be Date-formatted. How these dates are constructed must of course reflect the conditions of your problem.
ps. An Excel file is underway to you, showing the mechanism I described.
Aladin
Posted by lenze on December 28, 2000 9:33 AM
I am looking for some help....I am trying to create a function that will do the following:
Try This:
If A1 has the date, B2 the number of months, then use conditional formatting like this:
Formula is =(EDATE(A1,B1)-TODAY()<=14.
If you do not have the EDATE function available, you will have to choose tools, add-ins and select data analysis package.
lenze
