correct functions in a 2010 excel sheet to calculate how many (hours) minutes and seconds have elapsed

I am entering a start time and an end time for hours minutes and seconds, formatted Custom 0|:00|:00, so that when I simply type 75200, it displays as 07:52:00.  These are columns A and B.  Columns C and D are supposed to convert these times to a number so that Column E will display the total hours, minutes, and seconds elapsed on each row.  It appears to work, as you can see on the attached (see formulas used on Sheet 1 and results on Sheet 2), but it does not calculate correctly when I go from the hour of 7:58 to the hour of 8:01, and it also does not calculate correctly when I go over an hour or time elapsed, as shown from 8:14 to 9:18.  The problem may stem from incorrect functions in Columns C and D, or it may be a problem with the formatting of Column A, or a combination of issues.  I can't see why the formulas work some times, but not always.
Truss-Fabrication-Time-Study.xlsm
mainelytrussesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
format everything as time and not just numbers
http://1drv.ms/1BwOX3Z
0
mainelytrussesAuthor Commented:
The initial requirement of my assignment was to format columns A and B so that you entered only 70506 and have it appear at 7:05:06, my boss does not want to have to type in the colons, he wants it automatic.    Is there a way to convert these columns to a format of time?  I can't see your formatting on the attached sheet.
0
captainCommented:
There is no way of simply entering a whole number and using a function that converts this into time using the same digits as entered.

This is simply due to the time value (as non decimal) being represented in the colon format whilst its decimal value is entirely different.

For example:
If you enter 07:58 in a time field and copy out the value alone it results in 0.331944444444444

You can of course use your format builder but then Excel will see the digits as decimal number and not calculating time.

I think unless your boss knows all decimal equivalents for the times of the day from memory it will be simpler to enter the colon by hand. :)

hth
capt.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

mainelytrussesAuthor Commented:
If you look at the attached sheet, you'll see that on Sheet 3, the calculations are correct in Column E in all rows except rows 4 and 8.  Coincidently, or not, this is where the hour changes.  Can you see a work around for this issue?
0
captainCommented:
that is what I meant looking at your numbers it works when the rules of the decimal system apply, but an hour has got 60 minutes not 100 so your value in D4 is lower than C4 even though it should be higher as it is later. Conversely the same in D8 hence when you subtract these as per the logic in the cells were it is analog to decimal calculations you get a minus result.

Maybe there is a workaround but I cannot see it right now.
0
captainCommented:
OK here is your workaround.

You need to break down each of the digits into single digits and then recompile into the time format. The benefit of this is that you can save a lot of formulas as a simple CELLREF1 minus CELLREF2 will calculate it for you without the need for TRUNC and MOD

You need LEFT, MID and my example here shows the formula to do a test for LEN (5 digit times, obviously after 10am it will be 6 digit) and an =if test to determine which characters to use to build the time.

Then you combine the characters again with =TIME() and subtract one from another just make sure cell format is mm:ss

I have illustrated that very crudely on sheet 3 in the yellow framed area, this will be the whole function you need, check the formula for each of the cells and then how it is combined and calculated.

hth
capt.
Copy-of-Truss-Fabrication-Time-Study.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mainelytrussesAuthor Commented:
I question how this works when you change from 9 to 10, (from 5 digits to 6), yet the formula remains LEN 5.  It does work, but I will continue testing in a few hours.  Thank you!
0
captainCommented:
Thanks for the accept. :)

I guess you can make this as bullet proof as you want. At the moment it relies on the fact that the input is either 5 or 6 digits entering less or more will simply break the formula. However, you can obviously add validation to the input to make sure it is only either 5 or 6, and add conditional formatting to highligh cells that have less than 5 or more than 6.

Happy to help you with this, but I thought you may want to take it from here

cheers
capt.
0
mainelytrussesAuthor Commented:
Thanks so much!
0
captainCommented:
No problem. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.