Solved: Need to convert date to three letter month

[coserria]

Hello,

I have a series of dates all in my first Colum A:A, need to convert all of this column to a three letter date such as JAN, FEB, MAR and so on. Code would be the last part of this code, that I got help with. I'm totally lost as to how to make this end. Input date format is

17/11/2007 12:44:02

all I need is

NOV

Can any one show me how this would convert; I am having a lot of trouble finding where to get this. I used to have an insert function =text(a1,"MMM") that I would copy in an inserted column but now it will not give the correct out put. Formatting is not the issue and a bit of VB code would work better
[VBA]
On Error GoTo Exits:
For Each ws In Worksheets
With ws
.Activate
.Cells.MergeCells = False
.Cells.WrapText = False
.Range("A:B,D:E,K:U").Delete Shift:=xlToLeft
'Find and delete terms
For Each a In arr
.Cells.Replace What:=a, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next a
'rows and colums here down
Set rng = Range(ws.Columns(1), Columns(ws.Cells.SpecialCells(xlCellTypeLastCell).Column()))
For Col = rng.Columns.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Columns(Col).EntireColumn) = 0 Then
rng.Columns(Col).EntireColumn.Delete
End If
Next Col
Set rng = Range(ws.Rows(1), Rows(ws.Cells.SpecialCells(xlCellTypeLastCell).Row()))
For Rw = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(Rw).EntireRow) = 0 Then
rng.Rows(Rw).EntireRow.Delete
End If
Next Rw
'insert cell for alignment
.Range("B1,D1").Insert Shift:=xlDown
Set rng = Range(ws.Rows(1), Rows(ws.Cells.SpecialCells(xlCellTypeLastCell).Row()))
For Rw = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(Rw).EntireRow) = 0 Then
rng.Rows(Rw).EntireRow.Delete
End If
Next Rw
'.Range("A:A").Formatdatetime("dd/mm/YYYY hh:mm:ss", convert"MMM")
.Cells.Interior.ColorIndex = xlNone
.Cells.EntireColumn.AutoFit
.Cells.EntireRow.AutoFit
End With
Next ws
Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
[/VBA]

[Simon Lloyd]

use:
[VBA]
.Columns("A:A").NumberFormat = "mmm"
[/VBA]instead of:
[VBA]
.Range("A:A").Formatdatetime("dd/mm/YYYY hh:mm:ss", convert"MMM")

[/VBA]

[coserria]

.Columns("A:A").NumberFormat = "mmm"

same output nothing changed it came back as

13/11/2007 14:12:50

[perhol]

Replace

[vba] Next Rw
'.Range("A:A").Formatdatetime("dd/mm/YYYY hh:mm:ss", convert"MMM")[/vba]
[vba] Columns("A:A").Select
Selection.NumberFormat = "mmm"[/vba]

[coserria]

I have tried a few varients

Columns("A:A").Select
Selection.NumberFormat = "mmm"

Columns("A:A").Select
Selection.FormatDateTime = "mmm"

Columns("A:A").Select
Selection.FormatDate = "mmm"

I think it is not reqonizing the input and being able to calulate a output. but i'm not sure how to tell it what the input is.

[Simon Lloyd]

It wont do anything if you are showing the date as a text, it would need to be in proper date format!, looking at your code above you are not "inputting" anything. I tried some variations and they worked for me you will probably have to post a test workbook with your problem in!

[Bob Phillips]

Try this

[vba]
With .Columns(1)

.TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
Tab:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
.NumberFormat = "mmm"
End With
[/vba]

[coserria]

Attached main macro into the test workbook, Not TONS of data. The date ends up in Column A. The date is a month day year format. I have not been able to get Excel to recognize it and allow me to reformat it. All I need is to get the month out of it so that I can use it in a pivot table.

[Simon Lloyd]

You need to trim all your dates down so just before

[VBA]
Next Rw
'.Range("A:A").Formatdatetime("dd/mm/YYYY hh:mm:ss", convert"MMM")
[/VBA]enter this:
[VBA]

Dim dCell As Range
For Each dCell In Range("A:A")
If IsDate(dcell) Then
dcell.Value = Int(dcell.Value)
End If
Next dCell

[/VBA]

[coserria]

[VBA]
Dim i As Long
'rest of the code was in here

For i = 1 To 2000
Cells(i, 1).Value = Format(Cells(i, 1).Value, "mmm")
Next i

end sub
[/VBA]
Ended up with this.

Thank all for the help.

[coserria]

I was having trouble getting the format correct. The last set up worked and I'm sure that many others will too.

The date that I was working with is coming from a database program, MAXIMO. This is a French company I think.

Anyway the data was set like this 23/04/2007 08:12:44

so i noticed by using the

.columns ("A:A") = format ("MMM")

I was getting the desired result but it iwas incorrect as it used the first set of number it knows.

I changed my language date format for the computer and now get the correct format.