Cell format

[bill_reinwald]

Hi, I have a range of cells that look like this:
12/08/2005 0015

I need to insert a colon ) in between the hour and minute, so that another piece of software will read it as a date. When I do this manually in Excel, its no problem, Excel even seems to automatically reformat the cell, so that when you double click on it, it shows:

12/8/2005 12:15:00 AM

The problem I'm having is that when I use a macro to update this range, Excel doesn't reformat the cell, it just seems to stay as a string format. I've tried using cell->format->date but that doesn't do it. Strangely, if I double click on these cells, then unselect them, they do change to what I want. But of course, I don't wanna double click on a whole range of cells.

Any suggestions? Here's the part of the macro that inserts the colon, I'm wondering if I have to add some formating step also?

For Row = 122 To 2 Step -1
If Cells(Row, 3).Value = "" Then Rows(Row).Delete _
Else: Cells(Row, 8) = "=REPLACE(RC[-5],14,,"":"")"
Next Row


Thanks,

bill

 

[Fat Cat]

You could try using the format command in your macro when sending data to the sheet;

eg: Cells(10, 2).Value = Format(Now(), "dd/mm/yyyy hh:mm:ss")

 

[farnuckl]

Hello, here's an alternate suggestion:

You could also make your formula this:

"=VALUE(REPLACE(RC[-5],14,,"":""))"

Then formating the cell with the date format you want works.

-farnuckl

 

[bill_reinwald]

Thanks for the quick responses!

farnuckl, that little "Value" addition to the line works perfectly!


bill