<<Time Counting Question>>

[Scooterdog]

At my job, I get paid waiting time until Overtime starts.
Example:
Arrive: 16:00
Allowance: 01:00
Waiting Time Starts: 17:00
Overtime Starts: 19:00
Off Duty: 20:00

With this scenario, I would make "2 hours" waiting time and
"1 hour" overtime.
I need a formula that would allow me to calculate the start
of my waiting time(17:00) up to BUT not go beyond 19:00.

This is just a example. My hours are not fixed or set. This
will help me to get started though.
Thank you for your time.

 

[David McRitchie]

What problem did you encounter when you tried to solve your
problem. As long as you aren't going through midnight these
are simple subtractions, the cells should be formatted at
[h]:mm so that if you overflow in a total for the week that you
retain the number of hours instead of overflowing into days.

If you have a subtraction that where the later time ran through
midgnight you and to use something like
=C2-B2+(B2>C2) instead of =C2-B2
the condition will return 0 or 1, with 1 representing 1 day
or 24 hours when working with dates and time. More information
on Date and Time in
.http://www.mvps.org/dmcritchie/excel/datetime.htm

If you want to convert an Excel time into a decimal number of
hours multiply by 24.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Peo Sjoblom]

With the arrive time in A1 and the Off Duty time in B1 this formula will
always return the wait time

=IF(B1-A1<=1/24,--"0:00",MIN(B1-(A1+1/24),--"2:00"))

 

[Peo Sjoblom]

After reading David's answer I realized you might work after midnight, so
you can use this formula instead

=IF(MOD(B1-A1,1)<=1/24,--"0:00",MIN(MOD(B1-(A1+1/24),1),--"2:00"))