Date Formulas

[Siobhan_Mc_Kenn]

Hi there,

Wondering if anyone could help me with a couple of formulas.

I have an existing date field (DD-MMM-YY) and I want to create deadlines from this field as follows:

1.  23rd day of the 9th month after this date; example - where source field is 31-12-2022, formula should result in 23-09-2023 and for 28-02-2023, formula should result in 23-11-2023.

2.  23rd day of the previous month; example - where source field is 31-12-2022, formula should result in 23-11-2022 and for 28-02-2023, formula should result in 23-01-2023.

3.  23rd day of the month that is 6 months earlier; example - where source field is 31-12-2022, formula should result in 23-06-2022 and for 28-02-2023, formula should result in 23-08-2022.

For automations I would like the resulting dates to be in local format and up to now have been creating a secondary field using the DATETIME_FORMAT formula but wonder if there is any way to incorporate the DATETIME_FORMAT formula into the original DATEADD formula to limit the number of extra columns.

I hope this makes sense and many thanks in advance to anyone willing to assist.

Siobhan

[o_elsiefy]

I designed and built the airtable for my media company and we have a lot of similar formulas and automations that set deadlines based on a number of conditions. For example, breaking news is has a deadline of the same day, but if its a standard news buzz, the day before. If its a long feature, the design deadline is set to 3 days before and so on. This is done fully using formulas but with a number of conditions given the different scenarios that may arise.

On the agency side of our company, however, deadlines work using a script which looks at the number of things assigned to a designer and the posting date, the designer gets x number of tasks per day in order of the posting date. The automation triggers whenever a posting date is changed or a designer gets assigned meaning the tasks and deadlines are constantly updating to reflect whats most urgent.

[jsep]

Can you share the current formulas that you are using? 

[TheTimeSavingCo]

Thank you so much for the examples and the expected output, it makes helping so easy!!

I've put something together here for you that should do what you need, and you can duplicate it to your own workspace to get the formulas

I assumed that the format you wanted it in was the one you had in your post, but in hindsight I'm not too sure.  Let me know if it's something else and I'll see what I can do!

And sorry, you probably already know about this, but I take it none of the date format presets meet your requirements?

 

[Siobhan_Mc_Kenn]

Adam,

Thanks for much for this.

Quick and stupid question but how do I duplicate what you have provided into my own workspace?

Kind Regards,

Siobhan

[TheTimeSavingCo]

Hi Siobhan!  Check out Airtable's guide on how to duplicate here: https://support.airtable.com/docs/creating-a-new-empty-base#duplicating-airtable-bases

[Siobhan_Mc_Kenn]

Thanks again for this Adam. It seems that the resulting formats are not recognised as dates and so sorting by date order in the fields generating by the formulae is not possible.

Any way around this?

[TheTimeSavingCo]

I'm afraid not, once we use DATETIME_FORMAT to convert them into "DD-MM-YYYY" they become text.  If we want to be able to sort them by date order we're going to need to remove the DATETIME_FORMAT bit and use one of the default date display options: