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?
 
captainConnect With a Mentor Commented:
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.