Could you help me to calculate overtime

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
>
>

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]...
>
> 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
>
>

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

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

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

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
>
>

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]...
>
> 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
>

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

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

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 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

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]...
>
> 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 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

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
>
>

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

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
>
>

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]...
>
> 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
>

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

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

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

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
>
>

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
>
>

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

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

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]...
>
> 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 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

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

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]...
>
> 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 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

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

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
>
>

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
>
>

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

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

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]...
>
> 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
>