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:
Let me know if that works out for you and if there's any other limitations or specifics you want around the dates,
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
@kamikaze4416 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:
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:
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?
it was meant to be
SortByColumns(Filter('PTO Calendar', Day(DateDiff(Today(), 'Start Time')) < 1 && Day(DateDiff('End Time', Today())) > 0,"ID", SortOrder.Descending))
Please could you check here regarding date delegation:
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,
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 🤔
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
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Fill out a quick form to claim your user group badge now!
Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities
Features releasing from October 2019 through March 2020
Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications