Could you help me to calculate overtime
 

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

Sveta

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

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

Take a look at the attached excel file (I hope you know how to unzip
files!) - the gree cells can be filled in, the yellow cells are output
cells- take a look at the formulae in them to see how it compares the
time worked against the maximum allowable. The formula below compares
the time worked against the max (in cell B11), if the time worked is
less than the maximum, the answer is Nil, if not, then it subtracts one
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 |
+-------------------------------------------------------------------+

--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

just a suggestion

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

--

HTH

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


"Jon Quixley"
wrote in message
...

Sveta

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

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

Take a look at the attached excel file (I hope you know how to unzip
files!) - the gree cells can be filled in, the yellow cells are output
cells- take a look at the formulae in them to see how it compares the
time worked against the maximum allowable. The formula below compares
the time worked against the max (in cell B11), if the time worked is
less than the maximum, the answer is Nil, if not, then it subtracts one
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 |
+-------------------------------------------------------------------+

--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile:
http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

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

"Jon Quixley" wrote:


Sveta

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

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

Take a look at the attached excel file (I hope you know how to unzip
files!) - the gree cells can be filled in, the yellow cells are output
cells- take a look at the formulae in them to see how it compares the
time worked against the maximum allowable. The formula below compares
the time worked against the max (in cell B11), if the time worked is
less than the maximum, the answer is Nil, if not, then it subtracts one
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 |
+-------------------------------------------------------------------+

--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

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

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" wrote:


Sveta

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

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

Take a look at the attached excel file (I hope you know how to unzip
files!) - the gree cells can be filled in, the yellow cells are output
cells- take a look at the formulae in them to see how it compares the
time worked against the maximum allowable. The formula below compares
the time worked against the max (in cell B11), if the time worked is
less than the maximum, the answer is Nil, if not, then it subtracts one
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 |
+-------------------------------------------------------------------+

--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

Sveta,

To manage negative time (or undertime as you call it) you are going to
have to change time into proper numbers - Excel can't handle negative
time!
If you take a look at the attachment again, I have changed the format
of the max permissable time from 190:00 (hours and minutes) to decimal
190.00. The input line in green stays in time format so you can enter
this data as hh:mm. Below this the next line converts this into decimal
by multiplying the time by 24. The last line does more or less what it
did before except that if the driver hasn't used up the allocation of
190 hours and has a credit (in the first month of 55 hours), this shows
up as a negative decimal number. This is one way of doing this, the
Overtime line shows negative numbers if the driver has not used up all
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 whether
the monthly time D13 is larger. If D13 is larger than B11, then the
next piece -$B11--D13 subtracts B11 from D13 and forces the answer to
be negative - this gives your "Undertime", if D13 is smaller than B11
then the ssecoind part of the statement operates d13-$B11, this gives
you a positive answer which is the "Overtime"

All the best
Jon


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

--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

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

Sveta,

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

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

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

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


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

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


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=398089

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