Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I´m looking for a way to calculate time spent on a case but I only want to calculate working hours.
I got two tables.
Table1(Sheet1) - Case data with start and stop time as timestamp.
Table2(Sheet2) – Is the schedule. It only has the date and the amount of expected working hours. Holidays are missing or marked as 0 hour. To make it a bit more complicated it's in the format that 8,5 is 8 hours and 30 min.
They start every day at 08:00 and have 1 hour lunch.
Anyone got an idea how to solve this or at least where to start?
Would you be able to share expected output based on the sample you have provided?
Someting like this. It's the last case that have to use the schedule to get the right workinghours.
Identifier | CaseStartDate | CaseStopDate | Workhours |
2015KC126224 | 9/28/2015 2:51:30 PM | 9/28/2015 2:54:43 PM | 00:03 |
2015KC126864 | 9/30/2015 9:26:52 AM | 9/30/2015 9:28:42 AM | 00:02 |
2015KC127666 | 10/2/2015 8:16:34 AM | 10/2/2015 8:36:48 AM | 00:15 |
2016KC42304 | 4/20/2016 4:51:25 PM | 4/21/2016 9:18:55 AM | 01:28 |
I got the format problem solved by using
time([TIMMAR_V]/24) as TIMMAR_V
but the rest is still a problem.
where you ever able to solve this ?