Filtering items in a data table based on date and time

[Anonymous]

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: 

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: 

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

Thanks 

[GarethPrisk]

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]

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,

[Anonymous]

@v-yutliu-msft 

So, above is the start date column.

This is the formula for the time picker dropdown.

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

[v-yutliu-msft · Accepted solution]

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,