cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted

Hi @Bk3433 ,

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
Highlighted
Super User II
Super User II

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.

Highlighted
Community Support
Community Support

Hi @Bk3433 ,

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.
Highlighted

@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

 

 

Highlighted

Hi @Bk3433 ,

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,752)