# Help with entering Time Data

#### eschiesser

##### New Member
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!

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### StuLux

##### Well-known Member
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

##### MrExcel MVP
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

#### eschiesser

##### New Member
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.

Replies
3
Views
41
Replies
31
Views
768
Replies
1
Views
78
Replies
2
Views
81
Replies
4
Views
785

1,133,271
Messages
5,657,764
Members
418,411
Latest member
Excellency

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back