I am calculating time difference between 2 time stamps in hours: min :sec format .
Now my problem is I have to eliminate weekends between 2 time stamps and calculate difference in hours: min :sec format
Discussion posts and replies are publicly visible
Hello likhithan
You can test out the calworkdays(). Here is a reference for documentation calworkdays(). Have you tried converting epoch values and calculate when it comes to seconds? It would return epoch seconds and you can convert into days.
I have tried calworkdays() but no use becasue when I have time stamp with in same day lets say [3/11/2024 9:24 AM EDT] and [3/11/2024 9:26 AM EDT] then here the difference is just 2 min right . But when I use this function, days will be coming as 1 but difference between these two dates we cannot expect days as 1
Then I would say epoch conversions and calculations will definetely help.
What is this epoch conversions could you please explain
Here is a reference where you can check the conversions. and create a custom expression rule to evaluate accordingly.Epoch Converter
a!localVariables( local!MaxDateTimeForAssigned: todatetime( rule!ERR_GetMaxDateTimeByStatus( statusId: cons!ERR_STATUS[3], trackingId: ri!trackingId ) ), local!MaxDateTimeForWaitingApproval: todatetime( rule!ERR_GetMaxDateTimeByStatus( statusId: cons!ERR_STATUS[5], trackingId: ri!trackingId ) ), local!CycleTime: if( or( a!isNullOrEmpty(local!MaxDateTimeForAssigned), a!isNullOrEmpty(local!MaxDateTimeForWaitingApproval) ), {}, local!MaxDateTimeForWaitingApproval - local!MaxDateTimeForAssigned, ), local!format: if( a!isNullOrEmpty(local!CycleTime), {}, split(local!CycleTime, ".")[1], ), local!days: if( a!isNullOrEmpty(local!format), {}, split(local!format, "::")[1], ), local!hours: if( a!isNullOrEmpty(local!format), {}, if( local!days = 0, split(local!format, ":")[3], sum( (local!days * 24), split(local!format, ":")[3] ) ) ), local!min: if( a!isNullOrEmpty(local!format), {}, split(local!format, ":")[4] ), local!sec: if( a!isNullOrEmpty(local!format), {}, split(local!format, ":")[5] ), local!TotalCycleTime: { if( and( a!isNullOrEmpty(local!hours), a!isNullOrEmpty(local!min), a!isNullOrEmpty(local!sec) ), {}, local!hours & ":" & local!min & ":" & local!sec ) }, local!TotalCycleTime )
This will not be coming in hours:min: sec format . i already have rule where I can get difference between time stamps but my only problem is to eliminate weekend and get in the hours: min: sec format as output
Yes. You will have to construct your expression rule to calculate the difference based on the below valuesAlso I would say try out these for better understanding
I can understand the above mentioned way is a complex one. But the conversion of your date times will give you accurate values.
Can you please check my code. I have sent . The way you are saying like how can I eliminate weekends there
i think then you need to calculate works hours initially
If I calculate initially then I will be getting 1 day with the dates I mentioned in the output. The the output cannot be 1 day because time difference in only 2 minutes
Konduru Chaitanya any suggestion here after seeing my code
a!localVariables( local!date1: ( "Your date time 1 here" - todatetime("01/01/1970") ) * 24 * 60 * 60, local!date2: ( "Your date time 2 here" - todatetime("01/01/1970") ) * 24 * 60 * 60, local!getEpochfor1: index(split(local!date1, ":"), 1, null), local!getEpochfor2: index(split(local!date2, ":"), 1, null), local!difference: local!getEpochfor2 - local!getEpochfor1, local!days: rounddown(local!difference / 86400, 0), local!hours: rounddown(mod(local!difference / 3600, 24), 0), local!minutes: rounddown(mod(local!difference / 60, 60), 0), local!sec: rounddown(mod(local!difference, 60), 0), concat( local!days, " Days", " ", local!hours, " Hours", " ", local!minutes, " Mins", " ", local!sec, " Secs" ) )
Try replacing your date time values in the local!date1 and local!date2. Not sure how accurate it works for all values. But as far as I am getting for different values, it is giving me the expected answer
Sure I will check once. Thanks. Did your code remove weekends here
a!localVariables( local!date1: datetime(2024, 4, 11, 13, 24, 41, 0), local!date2: datetime(2024, 4, 15, 13, 24, 41, 0), local!getEpochfor1: index(split(local!date1, ":"), 1, null), local!getEpochfor2: index(split(local!date2, ":"), 1, null), local!difference: local!getEpochfor2 - local!getEpochfor1, local!days: rounddown(local!difference / 86400, 0), local!hours: rounddown(mod(local!difference / 3600, 24), 0), local!minutes: rounddown(mod(local!difference / 60, 60), 0), local!sec: rounddown(mod(local!difference, 60), 0), concat( local!days, " Days", " ", local!hours, " Hours", " ", local!minutes, " Mins", " ", local!sec, " Secs" ) )
Here difference should be only 4 days but I am getting 15 hours and so on extra
You are not supposed to replace the complete value from the local!date1. I have mentioned the place where your date should be added.
a!localVariables( local!date1: tointeger( ( datetime(2024, 4, 11, 13, 24, 41, 0) - todatetime("01/01/1970") ) * 24 * 60 * 60 ), local!date2: tointeger( ( datetime(2024, 4, 15, 13, 24, 41, 0) - todatetime("01/01/1970") ) * 24 * 60 * 60 ), local!difference: local!date2 - local!date1, local!days: rounddown(local!difference / 86400, 0), local!hours: rounddown(mod(local!difference / 3600, 24), 0), local!minutes: rounddown(mod(local!difference / 60, 60), 0), local!sec: rounddown(mod(local!difference, 60), 0), concat( local!days, " Days", " ", local!hours, " Hours", " ", local!minutes, " Mins", " ", local!sec, " Secs" ) )