Sorting Gallery by Date, but only showing entries in future

[CG83]

Hi all,

I have a gallery whereby one of the labels contains a date. What I am trying to do is make the gallery sort the entries by date (ascending), but only show the entries where the date is either today or in the future. My column in excel is called 'Final Date'

Currently, I am using the formula below:

SortByColumns(Filter(Events,'Final Date'>=Today()),"Final Date",SortOrder.Ascending)

This is showing an error saying 'Exected value " to be a valid RFC 3339 'date-time' format. Allowed ISO 8601 format(s): "YYYY-MM-DDThh:mm:ssZ', 'YYYY-MM-DDThh:mm:ss+hh:mm', YYYY-MM-DDThh:mm:ss'.)

The gallery isnt returning any results and I am a bit confused how to fix that formula. Can anybody assist?

[WarrenBelz]

Hi @CG83 ,

I assume here that you have your date stored as Text.

With(
   {_Date: Text(Today())},
   SortByColumns(
      Filter(
         Events
       'Final Date' = _Date,
       "_Date",
       SortOrder.Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[CG83]

Sorry. My date column in excel is stored as a date, not text. I have pasted your formula in and it throws up a lot of errors. Is this because it is stored as a date? 
apologies, I’m fairly new so not sure exactly how to identify the error

[WarrenBelz]

@CG83 ,

This gets more complicated - excel is a terrible data source and this is one of the many issues with data format.

SortByColumns(
   Filter(
      AddColumns(
         Events,
         FinalDate,
         DateAdd(
            Date(1900,1,1),
            'Final Date' -2,
            TimeUnit.Days
         )
      ),
      FinalDate = Today()
   ),
   FinalDate,
   SortOrder.Ascending
)

Test the - 2 part - I think that is correct. Also why are you bothing sorting on the column when all the values will be the same ?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[CG83]

Apologies. 

My label containing the date within the gallery has the formula below in the Text property.

Text(ThisItem.FinalDate,DateTimeFormat.LongDate)

When you asked if it was in text format I thought you meant in excel. Apologies. 

I am sorting as I want the entries to be in order of date, but hiding any entries where the date is in the past

[WarrenBelz]

@CG83 ,

I really need to know what format you date field is. Does that label show a valid date in the gallery display ?

[Meekou]

Excel date is stored as sequential serial numbers, for filter by excel date, you may try to convert the excel sequential serial numbers to date or convert powerapps today to sequential serial numbers.

Here is a sample code:

ClearCollect(
    A1,
    SortByColumns(
        Filter(
            Row_10,
            Value('Final Date') >= DateDiff(
                DateValue("1900-1-1"),
                Today()
            ) + 1 // this may be different due to timezone, check the filter result for + 1 or + 2
        ),
        "Final Date",
        SortOrder.Ascending
    )
)

---

Best Regards,

Meekou

YouTube

PowerApps

PowerAutomate

[WarrenBelz]

@Meekou ,

That is what I posted earlier and does not seem to be the correct format. You can also store dates in Excel formatted as Text, which is my current guess if DateValue() works in it.

[CG83]

The date bit is very confusing for me so I have added another column into my excel sheet to deal with this. Now all I need to do is sort my gallery into date order (earliest at the top) and remove them from the gallery if the new column (EventFinished) contains "Yes". Does this make it more straightforward in power apps?

[WarrenBelz]

@CG83 ,

I just need to know whether you have formatted your Excel column as a Date or Text - one of the two options I posted should work depending on what this is. The rest will not work unless you can turn it into a Date.