Time calculation
Time calculation
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Time calculation

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello,

    I am trying to figure out a simple (hah) time calculation and having hard time.
    I have a start time and stop time for when people come in and want to figure out in formula how many hours they worked.
    I tried different things and cant seem to get it to work.

    ie
    Monday Tuesday wednesd etc..
    9:30 5:30 5:00 9:00 12:00 4:30

    then I wanted to total them for the week.


    anyone able to help please =)

    Randy

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This worked for me (assuming your times are accurate time numbers (i.e., 5:30 is 5:30 pm (not AM)).

    Take subtotals for each day. e.g., =hour(B1-A1) where B1 is your time out. Sum your subtotals and format that cell as a number.

    HTH. Cheers, Nate

  3. #3
    Guest

    Default

    Make the start and end time cells in this format:
    Time - 1:30PM
    If A1 is start time, and B1 is end time, put this formula in C1 for your solution:
    =B1-A1
    Make C1 this custom format (you'll have to create it):
    [h]:mm

    Worked for me.

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cool those suggestions work well, I like the custom Time setting too. Now if you want to make it more of a challenge, On some days the data is entered as OFF, which seems to muck up the whole thing

    I was experimenting with IF and trying to nest an if statement, and it is quite challenging.

    What I used to total up the persons hours was this:

    =(C8-B8)+(E8-D8)+(G8-F8)+(I8-H8)+(K8-J8)+(M8-L8)+(O8-N8) Where the first number is the off time..
    that works good until they have an off in one of the fields..

    Now how could I add up those hours using IF to make a OFF day a 0.

    this is what i was trying so far:
    IF(E7<>"OFF",E7-D7,0, IF(D7<>"OFF",E7-D7,0))
    but that dont work

    Randy

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    A6 being start time. Assuming they type in "off" for both start and end time, check against only one of the cells.

    =IF(A6="off", "0:00", B6-A6)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com