Calculating Time within Date Range

Q&A
, , ,
1

Hi folks,

I’m looking to calculate for each row, the time within a date range that the user specifies via filters.

For example,

  1. User specifies a date range of Oct 29 to Nov 4 (one week)
  2. For each row, there is a start time and end time. Start and end times are timestamps and can be any time before, within, or after the date range
  3. For each row, I’d like to calculate how much time (from the row’s start and end time), in seconds, minutes, or hours, is within the user-specified date range.

Is this possible? I’d greatly appreciate any pointers here.

Thanks,
Joe

2

Hi @joeyu629
one option is create two parameter (pStart & pEnd).
Then you can use the parameter within a ifelse clause, like

ifelse( Start < pStart AND End > pEnd, dateDiff( Start,pEnd, "DD").....

BR

1 Like
3

You can create 2 parameters.
Date Fr, Date To to allow user to input parameter values

image
image550×534 27.5 KB

Then create a calculated field using the ifelse logic. Below I created an example, you need to extend a bit more to include all the ifelse logic to get the date range. it compares user input parameter(DateFr, DateTo) with dataset fields (Start Time, End Time)
ifelse(
{Start Time} < ${DateFr} AND {End Time} < ${DateTo},
ifelse({Start Time} > ${DateFr} AND {End Time} < ${DateTo},
‘0’,
toString(dateDiff(${DateFr}, {End Time}, “DD”))
),
toString(dateDiff({Start Time}, {End Time}, “DD”))
)

You will see a visual like below showing he date difference
image
t

1 Like
Calculated Field for Working Hours Between
4

Thank you @ErikG @royyung

Following the suggestions, I was able to string together an ifelse statement for a calculated field that does exactly what I need it to.

1 Like