Calculated Field for Working Hours Between

Question & Answer
, ,
1

I’m looking to get a calculated field that will show the working hours between two date / timestamps. Currently I have:
dateDiff({date 1} , {date 2} , ‘MI’) / 60 -

dateDiff({date 1} , {date 2} , ‘DD’) * 16 -

dateDiff({date 1} , {date 2} , ‘WK’) * 16

Is anyone able to share anything that may be more effective? Working hours to measure are from 09:00 to 17:30.

2

Hello,
I would recommend to look at this post:
https://community.amazonquicksight.com/t/how-to-calculate-work-days/863
This will provide the working days, and you should just convert it to hours.
Br,
Jose

2 Likes
3

@JoseB-aws On that solution you can not get the exact working hours. How do I get working hours if you specify the hours ( from 9 till 18h for example)

4

Hi @Daniel15
but you can use the described logic do build your ifelse.
See also

BR

5

Hi this is how I solved the issue, taking into account working hours.
You need to set up two numerical parameters workingDayEnd & workingDayStart.

The logic being,
(Working days - 1,
Add the time from start of the end day
Subtract the time from the start date)
Multiply by 24 to give hours

((netWorkDays({start_datetime}, {end_datetime}) - 1) * ((${workingDayEnd}-${workingDayStart})/24)
+
ifelse(
  netWorkDays({end_datetime}, {end_datetime}) > 0,
  ifelse(
    (extract("HH", {end_datetime}) + extract("MI", {end_datetime})/60 + extract("SS", {end_datetime})/60/60) < ${workingDayEnd},
    ifelse(
      (extract("HH", {end_datetime}) + extract("MI", {end_datetime})/60 + extract("SS", {end_datetime})/60/60) > ${workingDayStart},
      (extract("HH", {end_datetime}) + extract("MI", {end_datetime})/60 + extract("SS", {end_datetime})/60/60),
      ${workingDayStart}
    ),
    ${workingDayEnd}
  ),
  ${workingDayEnd}
)/24
-
ifelse(
  netWorkDays({start_datetime}, {start_datetime}) > 0,
  ifelse(
    (extract("HH", {start_datetime}) + extract("MI", {start_datetime})/60 + extract("SS", {start_datetime})/60/60) > ${workingDayEnd},
    ${workingDayEnd},
    ifelse(
      (extract("HH", {start_datetime}) + extract("MI", {start_datetime})/60 + extract("SS", {start_datetime})/60/60) < ${workingDayStart},
      ${workingDayStart},
      (extract("HH", {start_datetime}) + extract("MI", {start_datetime})/60 + extract("SS", {start_datetime})/60/60)
    )
  ),
  0
)/24
)*24