IF Functions using 4 arguments involving dates needing 4 results

I'm creating a tracking spreadsheet at work.

Column A = Turn In Date
Column B = Status
Column M = Date Shipped

What I need is a formula in B2 that says
If date in A2 is 45 days past today's date, then "Over 45 Days"
If there's any date in M2, then "Closed"
If neither are true, then "Open"
If there is no date in A2 or M2, then leave blank.

I tried = IF (A2 > = Today()+45, "Over 45 Days", If ( M2 < Today(), "Closed", "Open") )

but that obviously didn't work.

After I get the formula situated, I'm going to add some conditional formatting to highlight the rows that are open yellow, closed green, and over 45 days light red.

Any help would be appreciated!

Something like:

Formula:

Please Login or Register  to view this content.

Havent tested you may need to make adjustments

Mehmetcik:
Circular reference warning, everything yielded 0

But thank you!

Originally Posted by Zer0Cool

Something like:

Formula:

Please Login or Register  to view this content.

Havent tested you may need to make adjustments

Too Many Functions error. Thank you though

Please see attached.

In B2, try this:

=IF([@[TIN DATE]]="","",IF([@[SHP DATE]]<>"","Closed",IF(TODAY()-[@[TIN DATE]]>45,"Over 45 Days","Open")))

or this (without table references):

=IF(A2="","",IF(M2<>"","Closed",IF(TODAY()-A2>45,"Over 45 Days","Open")))

Originally Posted by mehmetcik

My formula seems to work

Almost. Cell b2 should say Over 45 Days

Originally Posted by 63falcondude

In B2, try this:

=IF([@[TIN DATE]]="","",IF([@[SHP DATE]]<>"","Closed",IF(TODAY()-[@[TIN DATE]=]=>45,"Over 45 Days","Open")))

or this (without table references):

=IF(A2="","",IF(M2<>"","Closed",IF(TODAY()-A2>45,"Over 45 Days","Open")))

BOOM! You got it!

Thanks everyone for all your help!!

You're welcome, glad we could help.