http://1drv.ms/1BwOX3Z

Solved

Posted on 2014-08-18

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

10 Comments

format everything as time and not just numbers

http://1drv.ms/1BwOX3Z

http://1drv.ms/1BwOX3Z

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.

Maybe there is a workaround but I cannot see it right now.

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

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.

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

prepare AD for 2012 R2 | 6 | 68 | |

Turnoff the screen backlight without letting the device to go into sleep in Windows 8 | 12 | 111 | |

Windows 10's blank icons (drag to desktop) | 6 | 116 | |

URL for downloading Google Chrome for Win XP | 2 | 44 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**14** Experts available now in Live!