Calculating Elasped Time (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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

=(B1-A1)*24

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

Reply | Quote

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

Reply | Quote

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:

=B1-A1

Then format the cell with the format:

[h]:mm

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:

=(B1-A1)*24

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Doh!

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!!

Reply | Quote

Smoke and mirrors. I know, it boggles the brain. When you are as old as I am, you just don’t think about it.

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

Reply | Quote

>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 will freeze over before I can explain them!

Reply | Quote

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?

Reply | Quote

check the rehours cell…
write only Hours and not Dates

(File is attached)

Reply | Quote

Great quote.

Perfect – I have a question though. Why wouldn’t the simpler formula of the hours worked minus the regular hours work? Now I can’t seem to write the other formula I need – the OT hours times the $15.00 OT rate.

Thank you so much – via con Dios

aunt Linda

Reply | Quote

Duh – so the negative number won’t appear as #### – sorry. Again – great and apropos quote.

Reply | Quote