Could you help me to calculate overtime

[Guest]

Hello.
I need your help to calculate an overtime for drivers. I warked out total
time and time allowed to drive for a month (48 hours * 4/5). Now i need to
find a different between hours Total and Allowed (for example, hours Total
for a month is 83:35, Hours allowed - 192:00. to find out if a driver made
overtime i substruct 83:35 from 192:00 - so driver didn't do overtime. So
1st problem, i don't know what format or formula to use to do this
2nd problem - if driver did overtime last month this figure have to float to
the next month. How to do this?
Thank you
Svetlana

 

[Jon Quixley]

Sveta

This might take some explaining since I get the feeling Excel and yo
have just met.

First thing: Handling time in Excel is a bit tricky, but no
impossible. All the cells you are going to be using with times in the
need to be formatted in the following Custom format [hh]:mm You can ge
to this either by pulling down Format tab and selecting the cell or b
pressing Ctrl and 1 together.

Take a look at the attached excel file (I hope you know how to unzi
files!) - the gree cells can be filled in, the yellow cells are outpu
cells- take a look at the formulae in them to see how it compares th
time worked against the maximum allowable. The formula below compare
the time worked against the max (in cell B11), if the time worked i
less than the maximum, the answer is Nil, if not, then it subtracts on
from the other and carries this into the mext month

=IF(D13<$B$11,0,D13-$B$11)

Good luck

+-------------------------------------------------------------------
|Filename: svetlana's help.zip
|Download: http://www.excelforum.com/attachment.php?postid=3722
+-------------------------------------------------------------------

 

[Bob Phillips]

just a suggestion

=MAX(0,D$13-$B$11)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jon Quixley" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

Hello Jon.
Thank you for help but i couldn't open this file, because after i tryed to
download this file, the message "Invalid Attachment specified" appeared.
Could you send it for me again, please.
Thank you
Svetlana

 

[Guest]

Hi again. this time i could open your attachment. thank you
Sveta

 

[Guest]

Thank you very much Jon, your help was very good! but i have another
question. what formula i have to use to show how many hours haven't been done
to meet an allowed hours. for example, it was done 135 hours but driver was
allowed to do 190 hours. so i need to show the result. i have got only one
column to show overtime or "undertime". is it possible?
Thank you
Svetlana

 

[Jon Quixley]

Sveta,

To manage negative time (or undertime as you call it) you are going t
have to change time into proper numbers - Excel can't handle negativ
time!
If you take a look at the attachment again, I have changed the forma
of the max permissable time from 190:00 (hours and minutes) to decima
190.00. The input line in green stays in time format so you can ente
this data as hh:mm. Below this the next line converts this into decima
by multiplying the time by 24. The last line does more or less what i
did before except that if the driver hasn't used up the allocation o
190 hours and has a credit (in the first month of 55 hours), this show
up as a negative decimal number. This is one way of doing this, th
Overtime line shows negative numbers if the driver has not used up al
his 190 hours and a positive number if he has and is into overtime.

=(IF(D13<$B$11,-$B$11--D13,D13-$B$11))

This looks at the max permissable time in B11 and determines whethe
the monthly time D13 is larger. If D13 is larger than B11, then th
next piece -$B11--D13 subtracts B11 from D13 and forces the answer t
be negative - this gives your "Undertime", if D13 is smaller than B1
then the ssecoind part of the statement operates d13-$B11, this give
you a positive answer which is the "Overtime"

All the best
Jo

+-------------------------------------------------------------------
|Filename: svetlana's help.zip
|Download: http://www.excelforum.com/attachment.php?postid=3729
+-------------------------------------------------------------------

 

[Guest]

Thank you very much Jon. But one thing is confusing me.
When Total time for month is, will say, 83:35 hours, this formula =+D12*24
gives you 83.58. So we have a difference in minutes. What shell i do?
Thank you
Sveta

 

[Jon Quixley]

Sveta,

This is part of the wonderful world of Excel that will drive you ma
sooner or later.

83:35 - this is in effect 3 days 11 hours and 35 minutes. Excel
handles this as 3.482638888889 or in general terms 3.48 days. Thi
multiplied by 24 gives you the number of hours IN DECIMAL - that is t
say 83 hours and 0.58 (or just over half) of an hour (or 35 minutes t
you).

83:35 and 83.58 are the same thing, one is in hours and minutes th
other in decimal.

In the Overtime line you will have -106.42 - this is the result o
subtracting 83:35 from 190:00. As I explained earlier, Excel can'
handle the idea of negative time which is what -106.42 represents.
don't know whether you are happy with this as a decimal or need t
translate this back into time for some other purpose. I hope not a
it's not easy

 

[Jon Quixley]

Sveta,

To change the overtime line from negative time (if it is) add this
formula to the row immediately below it (row 16)

=IF(D15<0,+D15-D15-D15,D15)/24
Set the cell to Custom format [hh]:mm and you shiould get for a
negative overtime of -160.42 a time of 106:25

Jon

 

[Guest]

Hi Jon
Thank you very much for your help. and YES, Excel can give you just a bit of
headache (my boss can't understand what can be so complicate in calculating
the difference between hours!!!)
So what i've done may be not very clever and smart, but i made another
column for "undertime" and used formula
=IF(D13>$B$11,0,B11-$D$13)
(so used your formula other way round )
seems to be it is working.
Thank you very much!
Svetlana