Question description:
This user has given permission to use the problem statement for this
blog.
NEED A FORMULA TO SHOW THE FOLLOWING:
IF (Q3) IS AFTER TODAY & (R3) IS BLANK,THEN "BEHIND SCHEDULE". IF (R3) IS POPULATED THEN "COMPLETE". IF (Q3) IS
Solved by E. U. in 42 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
24/05/2018 - 11:16
MY TEAM IS 15
Excelchat Expert
24/05/2018 - 11:16
Hello! Welcome to Got It Pro-Excel.
User
24/05/2018 - 11:16
MY COMPANY IS 3500
User
24/05/2018 - 11:16
NEED A FORMULA TO SHOW THE FOLLOWING: IF (Q3) IS AFTER TODAY & (R3) IS BLANK,THEN "BEHIND SCHEDULE". IF (R3) IS POPULATED THEN "COMPLETE". IF (Q3) IS <TODAY THEN "ON TRACK", IF Q3 & R3 ARE BLANK THEN "ON TRACK"
Excelchat Expert
24/05/2018 - 11:17
Oh yes, I'm so glad to assist you today
Excelchat Expert
24/05/2018 - 11:17
Would you mine to share your data with us to create a formula for you instantly?
User
24/05/2018 - 11:18
CANNOT SHARE MY DATA
Excelchat Expert
24/05/2018 - 11:18
Okay, can you enter some sample data at our Document preview for us to resolve?
Excelchat Expert
24/05/2018 - 11:19
In this method we could evaluate your data at Q3 and R3 that you are referring for
User
24/05/2018 - 11:22
does that help?
Excelchat Expert
24/05/2018 - 11:22
sure, thank you
Excelchat Expert
24/05/2018 - 11:22
Please give me few more minutes to create the formula for you.
Excelchat Expert
24/05/2018 - 11:22
I will put the formula at P3, will this work for you?
User
24/05/2018 - 11:23
yea
User
24/05/2018 - 11:23
thanks
Excelchat Expert
24/05/2018 - 11:23
Or S3?
User
24/05/2018 - 11:23
doesnt matter
Excelchat Expert
24/05/2018 - 11:23
Thanks for the confirmation
Excelchat Expert
24/05/2018 - 11:23
Please hold on
Excelchat Expert
24/05/2018 - 11:31
Hello
Excelchat Expert
24/05/2018 - 11:32
he formula have been created, please check if this works for you
Excelchat Expert
24/05/2018 - 11:33
So the formula looks like this: =IF(OR(Q3<=TODAY(),AND(Q3="",R3="")),"ON TRACK",if(AND(Q3>today(),R3=""),"BEHIND SCHEDULE","COMPLETE"))
Excelchat Expert
24/05/2018 - 11:34
wherein the conditions above is the basis of the formula
Excelchat Expert
24/05/2018 - 11:34
You may edit the date at your Q3 or R3 for testing
Excelchat Expert
24/05/2018 - 11:34
And let me know for any concern
User
24/05/2018 - 11:34
doesnt appear to be working, just puts in on track for most
User
24/05/2018 - 11:35
i am double checking though
Excelchat Expert
24/05/2018 - 11:35
Do you have the same format of date at Q3 and R3?
User
24/05/2018 - 11:36
yes, ddmmyyyy
Excelchat Expert
24/05/2018 - 11:36
Opps
Excelchat Expert
24/05/2018 - 11:36
was set mm/dd/yyyy
User
24/05/2018 - 11:36
can that be altered?
User
24/05/2018 - 11:36
we are in the uk so we dont ever use mmddyyyy
Excelchat Expert
24/05/2018 - 11:36
because the default of the resulting today looks like this format mm/dd/yyyy
Excelchat Expert
24/05/2018 - 11:37
Please try to put =today() at cell to see if it produces the same format?
Excelchat Expert
24/05/2018 - 11:38
=TODAY()
Excelchat Expert
24/05/2018 - 11:38
Please check my P2
User
24/05/2018 - 11:39
i hav e today() in A1
Excelchat Expert
24/05/2018 - 11:39
then how was the format of your A1
Excelchat Expert
24/05/2018 - 11:40
how does it shown on your sheet?
User
24/05/2018 - 11:40
so i have altered the formula and it still is showing on track for everything
Excelchat Expert
24/05/2018 - 11:40
The cause of that is because you don't have the same format
Excelchat Expert
24/05/2018 - 11:41
You must need to convert the date format first into UK format
User
24/05/2018 - 11:41
i added in a new date on the spreadsheet in your window and p4 shouldnt say on track
User
24/05/2018 - 11:42
should be behind as todays date is more than target date
Excelchat Expert
24/05/2018 - 11:43
It is because of your rule mentioned IF (Q3) IS <TODAY THEN "ON TRACK",
Excelchat Expert
24/05/2018 - 11:43
and 5/22/2018 is less than today 5/24/2018
User
24/05/2018 - 11:43
sorry should be >today
Excelchat Expert
24/05/2018 - 11:44
For greater than today, you made the result as "BEHIND SCHEDULE"
Excelchat Expert
24/05/2018 - 11:44
So, shall we change them alternately?
User
24/05/2018 - 11:44
yes
User
24/05/2018 - 11:44
wonderful
Excelchat Expert
24/05/2018 - 11:45
Alright, I will revise it once more
Excelchat Expert
24/05/2018 - 11:45
Please hold on
Excelchat Expert
24/05/2018 - 11:46
Please take a look for the revision now and let me know
Excelchat Expert
24/05/2018 - 11:48
Any more concern regarding the output of the formula?
Excelchat Expert
24/05/2018 - 11:51
Up there?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.