Formula or array to achieve this ...

[Shadowboxer]

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.

 

[deepheat101]

If we're talking just about "looks", then create a Custom format for the cells in question...

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]

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]

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]

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]

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)

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]

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]

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

 

[Shadowboxer]

Works like a dream - thanks for all your help!