Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula help (office XP)

    How do I write a formula that calculates time for me? I want to be able to say I start work at 0800 and end at 1800 and have it calculate the time for me on a spread sheet. As always thank you for your help this lounge makes my life so much easier!

    Dale

  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: formula help (office XP)

    You can entereg in A1)
    08:00
    in a cell
    and enter (eg B1)
    18:00

    Then in (eg) C1 enter:
    =B1-A1
    and it will display:
    10:00

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (office XP)

    Thanks, still having a bit of problems with the formula. I have a date that has added it's self to the total some how, and sometimes I get a strange number in weekly total.If I reput in the total formula like =B6-A6 then the problem fixes it'self, but I can not do this everytime I go to use the time sheet. for example see line 6. Any input would be helpful.

    thanks,
    Dale

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula help (office XP)

    1) Set the format for D213 to hh:mm.
    2) Change the formula in D15 to =SUM(D213), this is both shorter and easier than =D2+D3+...+D13.
    3) Change the format for D15 to the custom format [hh]:mm. The square brackets [ ] indicate that the format is for accumulated time, i.e. hours can be higher than 24.

  5. #5
    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: formula help (office XP)

    Make sure they are formatted correctly to display hours, the total needs elapsed hours (as opposed to hours in a day). You can set them all to this.

    select the range of cells(d1:d15)
    Format -cells-number(tab)
    custom
    [h]:mm
    <ok>

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (office XP)

    Thanks it is all set up now.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Soldotna, Alaska, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (office XP)

    Thanks a bunch it works right now.

Posting Permissions

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