calculate difference in time to hours

[Guest]

I am trying to create a spreadsheet to calculate hours worked. I need to know
what formulas to use to calculate the time in hours and overtime hours. for
example:

A1= 6:00 am start time
B1= 11:15 am start lunch
C1= 11:45 am end lunch
D1= 17:00 pm end time

I need E1 to reflect total hours worked to a maximum of 10 hours/day and I
need F1 to reflect overtime time hours in excess of 10 hours/day.

E1= 10.00 regular hours worked
F1= 00.50 overtime hours worked

PLEASE HELP!

 

[Arvi Laanemets]

Hi

E1=MIN(10/24,(D1-A1)-(C1-B1))
F1=MAX(0,(D1-A1)-(C1-B1)-10/24)
formatted as "hh:mm"

or
E1=MIN(10,((D1-A1)-(C1-B1))*24)
F1=MIN(0,((D1-A1)-(C1-B1))*24-10)
formatted as General or Numeric

 

[Jason Morin]

E1:

=MIN(10,(D1-A1-(C1-B1))*24)

F1:

=MAX(0,(D1-A1-(C1-B1))*24-10)

HTH
Jason
Atlanta, GA

 

[Guest]

Use and IF() function in the two cells like IF((B1-A1)+(D1-C1)>=10, 10,
(B1-A1)+(D1-C1)) for cell E1. You can adapt the same function for F1 to show
overtime. Is that what you are looking for?

 

[Arvi Laanemets]

Hi

E1=MIN(10/24,(D1-A1)-0.5/24)
F1=MAX(0,(D1-A1)-0,5/24-10/24)
formatted as "hh:mm"

or
E1=MIN(10,(D1-A1)*24-0.5)
F1=MIN(0,(D1-A1)*24-0.5-10)
formatted as General or Numeric

When number of regular hours differs from 10, replace this number in both
formulas.


Arvi Laanemets