Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
Hi all,
I have a date currently as text format "2000/Jan" and so forth. I wish to convert this to a date format as "Jan-2000" is there a way to accomplish this easily with a macro? I don't have much experience programming so any help would be appreciated. I have a huge dataset that this needs to be done for thus it would optimal to have a macro solution. Cheers, N |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
Well, it won't convert it to a date value, but it'll change it to read the
way you want: =right(a1,3)&"-"&left(a1,4) Copy down. A macro would likely take you a lot longer to get. ************ Anne Troy VBA Project Manager www.OfficeArticles.com "Nise" wrote in message oups.com... Hi all, I have a date currently as text format "2000/Jan" and so forth. I wish to convert this to a date format as "Jan-2000" is there a way to accomplish this easily with a macro? I don't have much experience programming so any help would be appreciated. I have a huge dataset that this needs to be done for thus it would optimal to have a macro solution. Cheers, N |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
Thanks for your quick response. That is how I've been going at it. Any
way to have the program copy all of the cells in that column transfer them to another sheet, apply the formula and copy back to the original with the updated format? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
For example, I have this much written thus far:
Sub ChangeToDate() Dim wks As Worksheet Dim col As Long Set wks = ActiveSheet With wks col = ActiveCell.Column .UsedRange = .Range(.Cells(2, col), .Cells(2000, col)) For Each cell In wks.UsedRange If cell.????? Then //NEED code here End If Next End With End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
Maybe something like:
Option Explicit Sub ChangeToDate() Dim wks As Worksheet Dim col As Long Dim myRng As Range Dim myCell As Range Dim myDate As Date Set wks = ActiveSheet With wks col = ActiveCell.Column Set myRng = .Range(.Cells(2, col), .Cells(.Rows.Count, col).End(xlUp)) End With For Each myCell In myRng.Cells With myCell '2005/Jan If Len(.Value) = 8 Then On Error Resume Next myDate _ = CDate(Mid(myCell.Value, 6) & " 1, " & Left(myCell.Value, 4)) If Err.Number < 0 Then 'not a date MsgBox "Error with: " & myCell.Address(0, 0) Err.Clear Else .NumberFormat = "mmm-yyyy" .Value = myDate End If On Error GoTo 0 End If End With Next myCell End Sub Nise wrote: Hi all, I have a date currently as text format "2000/Jan" and so forth. I wish to convert this to a date format as "Jan-2000" is there a way to accomplish this easily with a macro? I don't have much experience programming so any help would be appreciated. I have a huge dataset that this needs to be done for thus it would optimal to have a macro solution. Cheers, N -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
Thank you so very much. You are a lifesaver/timesaver. I would give you
1 million points if I could. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date from text to Date format
Nise
Worked for me this way....... DataText to ColumnsNextNextColumn Data FormatDateYMDFinish Format to mmm-yyyy if necessary. Gord Dibben Excel MVP On 17 Nov 2005 08:54:07 -0800, "Nise" wrote: Hi all, I have a date currently as text format "2000/Jan" and so forth. I wish to convert this to a date format as "Jan-2000" is there a way to accomplish this easily with a macro? I don't have much experience programming so any help would be appreciated. I have a huge dataset that this needs to be done for thus it would optimal to have a macro solution. Cheers, N |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
MS Query Date Format | Excel Discussion (Misc queries) | |||
DATE Format | Excel Worksheet Functions | |||
Conditonal Format with a date format | Excel Discussion (Misc queries) |