Gallery filtered by Datediff in if statement

(0) Share

Report

Posted on by jbrit2020

632

Hello,

I'm trying to filter a gallery based on the datediff within an if statement to establish a threshold of DateDiff numbers such as <=1,<=14, >14

Search(
 Filter('Risk Mitigations',
 Status = Statuses &&
 Department.Value = Viewgr.Value && DateDiff(Today(),'Target Closure Date') <= 14,)
 
 
 ,
 TextSearchBox1_11.Text,"MitigationDescription","Title")

Really stuck on this, any help appreciated.

Categories:

General Questions

All responses (7)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Re: Gallery filtered by Datediff in if statement

@jbrit2020

I think this is a bit more elegant:

With({fltrd:Filter('Risk Mitigations',Status = Statuses, Department.Value = Viewgr.Value), dd: DateDiff(Today(),'Target Closure Date') }, Search( Switch(Ragsy, "Green",Filter(fltrd, dd > 14), "Amber", Filter(fltrd, dd > 1), "someothercolor"= Filter(fltrd,dd < 15), //looks like you left one out Filter(fltrd,dd < 2 ), TextSearchBox1_11.Text,"MitigationDescription","Title" ) )
Akser 1,546 Super User 2025 Season 1 on at

Like (0)

Report
Re: Gallery filtered by Datediff in if statement

Maybe a small change can be made to make it easier to read?
If(Ragsy = "Green", DateDiff(Today(),'Target Closure Date') > 14,If(Ragsy = "Amber", DateDiff(Today(),'Target Closure Date') >1 &&DateDiff(Today(),'Target Closure Date') <15,DateDiff(Today(),'Target Closure Date') <2)) //Becomes Switch ( Ragsy, "Green", DateDiff(Today(),'Target Closure Date') > 14, "Amber", DateDiff(Today(),'Target Closure Date') >1 &&DateDiff(Today(),'Target Closure Date') <15, DateDiff(Today(),'Target Closure Date') <2 )
jbrit2020 632 on at

Like (0)

Report
Re: Gallery filtered by Datediff in if statement

It's sorted, I was being an idiot. Do you reckon there's a more elegant way to go about it though?

Search( Filter('Risk Mitigations', Status = Statuses && Department.Value = Viewgr.Value && If(Ragsy = "Green", DateDiff(Today(),'Target Closure Date') > 14,If(Ragsy = "Amber", DateDiff(Today(),'Target Closure Date') >1 &&DateDiff(Today(),'Target Closure Date') <15,DateDiff(Today(),'Target Closure Date') <2))) , TextSearchBox1_11.Text,"MitigationDescription","Title")
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Re: Gallery filtered by Datediff in if statement

@jbrit2020

You mention an If() function but don't show one in the formula you posted. Is there more to it? Could you post a screenshot showing the full formula and error message.
Akser 1,546 Super User 2025 Season 1 on at

Like (0)

Report
Re: Gallery filtered by Datediff in if statement

Not sure if the error is related to your DateDiff formula as it looks OK. The Department check looks good too (I guess choice field in SharePoint?). Can you help me understand the type of Status and Statuses?
Filter('Risk Mitigations', Status = Statuses
jbrit2020 632 on at

Like (0)

Report

Re: Gallery filtered by Datediff in if statement

Yes comma is a typo. Doesn't work regardless of the comma.
Akser 1,546 Super User 2025 Season 1 on at

Like (0)

Report
Re: Gallery filtered by Datediff in if statement

Hi @jbrit2020,

Is there an extra comma there?
Search( Filter('Risk Mitigations', Status = Statuses && Department.Value = Viewgr.Value && DateDiff(Today(),'Target Closure Date') <= 14,) //The comma after 14 must be a typo? , TextSearchBox1_11.Text,"MitigationDescription","Title")