Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Elasped Time (2000)

    This seems such a simple thing to do, that it is driving up the wall that I can't figure it out. I am attempting to figure out how long a specific consultant is used. I have three columns. The first with the time we begin contact with the consultant (example we call him at 1:30 PM). The second column is the end of the contact (we hang up at 2:45 PM). What formula do I enter into the third column to figure out how many hours and minutes are spent (in this case I would want it to say 1:15 or 1.25. Even 75 would be fine. ) How do I do this?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Calculating Elasped Time (2000)

    Times are stored as fractions of a day, so that when you enter 1:30 PM in a cell it actually stores 0.5625. (What it actually displays is a separate issue, depending on how the cell is formatted.(
    Consequently, to diplay the difference in hours, you would just need the formula
    <pre>=(B1-A1)*24</pre>

    It won't work if your times aren't as I described. If not, post back.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston, Massachusetts, USA
    Posts
    205
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Elasped Time (2000)

    Thanks. That did it. I was missing the whole *24 part. Thank you!!!!

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

    Re: Calculating Elasped Time (2000)

    If you want the answer displayed in hours and minutes, and the start time is in A1, and the end time is in B1, then use the formula:

    <pre>=B1-A1
    </pre>


    Then format the cell with the format:

    <pre>[h]:mm
    </pre>


    The brackets around the h are important if the total time might be more than 24 hours.

    If you want the time in hours and fractional part of an hour, then use the formula:

    <pre>=(B1-A1)*24
    </pre>


    and format the cell as a number with as many decimals as you need.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Elasped Time (2000)

    I am not having a problem calculating the elapsed time but cannot get it to add up correctly. For example, given the following:

    Date In Time In Date Out Time Out Hrs.W
    26-Nov 17:22 27-Nov 5:43 12:21
    27-Nov 17:20 28-Nov 5:47 12:27
    28-Nov 17:25 29-Nov 3:43 10:18
    29-Nov 17:30 30-Nov 6:00 12:30
    30-Nov 17:23 1-Dec 6:40 13:17
    1-Dec 17:31 2-Dec 5:55 12:24


    1:17 Total Hrs./Minutes

    Obviously the hours/minutes total should not be 1:17 but I cannot seem to convert the numbers so they add up properly. It must be late in the day and I must be awfully tired because this as I remember it was simple first year stuff but I do not use Excel a lot and am stumped. My file is attached if it helps and of course your help will as always be much appreciated.
    Attached Files Attached Files

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

    Re: Calculating Elasped Time (2000)

    You had two problems in that sheet. First, the formula to calculate hours should be 1-Start+Finish, not 24-Start+Finish. That 24 is adding an extra 23 days (552 hours) to each hour calculation. The 552 hours was not displaying because you were also using the incorrect format for the cells. The format needs to be [h]:mm (with the brackets around the h) to show elapsed time. The format you were using lops off all time over 23 hours 59 minutes. I have fixed this in the attached workbook.

    Your formula is still going to fail if the person clocks in and out in the same day. If you have that possibility, you will have to come up with an IF statement that uses two different formula based on the Clock In and Out days.

    On Sheet 2 of the attached workbook I have shown how I would do this. It is much simpler to use Excel Date/Time cells to put the date and time into a single cell. If you can do that, then the formula is MUCH simpler, and always works.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Elasped Time (2000)

    Thanks so much - this is exactly what I needed. The fact is I am going to give the lady who does the posting two separate sheets to use. One for people on the day shift who always arrive and leave on the same day, and the other the end result of what you just sent me. Since she is someone who is not very computer literate to begin with I need to make it as simple as possible for her to do these calculations based on the time cards without her making 12 hour mistakes as she did this week which we now owe the employee money for!

    Thanks again!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Calculating Elasped Time (2000)

    Here's yet one more twist on this topic. Our employer requires us to turn in time sheets that reflect our hours as "hours plus tenths of an hour" (e.g., 7.8 hours, or 7 hours 48 minutes). How can I format the results cell to convert 7:48 to 7.8?

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating Elasped Time (2000)

    Lucas, Excel keeps time as decimal fractions of a day, so if you multiply 7:48 by 24 and then format the result as a number with one decimal place, you will get 7.8. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Calculating Elasped Time (2000)

    Thanks a bunch! Just one more question, though. I'm trying to understand the logic at work here. How can I multiply 7 hours, 48 minutes by 24 and get 7.8? This one totally escapes me.

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Elasped Time (2000)

    7 hrs 48 mins = 0.325 of a day

    0.325 * 24 = 7.8 hrs

    You just need to change the number format to "Number" and set you decimals to suit your preference ;-)

    At least using Decimal hours makes it is a lot easier to do maths on!



    Peter

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Calculating Elasped Time (2000)

    Doh! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    More than one person has mentioned that Excel thinks in terms of fractions of the 24-hr day. So, when I multiply 24 times 7:48, it's not 24 X 7 hours and change. It's 24 X [the fraction of the day]. OK...the light came on! Thanks, Peter!!

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating Elasped Time (2000)

    Smoke and mirrors. I know, it boggles the brain. When you are as old as I am, you just don't think about it. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    OK, I'll try to explain:
    Enter 12 hours and 10 minutes in cell A1 as 12:10.
    This is just a little over one-half day.
    A1 now contains 12:10
    Select A1 and use Format | Cells | Number Tab. Change the format from Time to Number and press OK.
    Now you see 0.51
    This is how Excel keeps time internally -- as fractional days.
    Select B1 and type =A1*24
    Excel displays 12.16667, the answer you want
    Select A1 and change the format back to time.
    Cool! Now if I could just understand and explain array formulas! Have fun! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating Elasped Time (2000)

    >understand and explain array formulas

    .. especially considering that understanding and explaining are two very different things! I'm kind of starting to understand array formulas by visualising the arrays as columnar tables of booleans and values, but <img src=/w3timages/censored.gif alt=censored border=0> will freeze over before I can explain them!
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Elapsed Time (2000)

    Okay - I give up. Please see attached worksheet. I've got the hours worked per day down pat - how in the $*($*)QW$*) do I figure out how many hours worked over and above the regular 8-hour day so I can figure the OT pay per day?
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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