Hrs calculation according to service window

[ushaanu]

hi all,

how to Hrs calculation according to service window. like working window 9 to 17:30

how to calculate working hrs .

Agent In TimeAgent Out TimeWorking Hrs
12/02/2017 09:0012/02/2017 10:00 1
12/02/2017 13:0012/02/2017 18:00 4
in 2nd case total working hrs is 5 but as per service window is 4 hrs. I need calculation according to service window.

Thanks
Anu

 

[Hui]

Simple subtract the Start Time from the End Time
Then apply a Custom Format to the cell of HH:MM

 

[bosco_yip]

Maybe,

=MIN((B2-A2)*24,4)

Regards
Bosco

 

[NARAYANK991]

Hi ,

I think it is not so simple ; if the IN time is 16:00 hrs. or 4 pm , and the OUT time is 21:30 hrs or 9:30 pm , then the hours calculation should return 1.5 hours and not 4 hours.

Narayan

 

[ushaanu]

hi Hui,

I have data like file enclosed

 

[ushaanu]

yes sir I need like this only IN time is 16:00 hrs. or 4 pm , and the OUT time is 21:30 hrs or 9:30 pm , then the hours calculation should return 1.5 hours and not 4 hours.

 

[bosco_yip]

Maybe,

D2, copied down :

=MAX((MIN(0+"17:30",MOD(B2,1))-MAX(0+"9:00",MOD(A2,1)))*24,0)

Regards
Bosco