cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering items in a data table based on date and time

So, I have a column in a data table called Start Date. I am trying to filter this column for specific date and time ranges. This is how I tried to do it: Capture.JPG

Now, this works fine if the date is the same in both date pickers. If I were to select a range between two different dates, the data is not shown: 

 

Capture.JPG

Is there a different way I can filter the table based on date/time range? 

Thanks 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous ,

Could you show me some samples of 'Start Date'?
I assume that 'Start Date' format looks like this: 8/13/2020 7:30

So you also need to use DateTimeValue() function to transfer text value to datetime value.

Try this formula:

Filter(ZPRSTable,
DateTimeValue('Start Date')>=DateTimeValue(DatePicker2.SelectedDate&" "&Text(Dropdown1.Selected.Value)),
DateTimeValue('Start Date')<=DateTimeValue(DatePicker2_1.SelectedDate&" "&Text(Dropdown1_1.Selected.Value))
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
GarethPrisk
Resident Rockstar
Resident Rockstar

To break this down.

 

tblDate []
xDate 8/13/2020
yDate 7:00 AM
xTime 8/13/2020
yTime 7:00 PM

 

Filter(
Table,
DateValue(tblDate) >= xDate,
DateValue(tblDate) <= yDate,
TimeValue(tblDate) >= xTime,
TimeValue(tblDate) <= yTime
)

 

From a formula perspective, this not logically correct. Basically, because you've separated the Date and the Time values, they're being checked independently. Meaning, you may have a time that occurs outside the range, but a date that does, and it's not recognizing. Instead, you need to create a true start and end DateTime (combined) value to check against.

 

Is your table a collection? If so, you can also do some table shaping there to Add Columns to quickly see the respective DateValue, and TimeValue outputs of the given row. See an example app attached, for some ways to accomplish this.

v-yutliu-msft
Community Support
Community Support

Hi @Anonymous ,

Do you want to filter datetime field within datetime range?

I notice that the filtered rule for date is:

DateValue('Start Date')>=DatePicker2.SelectedDate&&DateValue('Start Date')<=DatePicker2.SelectedDate

Why you compare two times with DatePicker2?
I think you enter wrong datepicker's name.

I assume that one datepicker name is DatePicker2 and the other's name is DatePicker2_1.

If so, you should compare like this:

DateValue('Start Date')>=DatePicker2.SelectedDate&&DateValue('Start Date')<=DatePicker2_1.SelectedDate

 

Could you tell me:
1)the data type of 'Start Date' field?

2)the formula in two drop down's Items?

I assume that 'Start Date' field is date time type.

If so, you should set like this:
drop down's Items:

[Time(1,0,0),Time(2,0,0),Time(3,0,0),Time(4,0,0),Time(5,0,0),Time(6,0,0),Time(7,0,0),Time(8,0,0),Time(9,0,0),Time(10,0,0),Time(11,0,0),Time(12,0,0),Time(13,0,0),Time(14,0,0),Time(15,0,0),Time(16,0,0),Time(17,0,0),Time(18,0,0),Time(19,0,0),Time(20,0,0),Time(21,0,0),Time(22,0,0),Time(23,0,0),Time(24,0,0)]

the data table 's Items:

Filter(ZPRSTable,
'Start Date'>=DateTimeValue(DatePicker2.SelectedDate&" "&Text(Dropdown1.Selected.Value)),
'Start Date'<=DateTimeValue(DatePicker2_1.SelectedDate&" "&Text(Dropdown1_1.Selected.Value))
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yutliu-msft 

Capture.JPG

So, above is the start date column.

This is the formula for the time picker dropdown. Capture.JPG

I realized my mistake on the date picker and did change it. It is still the same problem though where I can select times between the same date but it messes up when I select the time difference between two different dates.

Also, yes the intent is to filter the 'Start Date' within a datetime range.

Thanks,

Bhavya

 

 

Hi @Anonymous ,

Could you show me some samples of 'Start Date'?
I assume that 'Start Date' format looks like this: 8/13/2020 7:30

So you also need to use DateTimeValue() function to transfer text value to datetime value.

Try this formula:

Filter(ZPRSTable,
DateTimeValue('Start Date')>=DateTimeValue(DatePicker2.SelectedDate&" "&Text(Dropdown1.Selected.Value)),
DateTimeValue('Start Date')<=DateTimeValue(DatePicker2_1.SelectedDate&" "&Text(Dropdown1_1.Selected.Value))
)

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,304)