Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Subtracting Hours in Excel

    Hello,
    I have a spreadsheet that keeps track of the number of hours an employee has worked as it cannot exceed a certain amount of hours. In my calculation the max hours is 1039 and the employee has worked 39 hours and 45 minutes, when i subtract 39.45 from 1039 the result is 999.55. How can I get Excel to display the difference in hours/minutes so that it would display 999.15?

    Thanks for any assistance you can provide!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Divide the 1039 by 24 to convert it to the correct number of days (43.29 approx) and then subtract 39:45 from that number - not 39.45). Format the result as [h]:mm
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks for the reply Rory. I don't need to convert it to number of days I need the result to display in hours/minutes.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Mostate,

    You can do it this way using a custom cell format:
    Timecalcs.JPG
    Just make sure to enter the values in Cols A&B as hours:minutes, 1039:00 & 39:45 respectively. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MOSTATE (2014-05-21)

  6. #5
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    What a difference a colon makes I tried that custom format before I posted my thread but used the . instead of the : to separate the hours and minutes so it didn't work for me. It works great, now. Thanks so much!!

Posting Permissions

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