Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    I need to be able to calculate hours worked when on a 24 hour roster.

    For example in

    A1 is start time - 6:00 (cell format shows 06:00;00 AM); B1 is finish - 18:00 (cell format shows 06:00;00 PM); C1 contains =B1-A1 give 12:00, this is correct
    A2 is start time - 20:00 (cell format shows 06:00;00 PM); B1 is finish (cell format shows 06:00;00 AM) - 06:00; C1 contains =B2-A2 give ############, but should what that means.

    Any thoughts on how how to sort out a formula to provide the correct number of hours worked.

    I hope this makes sense

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    One option would be to enter both the date and the time.

    If you prefer to enter just the time, use the following formula in C1:

    =B1-A1+(B1<A1)

    Format C1 as a time, and fill down.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hans - not sure if your suggested calculation is working the way I need. I have attached an example of the speadsheet being use.

    Any other thoughts would be greatly appreciated

    Quote Originally Posted by HansV View Post
    One option would be to enter both the date and the time.

    If you prefer to enter just the time, use the following formula in C1:

    =B1-A1+(B1<A1)

    Format C1 as a time, and fill down.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You didn't use my suggestion! It does work the way you want, see this version:


    [attachment=86433:time test.xls]
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You didn't use my suggestion! It does work the way you want, see this version:


    [attachment=86433:time test.xls]
    The formula HansV provided does work, In your first post you mentioned calculating hours worked. Do you want to take the results and multiply the total amount by 'say an hourly rate'?

    In order for me to do this, I had to tweak it a bit:

    =(B2-A2+(B2<A2))*24-----and then change the format in the "C" column to number format instead of time format.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hans/Stan

    Thanks for you reply.

    Calculation working OK, however is a problem with calculating total hours (see attached)

    Any thoughts?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Times are displayed as time-of-the-day by default, i.e. they don't go above 23:59. To display cumulative time, select the cell and set a custom number format

    [hh]:mm

    This will show 34:00 in your example.


  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Hans - you have done it again. Very much appreciated

Posting Permissions

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