using multiple IF statement with 3 conditions

Question

Date in = when order comes in
Due Out = "Date in"+ 14 days
Today= "today's date" to determine "Current Status" where i have put IF formula:
 IF(Due out<Today,"Over Due,"Current")

Over Due Days= =IF(Current Statues="current","0",Today-Due Out)

Actual Out= Date when order went out/completed

i am stuck at: Once the order is completed, Is it possible to replace "Over Due Days" (which is showing days overdue based on today's date) with "Actual Out"- "Due Out" ( to show the total delayed days which is the final status).

not sure if have explained it correctly, your help will be much appreciated - this is more related to how to put logic.

You are new here!, Please try to program the sub in VBA and show us the code and point where you are stuck. I would expect that you will solve it by yourself — Christine Ross
– Christine Ross, Commented Oct 19, 2016 at 12:39
You could change your current status formula to: IF(AND(Due out<Today,ISBLANK(Actual_Out)),"Over Due",IF(ISBLANK(Actual_Out),"Current","Completed")) Then the formula in Overdue Days will still show the differential. — Jacob Edmond
– Jacob Edmond, Commented Oct 19, 2016 at 12:39
@JacobEdmond Excellent, cant thank you enough, worked very well — victor
– victor, Commented Oct 20, 2016 at 21:24

Pomul · Accepted Answer · 2016-10-19 12:52:20Z

1

Would be easier with actual cells references, but try this in Over Due Days cells:

=If(IsNumber('Actual out'),('Actual Out'-'Due Out'),IF(Current Status="current","0",Today-Due Out))

This checks if there is a number in your Actual Out cells and if not uses the if statement you already had working for you. You could use Data Validation to ensure only Dates are entered in your Actual Out.

edited Oct 19, 2016 at 12:52

answered Oct 19, 2016 at 12:46

Pomul

3903 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

victor Over a year ago

Thank you Pomul, much appreciated.

victor Over a year ago

somehow it messing up the status as Status=Due out-Today so even the actual out<due out (which means completed on or before the deadline) its showing over due.

Pomul Over a year ago

I think something like this would work: =IF(ISNUMBER(Actual Out),IF(Actual Out<=Due Date,"Out in time",TODAY()-Due Out),"Over Due")

Tim Schmidt · Accepted Answer · 2016-10-19 12:40:38Z

1

I dont know if I get you right but i think this might be a solution:

=IF(ISBLANK('Actual Out');TODAY()-'Date In';'Actual Out'-'Date In')

This formula calculates the "Over Due Days" with the current as long as the order isn´t completed and calculates the "Actual Over Due" as soon as the order is completed.

It uses the 'ISBLANK' function to check if there already is a date when the order was completed. Based on the Result the formular calculates with either the current date or the date that was insertet.

Hope I could help.

answered Oct 19, 2016 at 12:40

Tim Schmidt

1,3071 gold badge16 silver badges30 bronze badges

Comments

sn152 · Accepted Answer · 2016-10-19 12:44:49Z

0

From what I understood from your question, I have came out with the below. Check and let me know if this is what you are looking for. Actually, you can remove the row "Over Due Days", as it is shown in the row "Current Status"

answered Oct 19, 2016 at 12:44

sn152

2761 silver badge8 bronze badges

2 Comments

victor Over a year ago

Day in 28/09/2016 Today 20/10/2016 Due Out 12/10/2016 Actual Out 20/10/2016 Over due days 0 Over Due Days current

victor Over a year ago

thank you for putting it together, somehow when I tried this with my dates it not giving accurate over due days/current status

Collectives™ on Stack Overflow

using multiple IF statement with 3 conditions

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related