Well, I should have added to my previous post to Custom Format the cells
as
"[h]:mm" (without the quotes) to prevent the hours rolling over into days
when they get to 24 hours.
With the total Police and Medical hours in C2 and D2 respectively as
before:
Cell C8: Police hours under 41:40 minutes:
=MIN((41+2/3)/24,C2)
Cell D8: Medical hours under 41:40 minutes:
=MIN((41+2/3)/24,D2)
Both formatted as above. If the total hours for Police or Medical are
over
41:40 minutes these cells will show 41:40
(The TIME() function does not work at more then 24 hours so because 1
hour
is 1/24th of a day, 41 2/3 divided by 24 converts the 41.66666666 decimal
into a time when the cell is formated as [h]:mm)
Cell C10: Police hours over 41:40:
=MAX(C2-(41+2/3)/24,0)
Cell D10: Medical hours over 41:40:
=MAX(D2-(41+2/3)/24,0)
Assuming that you want the percentages of the total Police + Medical
times
then:
% Police hours:
Under 41:40: =C8/(C2+D2)
Over 41:40: =C10/(C2+D2)
% Medical:
Under 41:40: =D8/(C2+D2)
Over 41:40: =D10/(C2+D2)
All formated as percentage
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
AOU said:
Thanks Sandy.
I need to know the hours used in percent for each unit in two blocks.
The
first bloke goes up to 41hours and 41 minutes. The second is anything
above
that figure.
A little more info: Up to 41:40hours Medical hours are budgeted to 30%
and
above that it is 25%. I need to know when that figure is passed my the
Medical unit and by how much (in percent). And the easiest way is to
know
how
much each unit is useing for each bloke.
Hope that makes it any clearer!
--
AOU
:
Im not Bernard but:
C2 formula:
=SUMIF(A2:A7,"Police",B2:B7)
D2 formula:
=SUMIF(A2:A7,"Medical",B2:B7)
I don't know what you mean by "Percentage over/under 30 hours"
percentage
of
what? The totalof all hour? The totalof only the Police ot Medical?
or
what?
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
I have to collate monthly hours flown by two units.
I need a break down in percent of each unit useage for the first 30
hours
and then again for the remaining hours above 30.
A sample below:
Columns:
A B
Unit Hours
Police 0:20
Police 0:30
Medical 0:10
Police 0:50
Medical 0:05
medical 0:55
and so on......
Cells: Total hours for the month
C2 D2
Police Medical
C8 D8
Police Medical
Percentage Percentage of hours
of hours used used under 30 hours
under 30hours.
Cells:
C10 D10
Percentage Percentage
use over use over
30hours 30hours
Hope that helps.
