date conversion from m/d/yyyy to yyyymmdd
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. |
date conversion from m/d/yyyy to yyyymmdd
Jeff,
See response in misc group -- HTH Bob Phillips "jeff" wrote in message ... 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. |
date conversion from m/d/yyyy to yyyymmdd
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 -----Original Message----- 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. . |
date conversion from m/d/yyyy to yyyymmdd
See a couple of replies in .misc.
Jeff Wysocki wrote: 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. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Dave Peterson |
All times are GMT +1. The time now is 12:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com