Excel Formula Help

[Gliffix101]

Happy Monday! Hoping this is a quick and easy one for you all.

I have four fields:

Column AE: Order Status
Column AF: Order Status Date
Column AG: Billing Status
Column AH: Billing Status Date

I need to write a formula that returns the days between the Status Date and today IF the Order Status or Billing Status equals "Outstanding". Some examples:

A) Order Status = Outstanding (9/9/18), Billing Status = Pending (9/9/18) - return the days (1) between Today (9/10/18) and Order Status Date (9/9/18)

B) Order Status = Received (9/9/18), Billing Status = Outstanding(9/8/18) - return the days (2) between Today (9/10/18) and Billing Status Date (9/8/18)

C) Order Status = Outstanding (9/9/18), Billing Status = Outstanding (9/8/18) - return the days (2) between Today (9/10/18) and the MIN of Order Date (9/9/18) and Billing Date (9/8/18).

Hoping this makes sense.

Thanks,

Bill

 

[Joe4]

Try this formula row 2:

=IF(AND(AE2="Outstanding",AG2="Outstanding"),TODAY()-MIN(AF2,AH2),IF(AE2="Outstanding",TODAY()-AF2,IF(AG2="Outstanding",TODAY()-AH2,"")))

 

[etaf]

edit

i see you have a reply

ignore

i would like to see a table showing all the
order status and possible billing status and result needed

outstanding - pending - (date calc > today() - order status date)
outstanding - outstanding - (date calc > today() - min of order status date or billing date)
outstanding - {other possible billing status} - ({date calc > today() - ????})

received - outstanding - (date calc > today() - billing status date)
received - {other possible billing status} - ({date calc > today() - ????})\

other possible order status

so we can see all possible status combinations and date calculations required\

 

[Gliffix101]

Worked perfect! Thank you.

 

[Joe4]

You are welcome.

I have it set to return nothing if neither column is Outstanding. You can easily change that by replacing the "" at the end with any text or calculation that you want.