Calculate working hours based on schedule

[chme02]

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?

[sunny_talwar]

Would you be able to share expected output based on the sample you have provided?

[chme02]

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

[chme02]

I got the format problem solved by using

time([TIMMAR_V]/24) as TIMMAR_V

but the rest is still a problem.

[bryanalmeida525]

where you ever able to solve this ?