Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc