ExpressMan1

asked on

# Format column and fill with time

Hello,

I have a column "EA" containing delivery times, as general format, example:

1018

1001

943

I need to show these time in column "DZ" in the time format,

10:18 AM

10:01 AM

9:43 AM

I know the formula is =TIME(INT(EA2/100),MOD(EA2,100),0)

How can use vba to insert this formula in DZ column and fill down to length of EA column?

Thank You

I have a column "EA" containing delivery times, as general format, example:

1018

1001

943

I need to show these time in column "DZ" in the time format,

10:18 AM

10:01 AM

9:43 AM

I know the formula is =TIME(INT(EA2/100),MOD(EA2

How can use vba to insert this formula in DZ column and fill down to length of EA column?

Thank You

ASKER

After I run this the range of cells in "DZ" column are all showing just "10"

Very weird -- I just tried it on my system with success. What version of Excel are you using? What is the format for those target cells. They start off as general on mine and are switched to custom

Tom

**h:mm AM/PM**Any change that you could post the spreadsheet? Just having the DZ and EA columns would work for testing. I find that when I work with made up data that everything works until I get the real data.Tom

ASKER

Excel 2007. File attached

FedEx-9.xlsx

FedEx-9.xlsx

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

That did seem to help but now all the results in column DZ show 10:18 which is the value of EA2 (1018),

But not taking the adjacent time values in column EA,

1018

1001

943

etc

But not taking the adjacent time values in column EA,

1018

1001

943

etc

ASKER

Correction, all values are showing 10:18 AM

It sounds like it is doing a copy instead of an auto fill of it is using absolute reference.

What is the formula in cell DZ3 after you run the macro?

Tom

What is the formula in cell DZ3 after you run the macro?

Tom

ASKER

Thanks very much for your help.

I just saved the sheet and the values updated correctly. All the formulas were showing correctly in the DZ cells but only updated when saved. I imagine there is an simple line of code to save the sheet?

I just saved the sheet and the values updated correctly. All the formulas were showing correctly in the DZ cells but only updated when saved. I imagine there is an simple line of code to save the sheet?

ASKER

Formula in DZ3 was correct =TIME(INT(EA3/100),MOD(EA3,100),0)

I added this line to the end of your code and it updated correctly,

Columns ("DZ") .Formula = Columns ("DZ") .Formula

I added this line to the end of your code and it updated correctly,

Columns ("DZ") .Formula = Columns ("DZ") .Formula

Open in new window

There is probably a more elegant way to determine number of rows.

Tom