Skip to main content

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. 

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! 

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 🙂

 

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 🙂

 


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! 

Reply


Terms & Conditions