topic Creating a Date Range? in Data & Schema Discussions

Creating a Date Range?

jfinn57 — Wed, 06 Apr 2022 19:19:56 GMT

Is it possible to create a date range field from a list of expiration dates? Say there's contracts for Customer A that expire in June 2022, January 2023, and December 2024. Is there a formula that could take the min and max date to show a range of June 2022 - December 2024?

Re: Creating a Date Range?

RADSr — Wed, 06 Apr 2022 20:47:49 GMT

I don't see why not

What do you want to use the feature for? Just text displaying a range? Or a filter - in which case two aggregate filters might more easily fit the bill <field> < max(date_field) and <field> > min(date_field)

If it is the display you'll probably have a fairly complex formula as you'll end up converting date or datetime to text and concatenating strings together.

Re: Creating a Date Range?

jfinn57 — Thu, 07 Apr 2022 21:52:53 GMT

Thank you for the response! I was hoping to get it to show as a display. It's part of a report I'm trying to recreate from excel, in the excel spreadsheet I do a MIN(IF()) to pull the first expiration date then a MAX(IF()) to grab the second, then just concatenate them.

I get an error that "min currently not supported in this context. It is currently supported only in insight formulas." when I try to grab the first date using min() in the formula builder so I was hoping there might be a workaround

Re: Creating a Date Range?

JoeM — Thu, 07 Apr 2022 23:03:10 GMT

@jfinn57 - Are the expirations in different columns or is each contract it's own row. If the latter:

formatdate( min( Community.Problem875.Expiration ), 'MMMM-YYYY' ) + ' - ' + formatdate( max( Community.Problem875.Expiration ), 'MMMM-YYYY' )

Re: Creating a Date Range?

RADSr — Fri, 08 Apr 2022 00:09:20 GMT

I saw that error as well - you'll see it if you try to use the formula as a grouping element in an aggregation table. I was able to work around it by using the new query over result set feature.

Basically you can't group on an element which in turn requires grouping ( i.e. no grouping on a summary calc ) in one pass.