Results 1 to 15 of 18
Thread: Calculating Elasped Time (2000)

20011106, 18:13 #1
 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?

20011106, 18:38 #2
 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>=(B1A1)*24</pre>
It won't work if your times aren't as I described. If not, post back.

20011106, 18:49 #3
 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!!!!

20011106, 21:53 #4
 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>=B1A1
</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>=(B1A1)*24
</pre>
and format the cell as a number with as many decimals as you need.Legare Coleman

20011205, 01:43 #5
 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
26Nov 17:22 27Nov 5:43 12:21
27Nov 17:20 28Nov 5:47 12:27
28Nov 17:25 29Nov 3:43 10:18
29Nov 17:30 30Nov 6:00 12:30
30Nov 17:23 1Dec 6:40 13:17
1Dec 17:31 2Dec 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.

20011205, 03:02 #6
 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 1Start+Finish, not 24Start+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.Legare Coleman

20011205, 03:18 #7
 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!

20020213, 14:12 #8
 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?

20020213, 14:26 #9
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
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>

20020213, 15:09 #10
 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.

20020213, 15:31 #11
 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

20020213, 16:52 #12
 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 24hr 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!!

20020213, 18:21 #13
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
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 onehalf 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>

20020213, 19:44 #14
 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

20020322, 20:45 #15
 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 8hour day so I can figure the OT pay per day?