Autofill a static date using automation

Kevin7859

I am using a formula to compare the date created and the date due to return a green check, if not then a red 'X', but the date due column has to be a date.

The automation workflow to record the date due if a specific value in a dropdown list is selected only allows to record the current date and if I want to force a specific value that breaks the date comparison formula.

Any ideas to resolve this would be helpful.

=IF(Date@row <= [Date Due]@row, "Yes", IF(Date@row > [Date Due]@row, "No"))

Paul Newcome

What would be the logic for the date that should be in the date due column?

Kevin7859

If the document category field value =X then record the date 07/01/2024

Kevin7859

I tried a helper column to record the text date 07/01/24, but now sure how to convert that to the date for the date due, because if I use =the other cell value then I get an Invalid Operation error on the Verification column.

Kevin7859

I think I got it figured out, I used the helper column for the automation to record a value for the static due date. then in the actual Date due I used the Date formula to convert that text date to a DATE and then that works in the date comparison formula:

=IFERROR(DATE(VALUE(RIGHT([Date due helper]@row, 4)), VALUE(LEFT([Date due helper]@row, 2)), VALUE(MID([Date due helper]@row, 4, 2))), "Review")

=IF(Date@row <= [Date Due]@row, "Yes", IF(Date@row > [Date Due]@row, "No"))

Paul Newcome

Why not just use a formula in the Due Date column directly?

Kevin7859

Paul, what would that look like? Would it be just a longer formula like I developed?

Paul Newcome

That depends on your exact needs, but from the look of it, you just need to output July 1, 2024 when [Document Category] is "1. ………".

=IF([Document Category]@row = "1. ……….", DATE(2024, 07, 01))