If I have two dates can I get the working hours between them through the Expression editor (considering that I know that the working hours are 8-4 pm on weekdays?
I.e. Date1: Sat 22-Sep-2022 9:00 PM and Date2: Mon 24-Sep-2022 10:00 AM → Result should be 2h
Hey @DariusM -
This is super tricky to achieve with normal trigger logic for a bunch of specific edge cases, its possible (I think, but it would be super clunky) to handle cases like if the end date is in a different year than the start date (and a whole bunch more).
Because of this I went down the custom widget road to achieve this functionality. Even in Javascript, supporting all the potential edgecases correctly was a bit of a challenge (and there may be additional cases that I missed, but its close).
Setup:
Check out this doc on how to import a custom widget:
This widget has 5 inputs. Start time, End time, Exclude weekends, shift start time and shift end time. in your case I would set these as follows:
- Start time - Whatever variable you are using to track the starting point of the range you want to measure
- End time - Same deal as start time, just the other end of the range
- Exclude weekends = True
- Shift start time = 8
- Shift end time = 16
There may be edge cases I didn’t catch in my testing, feel free to iterate on this guy if you find any!
customWidget-Get Hours Between Dates (1).json (6.7 KB)
Note: This widget didn’t go through our standard code review process. Use it with caution
Pete
1 Like