Automated Due Dates on Creation

Hello, I’d like to request a change to the way due dates work OR can someone suggest a better way to achieve due dates that are X days before another date in a table?

Currently functionality exists to create a calculated column using a date format however it not an easy calculation to get your head around… here’s some examples…

Example: DATE(YEAR({Date}),MONTH({Date}),DAY({Date})+4)
This returns a really messy date in the format DDD MMM DD YYYY 00:00.

In order to then get this date to format nicely, you need to make the formula more complex…

Example: RIGHT(LEFT(CONCATENATE(DATE(YEAR({Start Date}),MONTH({Start Date}),DAY({Start Date})+7)),10),6)
This then formats as DD MMM YY

The issue we then have is if we are subtracting days from a date, i.e. working backwards from a launch, the logic gets confused and creates errors. our formula then looks like this;

Example: IF(DAY({Order Date})>14,RIGHT(LEFT(CONCATENATE(DATE(YEAR({Order Date}),MONTH({Order Date}),DAY({Order Date})-14)),10),6),RIGHT(LEFT(CONCATENATE(DATE(YEAR({Order Date}),MONTH({Order Date})-1,DAY({Order Date})+16)),10),6))

In the end, this column is not recognised as a date and there is no option to set it as a “Due Date” and therefore we cannot automatically drive tasks from this field.

Another method I thought that might be effective, or a work around would be to set a date using an automation like the following existing one;
“WHEN AN ITEM IS CREATED, SET DUE DATE TO CREATION DATE PLUS SOME DAYS
However this automation is limited to working off a creation date + some days and isn’t suitable when you’re trying to create timelines off a start date that cannot change.

ideally, the automation would be something more like…

“WHEN AN ITEM IS CREATED, SET DUE DATE TO WHEN DUE DATE

This would look something like…

WHEN AN ITEM IS CREATED, SET ORDER DATE TO 7 DAYS BEFORE START DATE

Any feature updates or ideas on how we could get this to work for us would be much appreciated!