Announcement

Collapse
No announcement yet.

Time Calculation Formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Time Calculation Formula

    Hello,
    I'm a total newbie to this forum and not really more then a beginner in excel use.

    I have attached a time card that needs a formula to calculate the time for the high lighted areas.
    In+out-in+out= total hours.

    To be honest i have not been able to take the time to absorb how hours and dates work in Excel so i'm here for any help i can get.

    Please help me out here.
    File is attached.

    Thanks in advance
    Trix
    Attached Files

  • #2
    Re: Time Calculation Formula

    Assuming all the times are in the same day (I think you meant for your last Out time to be 3:45 PM not the AM that is in your attachment), thyen you can use:

    =Q1-N1-(P1-O1)

    Format the cell as Number > Time > 13:30 o eliminate the AM/PM display.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Re: Time Calculation Formula

      Addendum: Also have a look at the Dates and Times help link found here:

      http://www.ozgrid.com/Excel/
      Last edited by thomach; May 11th, 2007, 23:53.
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Re: Time Calculation Formula

        Hi Trixboomer,

        Excel treats a day as a number = 1. ie 1 = 1 whole day. so any part of a day is a fraction, actually decimal. midnight to noon is 12 hours to us humans but it's 1/2 a day to Excel ie .5 of a day. so far so good.

        What about 6AM? to us that's early! to Excel that is only .25 of a day. noon is .5 of a day, 6 pm is .75 of a day.

        So forget base 24 or Am/Pm base 12.

        Just:

        (Latest time - earlier time)*24 = hours elapsed

        You must subtract the lesser time from the greater and multiply by 24 to get number of hours. If it spans midnight then add 1. the concept is simple the formula is hell <g>.

        The formula for spanning midnight is:

        =(F4-C4+(F4<C4))*24


        Found here (a classic)

        http://www.cpearson.com/excel/overtime.htm
        Attached Files
        Cheers,

        dr

        "Questions, help and advice for free, small projects by donation. large projects by quote"

        http://www.ExcelVBA.joellerabu.com

        Comment


        • #5
          Re: Time Calculation Formula

          Originally posted by rbrhodes
          If it spans midnight then add 1
          Or just format as [h]:mm to see total hours.
          Entia non sunt multiplicanda sine necessitate.

          Comment

          Working...
          X