Formula or array to achieve this ...

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
I'm trying to reduce thew amount of data entry within a spreadsheet containing (elapsed journey) times recorded in minutes and seconds.

I wish to enter data without the need to use the 00:00:00 format - for example where a time is 1 hr 23 mins and 13 seconds I would like to enter 12313 (without the need for entering colons) and the spreadsheet to know this should be converted to 01:23:13

If it is not possible to place the a formula/array withing the same cell as the data input I'm happy to have the data entry in an adjacent column (e.g. Data entry clerk enters 12313 in cell A4 - time magically appears as 01:23:13 appears in B4).

Here are some more examples of what I need:

Typing digits as = appearance of ...

3 = 00:00:03
31 = 00:00:31
313 = 00:03:13
3131 = 00:31:31
33131 = 03:31:31
313131 = 31:31:31

All help gratefully received.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

deepheat101

Board Regular
Joined
Jul 26, 2006
Messages
138
If we're talking just about "looks", then create a Custom format for the cells in question...

Code:
00":"00":"00
Remember, this is for appearances only... if you need to perform any calculations using these cells then you would need either something else or split the cell into its individual components.
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
I have already applied custom formatting to the relevant cells.

My request relates to reducing the number of keystrokes required for entering large amounts of data.

I don't see how your answer relates to my request.
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60
To (hopefully) clarify:

I have aleady formatted my columns to show all elapsed times as

hh:mm:ss

BUT

for the purpose of data entry where an elapsed time is 1 second I want to be able to type '1' instead of '00:00:01' and the worksheet to convert this into 00:00:01.

At the moment when I type '1' this would be converted into 00:00:00. This is not what I want.

ADDITIONALLY where an elapsed time is 1 hr 13 mins 32 seconds I want to be able to type (without the use of colons which take extra time/keystrokes) '11332' and the worksheet convert this to 01:13:32
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi

A bit chunky-clunky but try:

=(RIGHT(TEXT(A1,"000000"),2)/86400+0)+(MID(TEXT(A1,"000000"),3,2)/1440+0)+(LEFT(TEXT(A1,"000000"),2)/24)

Best regards

Richard
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi Shadowboxer

If you want to have the result in the same cell, you can use the change event of the worksheet.

This example changes the values you enter in column A to excel time.

Format the column as hh:mm:ss.

Paste this code in the worksheet's module (right-click on the worksheet's tab and choose View Code)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Application.EnableEvents = False
    If IsNumeric(Target.Value) Then _
        Target.Value = (Target.Value Mod 100) / 86400 + ((Target.Value Mod 10000) \ 100) / 1440 _
                     + (Target.Value \ 10000) / 24
    Application.EnableEvents = True
End If
End Sub

Hope this helps
PGC
 

Shadowboxer

Board Regular
Joined
Aug 24, 2006
Messages
60

ADVERTISEMENT

Hi pgc01,

That's brilliant - very impressive - this code works a treat in column 'A'.

Unfortunately I've never written code before and have struggled to figure out how to achieve the same effect in other columns.

For column B I tried writing a variation of your code changing the bit which says 'column 1' to 'column 2' (hoping rather than believing this would work). Since this failed I've become unable to fathom which part of your code tells the worksheet 'Do this in Column A') and therefore what I should change to achieve the same effect across a range of different columns.

If you could guide me I would appreciate this.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi again

I'm glad it helped. Now for the other columns...

Each time you change the worksheet, the Change Event Sub runs. It gives you the parameter Target, which is the range that was changed. This is how you can decide whether you want to perform an action or not.

In the code I posted I used the condition

If Target.Column = 1 then

meaning that the action is performed if the column is A (first column). For column B it should be enough to change 1 to 2.

If Target.Column = 2 then

If you want columns A and B use

If Target.Column = 1 Or Target.Column = 2 then

If you want more columns there is another alternative. For columns A:F use

If Not Intersect(Target, Columns("A:F")) Is Nothing Then

Hope this helps
PGC
 

Watch MrExcel Video

Forum statistics

Threads
1,114,095
Messages
5,545,923
Members
410,713
Latest member
TaremyLunsil
Top