<<Time Counting Question>>

  • Thread starter Thread starter Scooterdog
  • Start date Start date
S

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.
 
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
 
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"))
 
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"))
 
Back
Top