past due date formula/automation

Dhoke

I am trying to setup an automation or formula that will check a box complete if an item has gone past the 30,60,90 day dates and the received date is not blank. I tried created an automation but it is not working 100% correctly as I want it to check the boxes if the received by date is past the date in the 30,60,90 columns. Might be a formula that is needed instead but the ones i researched didn't work on my sheet. Please help!

Protonsponge

Hello @Dhoke,

I think you should be able to achieve what you are looking for with a column formula as per below. The formula is asking IF TODAY minus the [Requested Date] is greater than 30, AND [Date Received] is blank then 1 (check the box) … if not then 0 (leave the check box blank)

You can change the 30 in the formula below to 60, and 90 for your other columns.

All seems to be working in the demos below,

=IF(AND(TODAY() - [Requested Date]@row >= 30, ISBLANK([Date Received]@row)), 1, 0)

=IF(AND(TODAY() - [Requested Date]@row >= 60, ISBLANK([Date Received]@row)), 1, 0)

=IF(AND(TODAY() - [Requested Date]@row >= 90, ISBLANK([Date Received]@row)), 1, 0)

I hope that is helpful to you in someway,

Protonspounge

Dhoke

Thanks but it is still not working correctly. It is checking the boxes but I need it to still check boxes for 30,60,90 days past due if the received by date is past the 30,60,90 day dates in the columns next to it. Make sense?

Dhoke

so basically right now, if there is a received by date it is not capturing that it WAS past due.

Protonsponge

Hello @Dhoke,

Would the following work for you?

=IF(AND(TODAY() - [Requested Date]@row >= 30, ISBLANK([Date Received]@row)), 1, IF([Date Received]@row - [Requested Date]@row >= 30, 1, 0))

=IF(AND(TODAY() - [Requested Date]@row >= 60, ISBLANK([Date Received]@row)), 1, IF([Date Received]@row - [Requested Date]@row >= 60, 1, 0))

=IF(AND(TODAY() - [Requested Date]@row >= 90, ISBLANK([Date Received]@row)), 1, IF([Date Received]@row - [Requested Date]@row >= 90, 1, 0))

I hope that is helpful to you in someway,

Protonspounge