productivity calculation

Hi, can anyone help me create a formula for the following:

I have column A with times (hh:mm) and column B with times (hh:mm).

These times represent lost time i.e 09:35 to 11:07 no work was done.

So in column C I have =B1-A1 +IF(A2>B2,1) and this gives the result as 01:32 (hh:mm).

Still with me this far?

Now comes the bit I'm stuck on:-

Next I need to take the time 01:32 (hh:mm) and multiply it by 15 (Format "number" with 0 decimal places).

The 15 represents an activity that can be done 15 times an hour so the answer should be 22 something or other.................

Can anyone help me with this brain teaser?

Hi,

When I take the time 01:32 (hh:mm) and multiply it by 15 (Format "number" with 0 decimal places),

I get 23 if the result is formatted hh

Is that what you are looking for ?

HTH
Carim

Originally Posted by Carim

Hi,

When I take the time 01:32 (hh:mm) and multiply it by 15 (Format "number" with 0 decimal places),

I get 23 if the result is formatted hh

Is that what you are looking for ?

HTH
Carim

Thanks that works ok but if you multiply by 17 (another activity) the result comes up as 02:04 in hh:mm format when it should be 23ish (sorry my maths is rubbish). So I think I need the result to be in number format. Any other ideas?

Format the cell as a decimal and use:

=24*C1*Number

Where C1 contains your time and Number represent the units_per_hour that you mentioned. Note that the resultant value is not therefore in a time format.

Originally Posted by RichardSchollar

Format the cell as a decimal and use:

=24*C1*Number

Where C1 contains your time and Number represent the units_per_hour that you mentioned. Note that the resultant value is not therefore in a time format.

Aah that seems to work, it looks so simple too! Thanks very much for that!

Originally Posted by Tanglefoot

Aah that seems to work, it looks so simple too! Thanks very much for that!

Try this:
A= start time
B= End time

=((B1-A1)+(A1>B1))*17

Format as [h]:mm