KPI spreadsheet help please!!!

[pobrien31]

Hi,

I have a spreadsheet that I have inherited from a chap that has sinc
left the company. He was an accountant and was therefore into fanc
formulas.

This particular spreadsheet details KPI's (Key performance indicators
for start and end times of employees, the number of pieces that wer
picked, the number of men, minutes per piece, pieces per men and tim
per piece per man.

So the table looks something like this:

No. of Men, Start Time, End Time, Total Hours, Total Mins, No o
Pieces, Mins per Piece, Pieces per Men, Time per piece per man.

These things are always hard to explain when you can't publish th
thing for people to see.

As I say, for whatever reason the guy before me has some way fanc
formulas in the time fields, yet the start hour and start minutes ar
in their own separate cells, as are the end hour and end minute. I ca
only assume that this has some bearing on the calculation.

It's just seems a lot of work to me.

I just need some helpd determining the best way to work out the tota
mins worked. The number of pieces picked is entered manually as is th
No. of Men. The final three columns then work out 'Mins per piece'
'Pieces per men' and 'Time per piece per man'.

If anyone can help then I would be most gratefull.

Excuse the diatribe, but never seem to be able to explain thing
quickly without going on a bit!!!

Regards,

Patrick

 

[Frank Kabel]

Hi
if you have the start time in C1, the end time in D1, the no of pieces
in E1 and the mins per pieces in F1 try the folowing:
1. G1: (Total hours)
=D1-C1
if the time can span midnight use
=D1-C1+(D1<C1)
format as time

2. Total minutes worked:
=G1*24*60 (for one man)

 

[Bob Phillips]

Patrick,

It is a little difficult without details, but let's say that the start hours
are in B3, start mins in C3, end hours in D3, end min in E4, the number of
minutes can be calculated as

=(TIME(D3,E3,0)-TIME(B3,C3,0))*24*60

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pobrien31]

Patrick,

It is a little difficult without details, but let's say that th
start hours
are in B3, start mins in C3, end hours in D3, end min in E4, th
number of
minutes can be calculated as

=(TIME(D3,E3,0)-TIME(B3,C3,0))*24*60

Hi Bob,

Nice one thanks, that is working fine now.

How about those peeps that we have working from one day into the nex
though??

I.E. - Start at 23:45 and finishes at 00:15?

Thanks again,

Patric

 

[Frank Kabel]

Hi
if your working time can span midnight use the following (A1: starting
time, B1: ending time9
=B1-A1+(B1<A1)

--
Regards
Frank Kabel
Frankfurt, Germany

Patrick,

It is a little difficult without details, but let's say that the
start hours
are in B3, start mins in C3, end hours in D3, end min in E4, the
number of
minutes can be calculated as

=(TIME(D3,E3,0)-TIME(B3,C3,0))*24*60

Hi Bob,

Nice one thanks, that is working fine now.

How about those peeps that we have working from one day into the next
though??

I.E. - Start at 23:45 and finishes at 00:15?

Thanks again,

Patrick

 

[Bob Phillips]

Patrick,

How about this

=(TIME(D3,E3,0)-TIME(B3,C3,0)+((TIME(B3,C3,0)>TIME(D3,E3,0))*1))*24*60

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Patrick,

It is a little difficult without details, but let's say that the
start hours
are in B3, start mins in C3, end hours in D3, end min in E4, the
number of
minutes can be calculated as

=(TIME(D3,E3,0)-TIME(B3,C3,0))*24*60

Hi Bob,

Nice one thanks, that is working fine now.

How about those peeps that we have working from one day into the next
though??

I.E. - Start at 23:45 and finishes at 00:15?

Thanks again,

Patrick

 

[pobrien31]

Patrick,

How about this

=(TIME(D3,E3,0)-TIME(B3,C3,0)+((TIME(B3,C3,0)>TIME(D3,E3,0))*1))*24*60

Bob,

You are a star - thankyou ever so much.

Regards,

Patrick