**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.*