Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 24

Thread: Need help with total monthly hours

  1. #11
    Seeker dcope7's Avatar
    Join Date
    Jan 2021
    Posts
    13
    Articles
    0
    Excel Version
    10


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Also, if there is non-billable time it will subtract that's what the +12 is it's row 12

  2. #12
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by dcope7 View Post
    Also, if there is non-billable time it will subtract that's what the +12 is it's row 12

    12 what? sseconds? minutes? hours? days?

  3. #13
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    When you key in 2:05 you're typing in 2:05 AM
    When you key in 10:05 you're typing 10:05 AM
    The actual values in those cells are 0.086805556 and 0.420138889. These are fractions of a day; 0.25 would be 6am, 0.75 would be 6pm. The format you choose only changes what's displayed, not the value itself.
    Excel's date and time works in days.
    If you add 12, you're adding 12 days, or 288 hours. The value in D16 is 13 (days) showing as 312 hours.

  4. #14
    Seeker dcope7's Avatar
    Join Date
    Jan 2021
    Posts
    13
    Articles
    0
    Excel Version
    10
    Quote Originally Posted by p45cal View Post
    12 what? sseconds? minutes? hours? days?


    It could be all. most of the time it will be only 30 minutes if there is any.

  5. #15
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    What's the 12 about in this formula in cell D16:
    =(D11+D15)+12
    ??
    Cell D16!!

  6. #16
    Seeker dcope7's Avatar
    Join Date
    Jan 2021
    Posts
    13
    Articles
    0
    Excel Version
    10
    If I take out the formula in rows 11 and rows 25 and change the format to h:mm:ss everything works fine but I have to manually calculate all the times except rows15,16,29 and 30 then cell s 30 works fine. I put the formulas in so me nor my staff would have to manually calculate all the times, just the pick up,arrival, departure and return. We are currently having to total the times manually. If the times were all the same like, 10:00 / 3:00 it would not be a problem. I was just trying ot make it easier on everyone involved. I can change it to different formats but then I get military times.

  7. #17
    Seeker dcope7's Avatar
    Join Date
    Jan 2021
    Posts
    13
    Articles
    0
    Excel Version
    10
    It subtracts any non-billable times

  8. #18
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by dcope7 View Post
    It subtracts any non-billable times
    I'm talking about row 16, NOT ROW 12!
    The 12 is a hard-coded 12 in the formula, and it adds (not subtracts) 12. The formula in cell D16 (yes, the one in row 16, not row 12).
    What's the value 12 doing there?
    I now have no hair left to tear out.

  9. #19
    Seeker dcope7's Avatar
    Join Date
    Jan 2021
    Posts
    13
    Articles
    0
    Excel Version
    10
    Quote Originally Posted by p45cal View Post
    I'm talking about row 16, NOT ROW 12!
    The 12 is a hard-coded 12 in the formula, and it adds (not subtracts) 12. The formula in cell D16 (yes, the one in row 16, not row 12).
    What's the value 12 doing there?
    I now have no hair left to tear out.
    Ok row 16 calculates rows 11 and 15, plus it subtracts row 12 and yes I know I have it adding but if you actually add 0:30 in row 12 it will subtract from the totals in row 16. I am attaching the original sheet I started with and you can see how it worked before I added formulas to rows 11 and 25 and formatted with am/pm.
    Attached Files Attached Files

  10. #20
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by dcope7 View Post
    plus it subtracts row 12
    It does not! it adds the value 12 and doesn't even look at row 12! In this context it adds 12 days (=288 hours). Barmy.
    Until you get formulae right in the cells you want to sum, you've no hope of getting the correct total hours.
    I'll look at your newly attached file later - out of time at the moment.

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

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