date formatting in excel VBA

[DaveJhelpexcel]

I'm trying to convert a text date - time to internal date format, I can do
this fine by copying the column and past special with add to another column
and pasting back to the first column.
Ive used record macro to create VBA code to do the same, but get american
date format 12/07/05 06:02 becomes 7/12/05 and 13/12/05 06:05 returns null.

The VBA code created by the macro recorder is as follows :-


Sub DateFormatter()
'
' DateFormatter Macro
' Macro recorded 12/7/2005 by Johnsd
'

'
Columns("G:G").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=2
Columns("N:N").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
ActiveWindow.SmallScroll ToRight:=-7
Columns("G:G").Select

ActiveSheet.Paste
Selection.NumberFormat = "d/m/yyyy h:mm"
End Sub
--
Can anyone help

Thanks in advance

DaveJ

[Norman Jones]

Hi Dave,

Try something like:

Sub tester01()
Dim Rng As Range

Set Rng = Range("G1:G20")

With Rng
.NumberFormat = "dd /mm/yyyy hh:mm"
.Value = .Value
End With

End Sub

---
Regards,
Norman



"DaveJhelpexcel" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to convert a text date - time to internal date format, I can do
> this fine by copying the column and past special with add to another
> column
> and pasting back to the first column.
> Ive used record macro to create VBA code to do the same, but get american
> date format 12/07/05 06:02 becomes 7/12/05 and 13/12/05 06:05 returns
> null.
>
> The VBA code created by the macro recorder is as follows :-
>
>
> Sub DateFormatter()
> '
> ' DateFormatter Macro
> ' Macro recorded 12/7/2005 by Johnsd
> '
>
> '
> Columns("G:G").Select
> Selection.Copy
> ActiveWindow.SmallScroll ToRight:=2
> Columns("N:N").Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd,
> SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Selection.Cut
> ActiveWindow.SmallScroll ToRight:=-7
> Columns("G:G").Select
>
> ActiveSheet.Paste
> Selection.NumberFormat = "d/m/yyyy h:mm"
> End Sub
> --
> Can anyone help
>
> Thanks in advance
>
> DaveJ

[Dave Peterson]

It sounds like your pc wants the date in one format and your data (as text) is
in a different order.

I'd insert a couple of helper columns to the right of G (where those date/times
are).

Then select your range (all of column G?) and do data|text to columns.

Choose the correct mdy or dmy for the date and general for the column.

Then you can either leave them in different columns (and delete the original) or
just add the values (in the original column or one more helper column) and
format the way you want.



DaveJhelpexcel wrote:
>
> I'm trying to convert a text date - time to internal date format, I can do
> this fine by copying the column and past special with add to another column
> and pasting back to the first column.
> Ive used record macro to create VBA code to do the same, but get american
> date format 12/07/05 06:02 becomes 7/12/05 and 13/12/05 06:05 returns null.
>
> The VBA code created by the macro recorder is as follows :-
>
> Sub DateFormatter()
> '
> ' DateFormatter Macro
> ' Macro recorded 12/7/2005 by Johnsd
> '
>
> '
> Columns("G:G").Select
> Selection.Copy
> ActiveWindow.SmallScroll ToRight:=2
> Columns("N:N").Select
> Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> Selection.Cut
> ActiveWindow.SmallScroll ToRight:=-7
> Columns("G:G").Select
>
> ActiveSheet.Paste
> Selection.NumberFormat = "d/m/yyyy h:mm"
> End Sub
> --
> Can anyone help
>
> Thanks in advance
>
> DaveJ

--

Dave Peterson

[DaveJhelpexcel]

--
Thanks in advance

DaveJ


"Norman Jones" wrote:

> Hi Dave,
>
> Try something like:
>
> Sub tester01()
> Dim Rng As Range
>
> Set Rng = Range("G1:G20")
>
> With Rng
> .NumberFormat = "dd /mm/yyyy hh:mm"
> .Value = .Value
> End With
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "DaveJhelpexcel" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to convert a text date - time to internal date format, I can do
> > this fine by copying the column and past special with add to another
> > column
> > and pasting back to the first column.
> > Ive used record macro to create VBA code to do the same, but get american
> > date format 12/07/05 06:02 becomes 7/12/05 and 13/12/05 06:05 returns
> > null.
> >
> > The VBA code created by the macro recorder is as follows :-
> >
> >
> > Sub DateFormatter()
> > '
> > ' DateFormatter Macro
> > ' Macro recorded 12/7/2005 by Johnsd
> > '
> >
> > '
> > Columns("G:G").Select
> > Selection.Copy
> > ActiveWindow.SmallScroll ToRight:=2
> > Columns("N:N").Select
> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd,
> > SkipBlanks:= _
> > False, Transpose:=False
> > Application.CutCopyMode = False
> > Selection.Cut
> > ActiveWindow.SmallScroll ToRight:=-7
> > Columns("G:G").Select
> >
> > ActiveSheet.Paste
> > Selection.NumberFormat = "d/m/yyyy h:mm"
> > End Sub
> > --
> > Can anyone help
> >
> > Thanks in advance
> >
> > DaveJ
>
>
> Thanks Norman but Date that can be formmatted into american date are converted eg 6/7/05 convert to 7/6/05 dates such as 15/7/05 are ignored

have you any other sugestions

[DaveJhelpexcel]

> Thanks Norman but Date that can be formmatted into american date are
converted eg 6/7/05 convert to 7/6/05 dates such as 15/7/05 are ignored

have you any other sugestions


--
Thanks in advance

DaveJ


"Norman Jones" wrote:

> Hi Dave,
>
> Try something like:
>
> Sub tester01()
> Dim Rng As Range
>
> Set Rng = Range("G1:G20")
>
> With Rng
> .NumberFormat = "dd /mm/yyyy hh:mm"
> .Value = .Value
> End With
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "DaveJhelpexcel" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to convert a text date - time to internal date format, I can do
> > this fine by copying the column and past special with add to another
> > column
> > and pasting back to the first column.
> > Ive used record macro to create VBA code to do the same, but get american
> > date format 12/07/05 06:02 becomes 7/12/05 and 13/12/05 06:05 returns
> > null.
> >
> > The VBA code created by the macro recorder is as follows :-
> >
> >
> > Sub DateFormatter()
> > '
> > ' DateFormatter Macro
> > ' Macro recorded 12/7/2005 by Johnsd
> > '
> >
> > '
> > Columns("G:G").Select
> > Selection.Copy
> > ActiveWindow.SmallScroll ToRight:=2
> > Columns("N:N").Select
> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd,
> > SkipBlanks:= _
> > False, Transpose:=False
> > Application.CutCopyMode = False
> > Selection.Cut
> > ActiveWindow.SmallScroll ToRight:=-7
> > Columns("G:G").Select
> >
> > ActiveSheet.Paste
> > Selection.NumberFormat = "d/m/yyyy h:mm"
> > End Sub
> > --
> > Can anyone help
> >
> > Thanks in advance
> >
> > DaveJ
>
>
>

[Norman Jones]

Hi Dave,

The following worked for me:

Sub Tester03()
Dim rng As Range
Dim rngArea As Range

Set rng = Range("A1:A10, B2:B7, D1:D9") ' (Test Range) CHANGE!

For Each rngArea In rng.Areas
With rngArea
.NumberFormat = "dd /mm/yyyy hh:mm"
.Value = .Value
End With
Next rngArea

End Sub


---
Regards,
Norman



"DaveJhelpexcel" <[email protected]> wrote in message
news:[email protected]...
>> Thanks Norman but Date that can be formmatted into american date are
> converted eg 6/7/05 convert to 7/6/05 dates such as 15/7/05 are ignored
>
> have you any other sugestions
>
>
> --
> Thanks in advance
>
> DaveJ
>
>
> "Norman Jones" wrote:
>
>> Hi Dave,
>>
>> Try something like:
>>
>> Sub tester01()
>> Dim Rng As Range
>>
>> Set Rng = Range("G1:G20")
>>
>> With Rng
>> .NumberFormat = "dd /mm/yyyy hh:mm"
>> .Value = .Value
>> End With
>>
>> End Sub
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "DaveJhelpexcel" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > I'm trying to convert a text date - time to internal date format, I can
>> > do
>> > this fine by copying the column and past special with add to another
>> > column
>> > and pasting back to the first column.
>> > Ive used record macro to create VBA code to do the same, but get
>> > american
>> > date format 12/07/05 06:02 becomes 7/12/05 and 13/12/05 06:05 returns
>> > null.
>> >
>> > The VBA code created by the macro recorder is as follows :-
>> >
>> >
>> > Sub DateFormatter()
>> > '
>> > ' DateFormatter Macro
>> > ' Macro recorded 12/7/2005 by Johnsd
>> > '
>> >
>> > '
>> > Columns("G:G").Select
>> > Selection.Copy
>> > ActiveWindow.SmallScroll ToRight:=2
>> > Columns("N:N").Select
>> > Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd,
>> > SkipBlanks:= _
>> > False, Transpose:=False
>> > Application.CutCopyMode = False
>> > Selection.Cut
>> > ActiveWindow.SmallScroll ToRight:=-7
>> > Columns("G:G").Select
>> >
>> > ActiveSheet.Paste
>> > Selection.NumberFormat = "d/m/yyyy h:mm"
>> > End Sub
>> > --
>> > Can anyone help
>> >
>> > Thanks in advance
>> >
>> > DaveJ
>>
>>
>>