mainelytrusses
asked on
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
Truss-Fabrication-Time-Study.xlsm
ASKER
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.
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.
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.
ASKER
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?
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.
Maybe there is a workaround but I cannot see it right now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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.
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.
ASKER
Thanks so much!
No problem. :)
http://1drv.ms/1BwOX3Z