Results 1 to 7 of 7

Thread: Excel Formula

  1. #1
    DJC
    Guest

    Excel Formula

    I need a formula to put in a cell in Excel.
    It is a time formula for tracking hours worked
    first cell would be start time of shift say 21:30 hours and end time of shift would be 05:30 hours a 8 hour period. However some times the start is 23:27 an end is 02:26 hours and since most start stop times start in one 24 hour period and end in a different 24 hour period.
    Any help would be appreciated
    Thanks

    [img]/w3timages/icons/smile.gif[/img]

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula

    Try this:

    =(1 -StartTime)+StopTime

  3. #3
    DJC
    Guest

    Re: Excel Formula

    Hello
    I tried that but it did not work but I thank you it seems to be on the right track.
    In cell G42 I have the time of 23:00
    In Cell G43 I have the time 07:00
    I need Cell G44 to have a formula that computes the hours and minutes worked in this case 8 hours. In a case of some one working 7 hours and 15 minutes it should reflect 7.25 hours worked. Remember I am using a 24 clock for time and many of the workers work from one 24 hour period in to the next 24 hour period. as in from 21:30 to 05:30 hours
    Again thanks for any help you can give.
    It is truly appreciated

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

    Re: Excel Formula

    <font color=blue>=(24 - StartTime)+EndTime </font color=blue>seems to give the correct result but formatted as 07:15 not 7.25
    I've just written some macro code to import workers times from a CSV, total them, add a tea-break allowance [img]/w3timages/icons/crazy.gif[/img] etc. Formatting the cells for correct display (i.e. hh:mm) just did not work for me - in frustration I ended up using everything as strings and extracting the values before and after the ":"
    Still, I've got a working solution so I'm happy.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula

    To subtract two time values with the result in hours and minutes: (8:30 - eight hours and thirty minutes)

    =1-StartTime+EndTime
    Format this cell as time

    To subtract two time values with the result in hours and decimal fraction of hours (8.5 eight hours and thiry minutes):

    =HOUR(1-StartTime+EndTime) +MINUTE(1-StartTime+EndTime)/60
    Format this cell as Number.

  6. #6
    DJC
    Guest

    Re: Excel Formula

    [img]/w3timages/icons/grin.gif[/img] CPOD:
    This what I needed thanks for the help
    I have installed your formula and every thing now works the way I need it to.

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

    Re: Excel Formula

    Thanks from me too.
    I must play around with your suggestion.

Posting Permissions

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