Avatar of ExpressMan1
ExpressMan1Flag for Canada 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
Microsoft Excel

Avatar of undefined
Last Comment
ExpressMan1

8/22/2022 - Mon
ButlerTechnology

The follow code should work -- I am assuming that the first row has headings.
Public Sub TimeStuff()
Dim EACC As Integer
EACC = 0
Range("EA1").Activate
Do
  EACC = EACC + 1
  ActiveCell.Offset(1, 0).Activate
Loop Until ActiveCell.Value = ""

Range("DZ2").FormulaR1C1 = "=TIME(INT(RC[1]/100),MOD(RC[1],100),0)"
Range("DZ2").AutoFill Destination:=Range("DZ2:DZ" & EACC)
Range("DZ2").Activate

End Sub

Open in new window


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

Tom
ASKER
ExpressMan1

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

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 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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
ExpressMan1

Excel 2007.  File attached
FedEx-9.xlsx
ASKER CERTIFIED SOLUTION
ButlerTechnology

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ExpressMan1

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
ASKER
ExpressMan1

Correction, all values are showing 10:18 AM
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ButlerTechnology

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
ASKER
ExpressMan1

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?
ASKER
ExpressMan1

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy