Between times Filter not working

[Nix23]

I am trying to only show items  within today  if the start time and end time is today since this is being displayed on Sharepoint. 

the gallery does not change no matter if i use Now as well which i think should work. 

Sort(Filter('PTO Calendar', 'Start Time' <=Today() && 'End Time' >=Today()), SortOrder.Descending)

[iAm_ManCat · Accepted solution]

In these kinds of cases I would use label debugging as a start. 

Create labels in your gallery that are each part of your formula, ie one for IsToday('Start Time') etc. although its pretty clear in this case that the issue is delegation - as shown by the blue lines:

Also, just wanted to double-check, you only want items that BOTH start and end today? Or do you want items their EITHER Start Today or End Today? I would think the latter, in which case your formula needs to use the OR function || instead of &&.

...and If I had to think about it, I'd say you probably wanted only items where Today falls within the range of Start Time and End Time yeah?

I would also not use the IsToday function - this is not a delegable function with Dates - Date fields are delegable to some degree now, but not with Time unless the item's time is exactly the same as only the = function currently works.

Here's how I would solve this:

Create a new number column for each date that uses the format yyyymmddhhmm - that way you can compare them as number fields using either < or > or =

So I created a new SharePoint list, created two date fields with times, then created two new columns (one for each date, and had them set as above, then altered the formula to find any entries where Now() exists between these two dates:
(EDIT: used Now() instead of Today() and made the range include all items in the 24h period of Today)

Confirmed this only showed items that had a range that included today by adding entries with dates after today and at midnight tonight:

Here's the formula, you may want to choose another column to SortByColumns:

SortByColumns(
    Filter(DateDelegationTest,

           // Start time is before end of today

           StartTimeNumber <= Value(Text(Now(), "[$-en-GB]yyyymmdd" & "2359"))
           &&

           // End Time is either after today or Is Today

           EndTimeNumber >= Value(Text(Now(), "[$-en-GB]yyyymmdd" & "0000"))
    ),
    "ID", Descending
)

---

Then when creating any new items, we'd have it fill the number value of Start Time or End Time into the field automatically:

Value(Text(StartTimeControlName.Text, "[$-en-GB]yyyymmddhhmm"))

Let me know if that works out for you and if there's any other limitations or specifics you want around the dates,

Cheers!

Sancho

[Anonymous]

@Nix23 

Today() is a fixed time, not a range as far as i know.

Im 100% certain there's a shorter way to do this but....

Sort(Filter('PTO Calendar', Date(Year('Start Time'),Month('Start Time'),Day('Start Time')) = Today() && DateDiff('End Time', Date(Year(Now()),Month(Now()),Day(Now())+1)), SortOrder.Descending)

It basically checks if it started today and ends before tomorrow

[iAm_ManCat]

@Anonymous  Today is the fixed time yes, but start and end time then define a range

Unfortunately, the formula you gave also suffers from the issue of delegation as DateTime are not delegable with Filter so it will stop working once there are more than 500 (or 2000 if maxed) records:

[Anonymous]

@Nix23 @iAm_ManCat 

This should work then:

SortByColumns(Filter('PTO Calendar', Day(DateDiff(Today(), 'Start Time')) > -1 && Day(DateDiff('End Time', Today())) > 0,"ID", SortOrder.Descending))

It covers the logical error @iAm_ManCat accidentally had.

[iAm_ManCat]

Hey @Anonymous 

Apart from using Now() instead of Today() for my current value, I'm not really seeing what logical error I had. Could you explain please? Will update my post above.

Your formula is still not delegable though, and this one actually returns no results:

Thanks!

Sancho

[Anonymous]

@iAm_ManCat 

Since Today() gives the first second of today it fails to allow starttime to be 11am today for example since its not = to the first second and it's not before today either. Also why are you getting the value in date format of today()? Seems unnecessary since dates can be directly compared. Or are you storing it as a number?

[Anonymous]

@iAm_ManCat 

it was meant to be

SortByColumns(Filter('PTO Calendar', Day(DateDiff(Today(), 'Start Time')) < 1 && Day(DateDiff('End Time', Today())) > 0,"ID", SortOrder.Descending))

[iAm_ManCat]

Hi,

 @Anonymous I'm not sure you're understanding the issue with delegation - @Nix23 is using a SharePoint list, and datetime is not a delegable item when using Filter.

Please could you check here regarding date delegation:

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

As while these can be compared directly, they are not delegable, which is why I suggested to create number values of them in order to be a delegable query.

I have updated my original post above with the modified formula and screenshots,

Cheers,

Sancho

[Anonymous]

@iAm_ManCat 

I was solving it based on the original question instead of causing the hassle of changing sharepoint datatypes cause that has been an annoyance for me personally.

You can solve delegation issues with dates by setting a variable to be equal to the sharepoint and that allows filters. At least that worked in the past 🤔

[Anonymous]

@iAm_ManCat 

Yep, just double checked, it works completely fine if u set a variable to be the dataset 👌

That way, you dont need the added stress of converting between types constantly or changing datatype in sharepoint