Help with entering Time Data

[eschiesser]

Hi all. I need to enter elapsed time data manually into an excel spreadsheet. For example, on paper, I'm looking at this:

125:27:32

I would like to be able to enter this as follows:

1252732

Without colons, to save time. I've searched for many solutions before actually asking, and most of them are not meant for hours exceeding 24 or for hours exceeding two digits. I also need this to be time formatted, in order to do calculations within excel.
I have fiddled around with the custom formats and managed to visually add the colons, but this doesn't do much in the way of calculations. It also doesn't actually add colons to the entry, so the colons aren't really there.

Is any of this possible? Is there something outside of excel that can assist me? Any and all help welcome!

 

[StuLux]

Hi - welcome to Mr Excel:

This might help??

In 3 "spare" columns enter the formulas (assumes your number is entered in cell A2):
=MID(A2,1,LEN(A2)-4) - to give you hours
=MID(A2,LEN(A2)-3,2) - to give you minutes
=MID(A2,LEN(A2)-1,2) - to give you seconds

You then might be able to use other formulas to manipulate your time as needed? I can't see any way to get Excel to show time in excess of 24 hours without it becoming a date.

 

[pgc01]

Hi eschiesser
Welcome to the board

I see 2 solutions:

1 - a formula based one

You enter the times in one column as a number and use a formula in another column to convert the number values to time values

2 - use vba to convert the times

You can use for example the worksheet change event and change the value in the cell directly.


An example for option 1.

In B2:

=TEXT(A2,"0\:00\:00")+0

Select column B and format it with the string:

[h]:mm:ss

<TABLE style="BORDER-TOP-WIDTH: 2px; BORDER-LEFT-WIDTH: 2px; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BACKGROUND: #fff; BORDER-BOTTOM-WIDTH: 2px; BORDER-BOTTOM-COLOR: #cccccc; BORDER-TOP-COLOR: #cccccc; FONT-FAMILY: Arial,Arial; BORDER-COLLAPSE: collapse; BORDER-RIGHT-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc" cellPadding=1 border=1><TBODY><TR><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">A</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">B</TH><TH style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; BORDER-TOP-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888" width=30>C</TH></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">1</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">2</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">1252732</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">125:27:32</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">3</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">123</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">0:01:23</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">4</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">12345</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888">1:23:45</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BACKGROUND: #9cf; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #000000; PADDING-TOP: 0.4em; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000">5</TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD><TD style="BORDER-TOP-WIDTH: 1px; PADDING-RIGHT: 0.5em; PADDING-LEFT: 0.5em; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #888888; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #888888; PADDING-BOTTOM: 0.25em; BORDER-TOP-COLOR: #888888; PADDING-TOP: 0.4em; TEXT-ALIGN: right; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888"> </TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=4>[Book1]Sheet2</TD></TR></TBODY></TABLE>

 

[eschiesser]

Wow thank you both for your quick responses. I think Pgc01's first idea will work best for me, seeing as I am not versed in writing code or using worksheet change events. I've tried your method and it works perfectly. Thanks so much! This will save tons of time and hassle.