Can someone point me in the right direction please. Attached is a sample project plan, and what i want to do is either :
1. compare two dates and return either a color ( red, amber & green) Green when task is complete. Amber when 7 days to go & Red when past date.
2. same as above but have on, Complete,Due or late
3. A combination of the 2??
Any help much appreciated...and any other suggestions....
Hello interested...
Not sure how on what basis you'll mark the status as completed, but basis you comments in the excel sheet, came up with below formula:-
=IF(AJ1-F5>=7,"On Time",IF(AND(AJ1-F5<7,AJ1-F5>=0),"Due","LATE"))
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Thanks Dilipandey. I put your formula in, then did some conditional format to give the colors, but if you change the dates again,the colors dont change to the correct color. ( see attached)
Thanks again for your input
hi there. maybe you could share with us some scenarios on what is correct & wrong. for eg when AJ1 is 7 Jan 2013, G5 should be showing ____ instead of ____. also, it'll be better if you use TODAY() in AJ1 instead of NOW(). the latter has timing & might complicate things
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Colors are not changing.. because still I haven't applied any conditional formatting thereThanks Dilipandey. I put your formula in, then did some conditional format to give the colors, but if you change the dates again,the colors dont change to the correct color.
I need your confirmation if the text results are ok and after that we can move to colors ... thanks.
Regards,
DILIPandey
<click on below * if this helps>
Thanks guys for the replys. I changed the cell referance to "Today" as suggested. ( no change in data result )
PROBLEM: Formula is not working. When date in cell F5 is changed it does not change to the correct status. ie if i change the cell to 01/01/13 it will say due, when it is actually late. it says late now when it is actually on time.
I wanted athe following to happen:
Staus to say "Late" (RED) when due date greater than current date in cell AJ1 (Today):
Status to say "Due" (AMBER) when upto 7 days to due date and (to alert time is running out):
Status to say "On Time" (GREEN) when more than 7 days to due date
I appreciate both your comments, and hope this explains in more detail.
I have attached again the sample book with the changes..
It should be :-Staus to go RED when due date greater than current date,AMBER when 7 days to due date and GREEN when more than 7 days
Status to Go Red and value to show "Late" when due date is Lesser than (already passed) Current Date
Formula:
Please Login or Register to view this content.
see attached:- Master Plan tracker SAMPLE(1).xls
Regards,
DILIPandey
<click on below * if this helps>
Dilipandey,
your solution works for me !!!! thanks for your input and time.( Again) marked as solved
you are welcome interested...
Cheers
Regards,
DILIPandey
<click on below * if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks