MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula Help for Scheduling


Posted by Scott Currie on March 27, 2001 6:06 AM

I was looking for a lot of help...I am creating a schedule keeper for our office and have hit a roadblock. Any help would be greatly appreciated.
Here it goes:
Col A - Visits per year (varies could be 2, 3, 4 etc.)
Col B - Date of Scheduled Service
Col C - Date Service Actually Done
Col D - Next Service Date
I was looking to do the following...
Col B - I want to turn Italic RED within 2 wks. of actual date, and turn Italic BLUE after date in box passes.
Col C - I will type in date service is done, effectivly making date in Col B go back to Italic BLACK.
Col D - I want this to add Col A and Col C. (Eg. 3/28/01 service is done and it will be 60 days till next service so Col D will automatically go to 5/28/01) Then I want it to turn Italic RED within 2 wks. of actual date, and turn Italic BLUE after date in box passes.

Any ideas?


Posted by mseyf on March 27, 2001 7:04 AM

(assuming data starts in row 6)
this may get you started
the formula in column D:
=IF(C6<>"",C6+(360/A6),"")

then you need to Conditionally format (Format>Conditional Formating...) column B.
the Condition1 formula would be:
=AND(ISBLANK(C6),(B6-NOW())<15,(B6-NOW())>0)
set the formatting to red italics, click add to add a formula for Condition2:
=AND(ISBLANK(C6),B6<NOW())
and set the format to blue italics

you can use the same conditional formating formulas in column D, but leave out the ISBLANK(C6) part of the formula. Then copy row 6 down.

hope this gets you started

Mark

Posted by Scott Currie on March 27, 2001 7:57 AM

>>you can use the same conditional formating formulas in column D, but leave out the ISBLANK(C6) part of the formula. Then copy row 6 down.<<

Thanks for the help. Everything worked GREAT except this part that I have copied. I cant seem to get this to work.
Again THANK YOU very much.

: I was looking for a lot of help...I am creating a schedule keeper for our office and have hit a roadblock. Any help would be greatly appreciated. : Here it goes

Posted by mseyf on March 27, 2001 10:26 AM

Sorry:
Data Validation-
Column D, Condition1:
=AND((D6-NOW())&LT;15,(D6-NOW())>0)
Condition2:
=D6&LT;NOW()

HTH

Mark

>>you can use the same conditional formating formulas in column D, but leave out the ISBLANK(C6) part of the formula. Then copy row 6 down.<&LT; Again THANK YOU very much. : (assuming data starts in row 6) : this may get you started : the formula in column D

Posted by mseyf on March 27, 2001 10:28 AM

=D6&LT;NOW()

Posted by mseyf on March 27, 2001 10:33 AM

the 'less than' sign seems to be causing the formula to display incorrectly in the message - look in the comments window to see the correct formula for Condition2

Mark

=D6&LT;NOW()