I have this problem, here.
Now in a column A I have the End time and in column C in have the start time.
Am bit confused with the non working hours
From Sun to Thu it is 13:00:00 hrs -- Non working hours
On Friday it is 15:00:00 hrs -- Non working hours
On Saturday it is 12:00:00 hrs -- Non working hours
now here an employee can Swipe in on Monday and can swipe out on Thursday
1 Case
1) Its easy to get the total time (A-C).
2) But here need to exclude the non working hrs (Tue and Wed --(13+13))
now here an employee can Swipe in on Sunday and can swipe out on Friday
2 Case
1) Its easy to get the total time (A-C).
2) But here need to exclude the non working hrs (Monday to Thursday --(65:00:00))
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]INCIDENT CREATION_date[/TD]
[TD]INCIDENT CREATION_day[/TD]
[TD]INCIDENT Creat Start Time[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl66, width: 60"]END_date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl66, width: 56"]END_day[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]END_Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Actual Non Working Hrs Sun to Thu[/TD]
[TD]Actual Non Working Hrs Friday[/TD]
[TD]Actual Non Working Hrs Saturday[/TD]
[TD]Final Time Excluding Non working Hrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Thursday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]13:11:44[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Thursday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]15:44:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]3:44:23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]15:17:27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]1:33:48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]11:23:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]9:13:39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Friday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]13:58:03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]0:16:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Monday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]13:22:19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tuesday[/TD]
[TD]11:48:22[/TD]
[TD]7[/TD]
[TD]Saturday[/TD]
[TD]19:09:45[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any Idea or formula as how this can be done, as the start and end can always vary. but final output will be excluding non working hrs.
Now in a column A I have the End time and in column C in have the start time.
Am bit confused with the non working hours
From Sun to Thu it is 13:00:00 hrs -- Non working hours
On Friday it is 15:00:00 hrs -- Non working hours
On Saturday it is 12:00:00 hrs -- Non working hours
now here an employee can Swipe in on Monday and can swipe out on Thursday
1 Case
1) Its easy to get the total time (A-C).
2) But here need to exclude the non working hrs (Tue and Wed --(13+13))
now here an employee can Swipe in on Sunday and can swipe out on Friday
2 Case
1) Its easy to get the total time (A-C).
2) But here need to exclude the non working hrs (Monday to Thursday --(65:00:00))
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]INCIDENT CREATION_date[/TD]
[TD]INCIDENT CREATION_day[/TD]
[TD]INCIDENT Creat Start Time[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl66, width: 60"]END_date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl66, width: 56"]END_day[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]END_Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Actual Non Working Hrs Sun to Thu[/TD]
[TD]Actual Non Working Hrs Friday[/TD]
[TD]Actual Non Working Hrs Saturday[/TD]
[TD]Final Time Excluding Non working Hrs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Thursday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]13:11:44[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Thursday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]15:44:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]3:44:23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]15:17:27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]1:33:48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]11:23:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]9:13:39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Friday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]13:58:03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl65, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Sunday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl66, width: 93"]0:16:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 60"]
<tbody>[TR]
[TD="class: xl65, width: 60"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Monday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 62"]
<tbody>[TR]
[TD="class: xl66, width: 62"]13:22:19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tuesday[/TD]
[TD]11:48:22[/TD]
[TD]7[/TD]
[TD]Saturday[/TD]
[TD]19:09:45[/TD]
[TD]13:00:00[/TD]
[TD]15:00:00[/TD]
[TD]12:00:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any Idea or formula as how this can be done, as the start and end can always vary. but final output will be excluding non working hrs.