asked on

Add time reference

Hi Experts,

I need Expert help. How add-in automatically data at Column A with “:” and “.” E.g 06451800 become 06:45:18.00. Hope Experts could help.

Time.xls

andrewssd3

Do you want the result to be a real Excel time value, or just a string with the : and . added?

andrewssd3

If so, this formula in Column B will do it:

=TIME(MID(A4,LEN(A4)-7,2),MID(A4,LEN(A4)-5,2),MID(A4,LEN(A4)-3,4)/100)

Open in new window

It does not work with your first value, which you have converted to a number.

SOLUTION

wchh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wchh

Or for A2, length = 7,
=IF(LEN(A2)=8,LEFT(A2,2) &":"&MID(A2,3,2)&":"&MID(A2,5,2)&"."&MID(A2,7,2),"0" &LEFT(A2,1) &":"&MID(A2,2,2)&":"&MID(A2,4,2)&"."&MID(A2,6,2))

Cartillo

ASKER

Hi,

Is that possible to create a conditional formatting at column A?

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cartillo

ASKER

Hi Barry,

Sorry to complicate the whole think. Can you help to put this via VBA? When I import the actual data this column will be automatically added with “:” and ‘:”. Hope you will consider this.

barry houdini

VBA isn't my area of expertise - I'll see if anybody else can help you out......

barry

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cartillo

ASKER

Hi andrewssd3,

Cool! thanks a lot for the help.