Ifand, ifor, or just if with networkdays??

Hello all,
I have been asked to build a spreadsheet at work to track orders. I am able to build most of it but an stumped on a few formulas.

Any help is appreciated!

Scenario A: Need Age to calculate NETWORKDAYS if 3 conditions are met.
1. If units are less than or equal to 99 for the first Age, greater than or equal to 100 for the second age.
2. If no date in column B or D, result to be left blank
3. If column B has date of "00/00/00" result in age of 0 days

Scenario B: Need 3 formulas in this scenario:
1. Calculate for each customer how many days behind an order is based on it's order date and it's goal date. If it's within goal date range, return "Current", otherwise result in number of days behind the order is.
2. Calculate for each customer it's oldest date order based on units
3. Calculate the overall oldest order date

EXAMPLE 2.22.24.xlsx

You are the best!

Thank you!

Quick follow up on this. If there are no dates in the range, I would have assumed the following would work but it returns True or False instead. Any suggestions?

Original Formula:
=MINIFS($A$24:$A$32,$B$24:$B$32,A36,$C$24:$C$32,"<100")

Tried but returns True or False instead of "Current":
=IFERROR(MINIFS($A$24:$A$32,$B$24:$B$32,A36,$C$24:$C$32,"<100")=0,"Current")

what do you actually want =MINIFS($A$24:$A$32,$B$24:$B$32,A36,$C$24:$C$32,"<100") acutally returns a 0 but is if formatted to be a blank if you used dd/mm/yyyy;"1/0/1900";;@ as the format

the column B value is blank if there in no data meeting the criteria


=MINIFS($A$24:$A$32,$B$24:$B$32,A36,$C$24:$C$32,"<100") returns a number 0 if it is blank or a number over 45000 if it is a date (all dates are the number of days since 1st Jan 1900

so MINIFS($A$24:$A$32,$B$24:$B$32,A36,$C$24:$C$32,"<100")=0 will return a true if there are no values and a false if there is a date as it does not equal 0 at no time does an error occur so the current bit never gets triggered.

what cell do you want current in?

How about this in C18 and copy down?

Please Login or Register  to view this content.

That works! Thank you!
Deb

You are welcome.