Cell format

bill_reinwald

New Member
Joined
Sep 19, 2005
Messages
22
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
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

Board Regular
Joined
Dec 16, 2003
Messages
127
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

New Member
Joined
Sep 19, 2005
Messages
22
Thanks for the quick responses!

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


bill
 

Watch MrExcel Video

Forum statistics

Threads
1,118,070
Messages
5,570,018
Members
412,304
Latest member
citrus
Top