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
Hi @Pete_Hartnett ,
I tried to import your custom Widget into my instance but got this error message :
Do you know what could be the issue ?
Thanks,
Paul
My be easier to try and solve this using a Postgres SQL Connector. You can see an example of the postgres function here:
And here is an example of someone using the default postgres connector to access postgres functions from Tulip:
For posterity, here is how I did this with the CW:
HTML
<button id='btn'>Calclulate</button>
JavaScript:
$('#btn').click(function() {calculateDelta();})
function getDate(dt_obj){
//Convert a Tulip Datetime to a JS date object
let d = new Date(0); // The 0 there is the key, which sets the date to the epoch
d.setUTCSeconds(dt_obj['timestamp']/1000);
return d
}
function getShiftStart(start){
//Get the datetime of the shift start, on the date of the starttime.
//So if starttime is 1/1/2022 at 13:00 and shift start time is 8
// this would return 1/1/2022 at 8:00
const startOfDay = new Date(start);
startOfDay.setHours(getValue("Shift Start Time")||0);
return startOfDay
}
function getShiftEnd(end){
//Get the datetime of the shift end, on the date of the endtime.
//So if endtime is 1/1/2022 at 13:00 and shift end time is 16
// this would return 1/1/2022 at 16:00
const startOfDay = new Date(end);
startOfDay.setHours(getValue("Shift End Time")||24);
return startOfDay
}
function getBusinessDatesCount(startDate, endDate) {
//Returns the number of business days between 2 dates
console.log(startDate.toLocaleString(),endDate.toLocaleString());
let count = 0;
let first = true;
const curDate = new Date(startDate.getTime());
while (curDate <= endDate) {
const dayOfWeek = curDate.getDay();
if(dayOfWeek !== 0 && dayOfWeek !== 6){
count++;
}
curDate.setDate(curDate.getDate() + 1);
}
//alert(count);
return count;
}
function getDaysCount(start,end){
//Returns all the days between 2 dates.
let Difference_In_Time = end.getTime() - start.getTime();
let Difference_In_Days = Difference_In_Time / (1000 * 3600 * 24);
return Math.ceil(Difference_In_Days)
}
function getTruncDateTime(date){
//Returns the time being passed in, but truncated to 00:00 on that day
let startOfDay = new Date(date);
startOfDay.setHours(0);
//console.log("Before return test", startOfDay.toLocaleString());
return startOfDay
}
function betterDeltaEnd(measure,shiftEnd,shiftLengh){
let shiftStart = getShiftStart(shiftEnd);
if (measure.getTime()<shiftStart.getTime()){
console.log("in 1")
return shiftLengh
}
else if (measure.getTime()>shiftEnd.getTime()){
console.log("in 2")
return 0
}
else{
console.log("in 3")
let diff = shiftEnd.getTime() - measure.getTime();
diff = diff/1000/3600
diff = Math.floor(diff)
return (diff)
}
}
function betterDeltaStart(measure,shiftStart,shiftLengh){
let shiftEnd = getShiftEnd(shiftStart);
//console.log(shiftEnd.toLocaleString());
if (measure.getTime()>shiftEnd.getTime()){
//console.log("in 1")
return shiftLengh
}
else if (measure.getTime()<shiftStart.getTime()){
//console.log("in 2")
return 0
}
else{
let diff =measure.getTime()-shiftStart.getTime();
//console.log("in 3")
diff = diff/1000/3600
diff = Math.floor(diff)
return (diff)
}
}
function calculateDelta(){
let ST = getDate(getValue("Start Time"));
let ET = getDate(getValue("End Time"));
let trunc_ST = getTruncDateTime(ST)
let trunc_ET = getTruncDateTime(ET)
let shift_start = getShiftStart(ST);
let shift_end = getShiftEnd(ET);
let hours_in_day = getValue("Shift End Time")- getValue("Shift Start Time")
// if it is currently after the start time, return by how much
let startAlreadyElapsed = betterDeltaStart(ST,shift_start,hours_in_day);
//if it is before the end time, return that time
let endNotElapsed = betterDeltaEnd(ET,shift_end,hours_in_day);
//console.log(endNotElapsed);
let allDays = getDaysCount(ST,ET);
let businessDays = getBusinessDatesCount(ST,ET);
let days = null
let final_hours = null;
if (getValue("Exclude Weekends")){
days =businessDays;
}
else{
days = allDays;
}
let fullDayHours = days * hours_in_day;
let finalVal =fullDayHours - startAlreadyElapsed;
finalVal -= endNotElapsed;
console.log(allDays,businessDays,fullDayHours,startAlreadyElapsed,endNotElapsed,finalVal);
fireEvent("Hours",finalVal);
}
CSS:
body {
background-color: transparent;
font-family: Sans-Serif;
font-size: 40px;
padding: 10px;
}
button {
background-color: #3A89EB;
border-radius: 4px;
border-width: 0;
color: #fff;
cursor: pointer;
font-size: 24px;
padding: 16px;
}
button:active {
background-color: rgb(3, 106, 214);
}
Pete
1 Like