calculate difference in time to hours

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!

E1:

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

F1:

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

HTH
Jason
Atlanta, GA

>-----Original Message-----
>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!
>.
>

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?

"Chris" wrote:

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

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

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Chris" <[email protected]> wrote in message
news:[email protected]...
> 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!

I have a similar question but what I'm trying to achive is in A1 I enter
my time in, B1 is my lunch duration entered as 0.5 for a half hour 0.75
for 45 min's ect. and C1 I enter time out and have the total time in
E1. I've can get the total time without the lunch but not with it in a
decimal format. Any ideas?


benb Wrote:
> 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?
>
> "Chris" wrote:
> -
> 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!-


--
Superslinky

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


"Superslinky" <[email protected]> wrote in
message news:[email protected]...
>
> I have a similar question but what I'm trying to achive is in A1 I enter
> my time in, B1 is my lunch duration entered as 0.5 for a half hour 0.75
> for 45 min's ect. and C1 I enter time out and have the total time in
> E1. I've can get the total time without the lunch but not with it in a
> decimal format. Any ideas?
>
>
> benb Wrote:
> > 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?
> >
> > "Chris" wrote:
> > -
> > 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!-
>
>
> --
> Superslinky