Format dates and not Zeros

G

Guest

I need some code to:
I have a column (F) that contains dates and zeros. How
can I change the format to date (YYYYMMDD) and leave the
zeros as zeros and not get the 19000100 date?

0
6/7/2004
7/19/2004
0
0
0
0
0
1/31/2005
3/14/2005
9/13/2004
10/18/2004
1/31/2005
7/12/2004
 
R

Ron Rosenfeld

I need some code to:
I have a column (F) that contains dates and zeros. How
can I change the format to date (YYYYMMDD) and leave the
zeros as zeros and not get the 19000100 date?

0
6/7/2004
7/19/2004
0
0
0
0
0
1/31/2005
3/14/2005
9/13/2004
10/18/2004
1/31/2005
7/12/2004
Click to expand...


Format/Cells/Number
Category: Custom
Type: [=0]0;yyyymmdd


--ron
 
S

SidBord

Can you use something like:
Dim N As Integer, Limit As Integer
Limit=(whatever)
For N=1 to Limit
If Range("A" & N).Value <> 0 _
Then Format(Range("A" & N).Value,"YYYMMDD")
End For
 
R

Ron Rosenfeld

I need some code to:
I have a column (F) that contains dates and zeros. How
can I change the format to date (YYYYMMDD) and leave the
zeros as zeros and not get the 19000100 date?

0
6/7/2004
7/19/2004
0
0
0
0
0
1/31/2005
3/14/2005
9/13/2004
10/18/2004
1/31/2005
7/12/2004
Click to expand...


Format/Cells/Number
Category: Custom
Type: [=0]0;yyyymmdd


--ron
Click to expand...

I missed that you wanted VBA code to do this:

Range("F:F").NumberFormat = "[=0]0;yyyymmdd"



--ron
 
Top