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())<15,(D6-NOW())>0)
Condition2:
=D6<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.<< 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<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<NOW()
