# Calculated Field for Working Hours Between

**URL:** <https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104>\
**Category:** Q&A\
**Tags:** formulas, calculations, quick-sight\
**Created:** [April 19, 2022, 3:34pm UTC](https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104 "2022-04-19T15:34:33Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![kirstyvasp](https://avatars.discourse-cdn.com/v4/letter/k/a6a055/32.png) [@kirstyvasp](https://community.amazonquick.com/u/kirstyvasp)\
**Post date:** [April 19, 2022, 3:34pm UTC](https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104/1 "2022-04-19T15:34:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JoseB-aws](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/joseb-aws/32/284_2.png) [@JoseB-aws](https://community.amazonquick.com/u/JoseB-aws)\
**Post date:** [April 19, 2022, 4:39pm UTC](https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104/2 "2022-04-19T16:39:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Daniel15](https://avatars.discourse-cdn.com/v4/letter/d/ecccb3/32.png) [@Daniel15](https://community.amazonquick.com/u/Daniel15)\
**Post date:** [November 11, 2023, 1:54pm UTC](https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104/3 "2023-11-11T13:54:49Z")

</div>

@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)

---

<div class="post-metadata">

**Author:** ![ErikG](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/erikg/32/14524_2.png) [@ErikG](https://community.amazonquick.com/u/ErikG)\
**Post date:** [November 14, 2023, 6:39am UTC](https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104/4 "2023-11-14T06:39:21Z")

</div>

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

> [@Calculating Time within Date Range](https://community.amazonquick.com/t/calculating-time-within-date-range/21045/3):
>
> You can create 2 parameters. Date Fr, Date To to allow user to input parameter values 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(da…

BR

---

<div class="post-metadata">

**Author:** ![Ted](https://avatars.discourse-cdn.com/v4/letter/t/db5fbb/32.png) [@Ted](https://community.amazonquick.com/u/Ted)\
**Post date:** [November 17, 2023, 3:32pm UTC](https://community.amazonquick.com/t/calculated-field-for-working-hours-between/3104/5 "2023-11-17T15:32:33Z")

</div>

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

```auto
((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

```