cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a Date Range?

jfinn57
Rocketeer

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?

4 REPLIES 4

RADSr
Captain
Captain

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.   

 

-- IncortaOne@PMsquare.com --

jfinn57
Rocketeer

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
Community Manager
Community Manager

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

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. 

-- IncortaOne@PMsquare.com --