Help

Extract the first date from a string of dates

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
1644 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Emily_McLain
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a multipart task that I think can surely be accomplished with a few formulas. I looked around the forums for a while and I think I have an idea but I haven't been able to put it all together yet! 

Basically I have lookup field collating all the dates that a certain client has ads scheduled across multiple products. For our scheduling purposes, the first of each month have a "- [Month]" text added. I want to identify the first date among this group of dates, for a notification automation to let the team know when this client's contract starts. 

Using a Formula or Rollup field I can transfer that into a string separated by commas. 

Screen Shot 2023-10-26 at 1.00.11 PM.png

From here, my general understanding is I need to:

> Strip out the text so its just dates (I tried REGEX_REPLACE but it also removed everything after the first instance of text. These type of coding functions are really over my head)

> Format the dates into YYYYMMDD numbers that can then have the MIN function applied to find the earliest one (also can't figure out how to accomplish this within a string of dates)

> Additional wishlist is to identify the first date after today. There will only be a single year of dates in this field, but the client may sign a new contract later in the year, and those dates will add to this same field, so it would be useful to know the next earliest date. 

 

Grateful for any advice! Thank you! 

1 Solution

Accepted Solutions
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi @Emily_McLain ! When dates are listed on the Sales table, is the field format single line text or a date field? If possible I'd recommend using the date field type, it saves you having to convert strings to dates later and makes everything easier. 

As long as your field is in date format, you can use a rollup field with min(values) to get the earliest date. 

To show only dates after today, then add a condition to the rollup, to only show dates after today's date (Where DATE is after TODAY). Screenshots attached 🙂

Screenshot 2023-10-27 223304.pngScreenshot 2023-10-27 223331.png

 

See Solution in Thread

2 Replies 2
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi @Emily_McLain ! When dates are listed on the Sales table, is the field format single line text or a date field? If possible I'd recommend using the date field type, it saves you having to convert strings to dates later and makes everything easier. 

As long as your field is in date format, you can use a rollup field with min(values) to get the earliest date. 

To show only dates after today, then add a condition to the rollup, to only show dates after today's date (Where DATE is after TODAY). Screenshots attached 🙂

Screenshot 2023-10-27 223304.pngScreenshot 2023-10-27 223331.png

 

Emily_McLain
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Arthur,

Thanks for the reply! Our system is set up where the initial dates are records on a separate Calendar table so thats where I was getting the string of dates from, but they do eventually get translated into date field entries on another table. I took your suggestion and reset up my Rollup fields to pull from those date fields (had to have two working across two different tables so it collated everything how I wanted) instead and its working beautifully!