Excel Time Comparission for marking Idle Hrs. Please help guys

[sibin]

I have to calculate the idle Hrs of a working employee.

Three shifts are there.

Morning 7AM to 3PM
Evening 3PM to 11PM
Night 11 PM to 7AM.

Also if overtime then shift pattern is

Morning 7AM to 7PM
Evening 11AM to 11PM
Night 7PM to 7AM.

So the in time for an employee will be based on the above time range.

Example:

We have idle Hrs for the following time set.

10:20:00 AM to 10:40:00 AM = 00:20:00
10:40:00 AM to 11:25:00 AM = 00:45:00
02:40:00 PM to 03:00:00 PM = 00:20:00
03:00:00 PM to 03:30:00 PM = 00:30:00
03:45:00 PM to 05:15:00PM = 01:25:00


One of the employee came at 07:13:46AM and leaving office at 07:19:26PM. So based on the above idle Hrs she has 03:20:00 hrs idle.

So the idle time may change for each day. Based on the in time i need the total idle Hrs.
[TABLE="width: 234"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]
Please help me in this...[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

why are they idle, not enough work (but ready to do whats available) or they loaf off and are missing.

The first instance they are productive

measuring an employees time in seconds seems overly officious

 

[strooman]

One of the employee came at 07:13:46AM and leaving office at 07:19:26PM. So based on the above idle Hrs she has 03:20:00 hrs idle.

That 03:20:00, is that a typo? I calculate 03:25:00!

Check out this post. I had a similar problem.

In that post I got a very satisfying solution from Peter_SSs
Don't forget to have a look at the given solutions by Andrew Poulsom.
See, if it fits your needs.

[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]From[/TD]
[TD]Till[/TD]
[TD]Idle[/TD]
[TD]
[/TD]
[TD]From[/TD]
[TD]Till[/TD]
[TD]Idle[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10:20[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]00:20[/TD]
[TD][/TD]
[TD="align: right"]07:13[/TD]
[TD="align: right"]19:19[/TD]
[TD="align: right"]0:20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]11:25[/TD]
[TD="align: right"]00:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0:45[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]14:40[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]00:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0:20[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]15:30[/TD]
[TD="align: right"]00:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0:30[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]15:45[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]01:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1:30[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03:25[/TD]
[TD][/TD]
[TD]Total idle[/TD]
[TD]>>>[/TD]
[TD="align: right"]3:25[/TD]
[/TR]
</tbody>[/TABLE]

Formula goes in G2

=MAX((B2-A2-MAX(0;E$2-A2)-MAX(0;B2-F$2));0)

Drag down

Credits go to: Peter_SSs

 

[mole999]

That 03:20:00, is that a typo? I calculate 03:25:00!

03:45:00 PM to 05:15:00PM = 01:25:00 is where that five minutes were

 

[sibin]

That 03:20:00, is that a typo? I calculate 03:25:00!

Check out this post. I had a similar problem.

In that post I got a very satisfying solution from Peter_SSs
Don't forget to have a look at the given solutions by Andrew Poulsom.
See, if it fits your needs.

[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]From[/TD]
[TD]Till[/TD]
[TD]Idle[/TD]
[TD]
[/TD]
[TD]From[/TD]
[TD]Till[/TD]
[TD]Idle[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10:20[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]00:20[/TD]
[TD][/TD]
[TD="align: right"]07:13[/TD]
[TD="align: right"]19:19[/TD]
[TD="align: right"]0:20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]10:40[/TD]
[TD="align: right"]11:25[/TD]
[TD="align: right"]00:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0:45[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]14:40[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]00:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0:20[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]15:30[/TD]
[TD="align: right"]00:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0:30[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]15:45[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]01:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1:30[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03:25[/TD]
[TD][/TD]
[TD]Total idle[/TD]
[TD]>>>[/TD]
[TD="align: right"]3:25[/TD]
[/TR]
</tbody>[/TABLE]

Formula goes in G2

=MAX((B2-A2-MAX(0;E$2-A2)-MAX(0;B2-F$2));0)

Drag down

Credits go to: Peter_SSs

I getting invalid message...

 

[mole999]

maybe change ; for , it can be system settings dependant

 

[strooman]

I getting invalid message...

maybe change ; for , it can be system settings dependant

Correct, Dutch version uses ; as separator while US of A uses ,
Always have trouble with that. Especially with array's.
International = Comma for Columns and Semi colon for Rows
Dutch = \ for Columns and Semi colon for Rows