Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Time Calculation Formula

  1. #1
    Join Date
    12th February 2006
    Posts
    11

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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.

  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

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

  4. #4
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Cheers,

    dr

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

    http://www.ExcelVBA.joellerabu.com

  5. #5
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Time Calculation Formula

    Quote Originally Posted by rbrhodes
    If it spans midnight then add 1
    Or just format as [h]:mm to see total hours.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Calculation Of Time With Two Different Time Formats
    By jshayler in forum EXCEL HELP
    Replies: 2
    Last Post: January 30th, 2007, 04:48
  2. Time calculation
    By Barryj in forum EXCEL HELP
    Replies: 4
    Last Post: August 24th, 2006, 09:22
  3. Time Calculation
    By JSAM in forum EXCEL HELP
    Replies: 2
    Last Post: April 11th, 2006, 05:41
  4. a formula for time calculation.
    By tbartlett in forum EXCEL HELP
    Replies: 3
    Last Post: June 28th, 2005, 17:06
  5. Time Calculation (Formula)
    By chris59 in forum EXCEL HELP
    Replies: 3
    Last Post: March 19th, 2005, 04:22

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
  •  
porno