Using a formula to populate EDITABLE date fields?

[Steve-O]

I run a table that keeps track of content my team creates for a digital billboard. When we hear about new content, our project manager builds out a production schedule based on the date the content is supposed to go live on screen.

To help my PM with scheduling, I used formulas to populate some milestone columns (initial concepts due, kickoff to production, multiple creative reviews, etc...) with some estimated days for each step in the process. For example: Project Kickoff should occur about a month from the start date and uses "WORKDAY({Start Date}, -33)" while the File Handoff date is only a week before launch and uses "WORKDAY({Start Date}, -5)"

So far, this has worked great for providing some rough dates during the initial project planning stages, but we run into problems with our calendar when real world events force dates to change. For example: If the PM knows that a certain executive will be out of office on the formula-generated milestone date for a creative review, she can't edit the calculated field to reflect changes to the schedule. We don't want to edit the formula field and effect all the other rows. We also don't want each column's date to rely on the date from the previous column. Because launch dates are pretty firm, we may make decisions to move one milestone while still needing to meet the next firm milestone date.

So, I'm hoping there is there a way to use the formulas to generate the dates once and push them to normal (non-formula) date columns so that they'd become editable after the initial creation?

Thanks for any ideas you can share!

Steve

[joshsorenson]

What you want to do is have the formula calculate the date, but copy it to a normal date field. Then you can edit the normal date field.

[corb1]

Two possible solutions. Consider looking into record templates & relative date function. This can be used to spawn records in various linked tables and date fields can be relative in the template based on either the current date or other dates present in the record. In a base design I've done we are specifically using it in the use case you describe (PM, deliverable planning).

Another solution I've used is to have 3 fields - an automated formula date, an "override" date field, and the usable actual date field (another formula). The usable date field formula is used to check if there is an override date to use and if so, then it uses that. If not, then it uses the system-generated date.

The formula for the usable date field is roughly:

If (override is not empty, use override, otherwise use default).

[Steve-O]

Would copying the date into a new field be a manual or automated step? I've been looking for formula actions that would allow me to copy/paste into another field but haven't found one yet. I thought maybe I could add a check box and build an automation that would move dates but I can't use calculated fields. 

I'm guessing the solution to this is simpler than I imagine, but I'm not looking in the right place.

THANKS!

[corb1]

Sorry - I'm not quite sure what you mean by that last question.

I re-read your initial post and I think I understand more of what you're saying. Definitely check out record templates as they do essentially what you're looking for with the relative date feature built-in, and that will prefill data into editable date-based fields automatically.

If you find you still want to explore manually, the other solution should still work, but if you want it all automated, you'd need to add an automation that is basically....

-Trigger - when record is updated

-Condition - when dateformula field is not empty

-Action - set date value in editable date field to be equal to value of dateformula field

[Steve-O]

Hiya corb1,

Your answer got me most of the way there. I decided to go with the override field and simplified the original field formula to check for an override field but otherwise use its original WORKDAY calculation.

IF({PM Connect Override}, {PM Connect Override}, WORKDAY({Start Date}, -30))

Now, if the override field is empty, it goes with the original calculation but weirdly, if I put a date in the override date field the formula generates a result that's one day before the override field entry. Any ideas why this would be happening?

[corb1 · Accepted solution]

@Steve-O check your timezone settings for the base & for that field. I have found that time can be finicky and if you don't specify your time zone, some date fields may appear to spill over to the adjacent day because Airtable defaults to GMT.

[Steve-O]

That worked! Thanks!