EXCEL VBA problem, dates not being formatted.

[thehead]

Hi, I am using the following code but it just copies the date out as a real number, not as a date. When I double click on the cell the number becomes a date... how do I make into a date automatically?

AGE_COLUMN is a constant with the correct column number (8).

dteInstallDate = "" & recAccess!Install_Date
intDate = DateDiff("m", dteInstallDate, Now()) \ 12
ActiveSheet.Cells(intRowCount, AGE_COLUMN) = intDate
Range("I" & intRowCount).Select
Selection.NumberFormat = "m/d/yy h:mm"
Webmaster of

http://www.netepic.org

The EPICentre and owner of Minatures Online. (10% off all VOID goods!)

 

[xlbo]

Well, you are slecting on Col I to format. Column I is the NINTH column. BUT - you are putting your "date" into the EIGHTH column. therefore, you are not formatting your date but the next cell over (in col I) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[thehead]

I just double checked and column I is the eighth column, the formatting is already in the column in the template as well, it was more deperation the additional formating code.

But whilst it does change the format to date, it doesn't update the cell... which is annoying as it only updates the cell when you double click on it (as if to edit the contents) and then click out. Webmaster of

http://www.netepic.org

The EPICentre and owner of Minatures Online. (10% off all VOID goods!)

 

[bryanbayfield]

Column I is the eigth column - one of your columns is missing then matey! Presumably it's been hidden.

You'll need to correctly reference the cell as being in the ninth column.

Personally speaking I always use the

Format([i]<variable_name>[/i], &quot;dd/mm/yy&quot;)

function for this sort of problem. Excel, VBA and dates together can cause something of a nightmare, but I've found this function quite helpful in getting round the problems.

 

[xlbo]

ABCDEFGHI
123456789
I is the NINTH column - doesn't matter whether you have a hidden column or anything - you are still formatting the wrong column

Try this
ActiveSheet.Cells(intRowCount, AGE_COLUMN) = format(1*intDate,"m/d/yy h:mm&quot
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

thehead

are you sure you've not got a hidden column in your sheet? Excel counts like this even if there is a hidden column: A=1
B=2, ...,H=8, I=9
And I is definitely the ninth column

Couldn't you use the column index number rather than specifying the column *letter*: change

Range("I" & intRowCount).Select

to

Cells(intRowCount, AGE_COLUMN)

That way you're sure you're filling & formatting the same column

Cheers
Nikki