CODEWITHSUNDEEP
excelexcel-formula
victor
victor

Reputation: 105

using multiple IF statement with 3 conditions

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.

Upvotes: 0

Views: 422

Answers (3)

Pomul
Pomul

Reputation: 390

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.

Upvotes: 1

sn152
sn152

Reputation: 276

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"

enter image description here

Upvotes: 0

Tim Schmidt
Tim Schmidt

Reputation: 1307

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.

Upvotes: 1

Related Questions