Format cells 'Custom m/d/yyyy' in excel VBA

[Wulluby]

I have a need to format a number of cells with custom format "m/d/yyyy".

If I do this manually it comes out correct and uploads correctly. I recorded the making of this change into a macro but when I play it back that line does not seem to make the change.

 Selection.NumberFormat = "m/d/yyyy"

I have to go back after the rest of the macro has done it's thing and format these cells. Any suggestions would be greatly appreciated.

Thanks.

 

[vijay.vizzu]

I have created small macro... i hope it will help you.

Sub CellFormat()
Dim cell As Range
Dim rng As Range

Set rng = Application.InputBox("Select Range", "Range selection", , , , , , 8)

For Each cell In rng
cell.NumberFormat = "m/d/yyyy"
Next

End Sub

 

[Abhijeet R. Joshi]

Wulluby,

Have you mentioned a specific selection before the formatting?
would it be possible for you to mention the whole code?

 

[Wulluby]

Thanks for the responses, that first piece of code still does not change the format, the format remains in date format *14/03/2001.

The data is downloaded from one system and uploaded to another after changing a couple of things such as headers. The contents of the columns are dates, as I am in Europe these come around back to front so I have to use text to columns to split off the timestamps so that I can then format them to the correct date. A process I am trying to automate.

An example of the code:

Dim r As Long
r = ActiveSheet.UsedRange.Rows.Count


Range("B1").Select
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Insert
Range("B2:B" & r).Select
    Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(0, 4), Array(10, 1), Array(24, 1)), TrailingMinusNumbers:=True
 Selection.NumberFormat = "m/d/yyyy"
Range("B1").Select
ActiveCell.EntireColumn.Offset(0, 1).Delete
ActiveCell.EntireColumn.Offset(0, 1).Delete

 

[Hui]

Try using

Dim r As Long
r = ActiveSheet.UsedRange.Rows.Count


Range("c:d").Insert

Range("B2:B" & r).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
  Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
  :=Array(Array(0, 4), Array(10, 1), Array(24, 1)), TrailingMinusNumbers:=True
Range("B2:B" & r).NumberFormat = "m/d/yyyy"
Range("c:d").Delete

 

[Wulluby]

Looks a lot tidier, thanks Hui. Still not getting it into that custom format though so thinking it must be a regional thing at my end.

Straight from the export, column B looks to be formatted: Custom - dd/mm/yyyy hh:mm
Unless the middle digits or what it thinks is 'mm' is above 12 in which case the format is General.

Once I have stepped through the text to columns part of the macro the custom dates remain in custom format.
The dates originally interpreted as General are then Date *14/03/2001.

Then when stepping through the line Range("B2:B" & r).NumberFormat = "m/d/yyyy" all formats change to Date *14/03/2001 right down the column.

I can only seem to get it to "m/d/yyyy" by applying manually.

 

[Hui]

Are your dates actually numbers or text ?
Select a cell with a date and click the , icon
Does it display a number like 40000?

 

[Wulluby]

To start with
A cell containing - 10/09/2013 15:42 appears as 41,527.65
A cell containing - 10/15/13 09:16:17 appears as 10/15/13 09:16:17

After the macro is run then all come up with the long number.

Just to add some background to what I am trying to do. When colleagues in other regions pick up this report they just manually change the format to "m/d/yyyy", change headers and upload. For myself and others in Europe, if we do that we end up with the months and days all reversed so we have to split out the timestamps and in Convert Text to columns Wizard step 3 select Column Data format as DATE and MDY which then allows us to manually apply a date format without getting those the wrong way round.

We can still manually apply m/d/yyyy after this macro is run, just can't get my head round why when played back that line NumberFormat = "m/d/yyyy" doesn't do the same as when recorded.

What's interesting though is if I use a regular date format:
Range("B2:B" & r).NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
Then it makes changes and the dates although not the format needed for uploading do have the days and months correct, seems to be something to do with it being custom format. I'm guessing this is something to do with timezone default formats.