Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What am I not doing right? (Excel 2000)

    The attached is an employee schedule, In column B you have their beginning time, in C their ending time, and in D their total number of hours. This pattern repeats for 2 weeks worth of days with E being the beginning, F the ending, G the total, and so on. I'm trying to get a total sum of our for each employee in Column AV by adding the individual daily totals in D, G, J, etc. but I'm getting weird results. In fact, if you try to get a sum going all the way down column D (for total daily hours), you get weird results too. I think it has something to do with my cell formatting in the daily total and the biweekly total columns. Can someone take a look and tell me what the heck I'm doing wrong. I'm sure it's probably simple, I'm just having trouble getting my brain around it. - Thanks, Sat.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: What am I not doing right? (Excel 2000)

    If you want to sum ELAPSED time, format with(format - cells - custom):
    [h]:mm
    not
    h:mm

    The latter will NOT go over 23:59:59 since there is NO time after this (time starts over at midnight). If you use the elapsed time format you can get hours over 24.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What am I not doing right? (Excel 2000)

    Thank you, thank you. I have never had to use the [h]:mm format. Thanks for the information, as I'm sure I will need it again in the future.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: What am I not doing right? (Excel 2000)

    You are welcome.
    FYI,
    1) You can also use [m]:ss if you want elapsed minutes with seconds
    2) if you want the time in decimal format (eg 3.74 hours) just multiply the time by 24 and format as number with however many decimals places you want.


    Steve

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What am I not doing right? (Excel 2000)

    Hey Steve - you still out there somewhere. Ran into another problem, and I'm fairly sure it's because of this time issue again. I'm attaching a revised spreadsheet - the totals horizontally and vertically work great, thank you!!! However, now, we would like to enter the employee's hourly rate in column AF, and perform a calculation in AG that multiplys the hours accrued by the hourly rate. I'm getting funky results. Any advice?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: What am I not doing right? (Excel 2000)

    Multiply by 24 (hrs/day) Excel stores time in fractions of a DAY.

    Your time (while displayed as hrs/min) is actually stored by excel as days.

    Steve

Posting Permissions

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