RYGB Status Automate

HardWork

I have several columns but each section has a status section if the document is ON FILE, RYGB.

Red - Missing Documents

Yellow - Processing

Green - Complete

Blue - Not Applicable

I am trying to set it up so that if all the ON FILE sections are Green that the STATUS will update to Green. And if any of the other sections are Blank status is Yellow. Lastly, if the TAT is greater than 5 days that the status turns Red.

My TAT formula (not sure if I have this set up correctly. I am trying to see based off the Requestor Auto Date a running time till the overall status turns Green

=VALUE(MID([REQUESTOR AUTO DATE]@row, FIND(" ", [REQUESTOR AUTO DATE]@row) + 1, FIND(":", [REQUESTOR AUTO DATE]@row) - (FIND(" ", [REQUESTOR AUTO DATE]@row) + 1))) + IF(VALUE(MID([REQUESTOR AUTO DATE]@row, FIND(" ", [REQUESTOR AUTO DATE]@row) + 1, FIND(":", [REQUESTOR AUTO DATE]@row) - (FIND(" ", [REQUESTOR AUTO DATE]@row) + 1))) <> 12, IF(FIND("P", [REQUESTOR AUTO DATE]@row) > 0, 12), IF(FIND("A", [REQUESTOR AUTO DATE]@row) > 0, -12))

My On File section example:

I have 28 On File column categories.

I feel like I am almost there but I just can't get my head to wrap around these three pieces.

1. Correct TAT formula
2. Status update based on ON FILE row status
3. Status update to Red if TAT is >5 days from Requestor Auto Date/Time

Mike TV

@HardWork

What is the TAT formula doing? Just returning the military time for the hour the row was created in?