+ Reply to Thread
Results 1 to 6 of 6

calculate difference in time to hours

  1. #1
    Chris
    Guest

    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!

  2. #2
    Jason Morin
    Guest

    Re: calculate difference in time to hours

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


  3. #3
    benb
    Guest

    RE: calculate difference in time to hours

    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!


  4. #4
    Arvi Laanemets
    Guest

    Re: calculate difference in time to hours

    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" <Chris@discussions.microsoft.com> wrote in message
    news:4269C39C-314C-47C5-886A-01C6C65C5B1B@microsoft.com...
    > 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!




  5. #5
    Superslinky
    Guest

    Re: calculate difference in time to hours


    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

  6. #6
    Arvi Laanemets
    Guest

    Re: calculate difference in time to hours

    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" <Superslinky.1j29ho@news.officefrustration.com> wrote in
    message news:Superslinky.1j29ho@news.officefrustration.com...
    >
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1