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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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
 

Forum statistics

Threads
1,171,429
Messages
5,875,491
Members
433,131
Latest member
ThatOneDude

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top