FORMULA

John Wann

FORMULA TO COUNT IF ENGINEERING REQUEST DATE IS 3 DAYS LATER THAN TODAY OR IF PURCHASING REQUEST DATE IS BLANK

Mark Cronk

Found a couple mistakes. Try:

=IF(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=today(), ISBLANK([purchasing request date]@row)), 1, IF(OR(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=TODAY()), ISBLANK([purchasing request date]@row)), 0, ""))

Mark

Mark Cronk

Hi,

Try:

=COUNTIF([engineering request date]:[engineering request date], @cell =TODAY(3))+ COUNTIFS([purchasing request date]:[purchasing request date], ISBLANK(@cell), requester:requester, IS TEXT(@cell))-COUNTIFS([purchasing request date]:[purchasing request date], ISBLANK(@cell), requester:requester, IS TEXT(@cell), [engineering request date]:[engineering request date], @cell =TODAY(3))

A bit long but it counts engineer dates and adds blank purchase request dates, then subtracts cases where both conditions are true to eliminate double counting.

Work?

Mark

John Wann

Mark,

Thanks for the info, but I was unable to get this to work. I am looking to get a 1 in the formula column if these condition are met. Engineering request date is past 3 days and purchasing request date is blank, a 0 in formula column if neither condition is met.

Thanks,

Mark Cronk

Hi John, Thanks for clarifying. The formula to do that is:

=IF(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=today(), ISBLANK(([purchasing request date]@row), 1, IF(OR(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=TODAY()), ISBLANK(([purchasing request date]@row), 0, ""))

Work?

Mark

John Wann

It did not, got incorrect Argument set.

Thanks,

Mark Cronk

Found a couple mistakes. Try:

=IF(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=today(), ISBLANK([purchasing request date]@row)), 1, IF(OR(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=TODAY()), ISBLANK([purchasing request date]@row)), 0, ""))

Mark