Results 1 to 6 of 6

Thread: Time in excel97

  1. #1
    opg
    Guest

    Time in excel97

    I am trying to set up a worksheet to calculate time cards. I want to be able to enter starting time, ending time and lunch time. I can format the all of the above but cannot figure how to sum the hours worked. The result per day is formatted as time (10:15) worked for the day but when I try to total for the week my calculations are wrong. I have tried formatting the sum cell as time, number, and general. Nothing seems to work. Can anyone help me with this? Thanks!

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in excel97

    Have a quick look at this previous Excel post here and see if it helps.

  3. #3
    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

    Re: Time in excel97

    Hi,
    You can fix this by changing your total formula to:
    =sum(A1:A5)*24
    changing the range as appropriate, and formatting it as general. (Excel stores dates/times as a number with the decimal part representing the time as a proportion of 24 hours.)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in excel97

    Try using a custom format of:

    [h]:mm

    The brakets must be entered into the format. This tells Excel to fromat the time value as an elapsed time (can be more than 24 hours) not a time of day.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in excel97

    That will give you hours and decimal part of an hour, not hours and minutes. This is fine if it is what he wants.
    Legare Coleman

  6. #6
    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

    Re: Time in excel97

    Oops, sorry - I just assumed there was going to be a wage calculation following on. One day I'll answer the question asked rather than second-guessing them!
    Nice tip about the hour format - I'd completely forgotten that existed!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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