date conversion from m/d/yyyy to yyyymmdd

J

jeff

I have a cell with a custom date format of m/d/yyyy h:mm,
I need to convert the date to yyyymmdd text. Does anyone
have a VB macro code snippet to do something like this?
I've tried text to columns but can't get it to work...any
help is appreciated.

Thanks.
 
P

Patrick Molloy

You aren't clear. Does the cell CONTAIN as date or is it
just the format that you want to change?
Try changing the format to General. If its a 'date' you
will see the cell value as a number like 36752. If you
still see a text date, then the cell is text.
You could convert to a date using the DateValue()
function.
Now all you need to do is formatthe cell yyyymmdd

if you need the text in a cell
=TEXT(DateValue(A1),"YYYYMMDD")
where A1 is your text date

HTh
Patrick Molloy
Microsoft Excel MVP
 
D

Dave Peterson

See a couple of replies in .misc.

Jeff said:
I'll try to be more specific.

I want to use the functionality of excel/vb to change the actual current
value of a cell, for example, 7/9/2003 9:41 to "20030709". The current
format of the cell is custom m/d/yyyy h:mm. I need the actual value of
the cell to be 20030709, not a formula or the excel date/time serial
number. I am uploading this file to a Physical File on an AS/400. It's
easy enough for me to manipulate the field once I get the data to the
AS/400, I just would like to try to format the data correctly in the
spreadsheet before uploading...just looking to see what I can/cannot do
in excel/vb.

I've used a for/next loop in a macro to change date cells to text using
text to columns, now I want to do the same but change the date format to
YYYYMMDD also. The end result of the cell must be actual data, not a
formula.

Here is what I've used in the past:

Dim CurCell As Object
Columns("H:H").Select
For Each CurCell In Selection
If CurCell.Value <> "" Then CurCell.TextToColumns Destination:=CurCell,
DataType:=xlFixedWidth, _
OtherChar:="/", FieldInfo:=Array(0, 2)
Next

Once again, any help is appreciated.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel date format YYYYMMDD 1
m/d/yyyy h:mm date conversion to yyyymmdd ??? 2
How to convert mm/dd/yyyy date format to yyyymmdd 3
Cell Format Function "NumberFormatLocal" in VBA 1
Different Date formats in text to be recognised as date value 8
Date Conversion 1
how do I change the date format from yyyymmdd to mm/dd/yyyy 4
check date format in regional setting 2

Top