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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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,885
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,885
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
 

Forum statistics

Threads
1,144,278
Messages
5,723,464
Members
422,498
Latest member
KAT112014

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top