Creating a Date Range?

[jfinn57]

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?

[RADSr]

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.   

[jfinn57]

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

[JoeM]

@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'
)

[RADSr]

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.