1 Reply Latest reply on Jul 1, 2016 3:30 AM by Carl Slifer

    Filter on date & time field based on a time range (eg. from 9.30am to 5.30pm)

    Jorge Blat Palacios



      I am struggling with this one. I would like to classify work carried out at a specific time as Standard Working Hours.


      I was thinking of doing an if statement but I am unsure as to how should I go about working out the correct time formulation and all the examples I see are based on standard filtering (range based). I need to have a simple "include overtime" yes/no option as the filter.


      I have done something similar to exclude weekends using this formula in a calculated field and using it as a filter.

      IF (DATEPART('weekday',[Field Date]) >1 and DATEPART('weekday',[Field Date]) <7) then 'No'

      else 'Yes'



      For the filter, I am using a slider giving me the choice to select "all, yes, no" as options and it works great.


      I just can't figure out how to do something similar with time ranges. A filter set as a slider giving me Overtime "all, yes, no" is the objective here.

        • 1. Re: Filter on date & time field based on a time range (eg. from 9.30am to 5.30pm)
          Carl Slifer

          Howdy Jorge,


          I've prepared a workbook to answer your dilemma.


          You need three things.

          1) A field that determines the status of the time - is it overtime, regular time, etc.

          2) A Parameter that lets the user select to see overtime, regular time, or all time

          3) A calculated field based on this parameter in (2) that uses field (1)


          Because the field in (1) splits the data into two parts (overtime and regular) and the parameter gives an option for selecting all the time but you want to view this aggregated you will need to use the additional caculution to be able to reference one, the other, or both parts of the field.



          Carl Slifer