Help using If, ISBlANK, AND in a formula to return a status of Past Due, Not started, Approved

Kelly Dawson

I am trying to create an If/Then formula that will look at date columns to determine if a Biennial review is due, approved, past due.

I want the to look at if there is an initial approval date, to determine if It needs to be reviewed, not approved leave status Blank

If Approved look at Biennial Approval to determined if its been approved and return approved in column,

If not then look at Biennial Review Date to determine if it is past it's due date, but with in our fiscal year 7/1/23-6/30/24 FY24, and return past due date,

If it is past due date and past Fiscal Year End of the due date, then Past Due, so Biennial Review Due Date 6/30/21 is "Past Due", where as 1/9/24 is "Past Due Date"

I have the formula working without looking at the Initial approval Date-See Formula below

=IF(ISBLANK([Biennial Approval Date]@row), IF([Biennial Review Due Date]@row <= DATE(2023, 7, 1), "Past Due", IF([Biennial Review Due Date]@row < TODAY(), "Past Due Date", "Not Yet Due")))

when I try to add the comparison #incorrect argument error is returned, here is the formula

=IF(AND(ISBLANK([Initial Approval Date]@row), IF(ISBLANK([Biennial Approval Date]@row), IF([Biennial Review Due Date]@row <= DATE(2023, 7, 1), "Past Due", IF([Biennial Review Due Date]@row < TODAY(), "Past Due Date", "Not Yet Due")))))=IF(AND(ISBLANK([Initial Approval Date]@row), IF(ISBLANK([Biennial Approval Date]@row), IF([Biennial Review Due Date]@row <= DATE(2023, 7, 1), "Past Due", IF([Biennial Review Due Date]@row < TODAY(), "Past Due Date", "Not Yet Due")))))

And When I try to add the return of approved when there is a date in the Biennial Approve date Column I get #Unparseable returned, which I believe is syntax error in the formula.

=IF(ISBLANK([Biennial Approval Date]@row), IF(ISDATE([Biennial Approval Date]@row), "Approved", IF([Biennial Review Due Date]@row <= DATE(2023, 7, 1), "Past Due", IF([Biennial Review Due Date]@row < TODAY(), "Past Due Date", [GL String]43"Not Yet Due"))))

I appreciate any help, and if I am using the wrong formulas or there is an easier way I am open to suggestions.

heyjay

You have and extra equal sign here:

Looks like a comma is missing somewhere around here: