I export data that includes the date & time. The data is exported directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK. I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

Say A1 contains:

25.12.2008 09:30

In another cell enter:
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+TIME(MID (A1,12,2),RIGHT(A1,2),0)
and format:
Format Cells... . Number Custom dd/mm/yyyy hh:mm

This will display:
25/12/2008 09:30


--
Gary''s Student - gsnu200772


"Brampton76" wrote:

I export data that includes the date & time. The data is exported directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK. I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

Hi Glenn

The following seemed to work for me

Sub test()

Dim c As Range
For Each c In Range("G2:G13")
c.Value = Format(Replace(c, ".", "/"), "dd/mm/yyyy hh:mm")
Next

End Sub


--
Regards
Roger Govier

"Brampton76" wrote in message
...
I export data that includes the date & time. The data is exported
directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This
presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK.
I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I
have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day
and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

Did you really mean: dd.mm.yyyy hh:mm (with a 4 digit year)?

The first thing I would do is reformat that data in a nice unambiguous date
format: mmmm dd, yyyy hh:mm:ss

You may find that some of your data was imported as real dates--but not imported
correctly. But with the dots as the separator, I would guess that you would be
ok.

I'd use a macro like this (after selecting the range to fix):

Option Explicit
Sub Macro1()

Dim myRng As Range
Dim myStr As String
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, _
xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Select some cells with values"
Exit Sub
End If

For Each myCell In myRng.Cells
'dd.mm.yyyy hh:mm
With myCell
myStr = .Value
.Value = DateSerial(Mid(myStr, 7, 4), _
Mid(myStr, 4, 2), _
Left(myStr, 2)) _
+ TimeSerial(Mid(myStr, 12, 2), _
Right(myStr, 2), 0)
.NumberFormat = "mm/dd/yyyy hh:mm"
End With
Next myCell
End Sub



Brampton76 wrote:

I export data that includes the date & time. The data is exported directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK. I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

--

Dave Peterson

Many thanks. It converted the "." to "/" OK but the format remains
mm/dd/yyyy. I am wondering if it is a deeper problem for me. The format
cell screen allows me to select dd/mm/yyyy as a format but in the example
screen it shows mm/dd/yyyy. All very odd!
--
Glenn


"Roger Govier" wrote:

Hi Glenn

The following seemed to work for me

Sub test()

Dim c As Range
For Each c In Range("G2:G13")
c.Value = Format(Replace(c, ".", "/"), "dd/mm/yyyy hh:mm")
Next

End Sub


--
Regards
Roger Govier

"Brampton76" wrote in message
...
I export data that includes the date & time. The data is exported
directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This
presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK.
I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I
have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day
and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

Many thanks for all your help. I can only imagine there was some 'finger
trouble' on my part, because it works a treat now.
--
Glenn


"Roger Govier" wrote:

Hi Glenn

The following seemed to work for me

Sub test()

Dim c As Range
For Each c In Range("G2:G13")
c.Value = Format(Replace(c, ".", "/"), "dd/mm/yyyy hh:mm")
Next

End Sub


--
Regards
Roger Govier

"Brampton76" wrote in message
...
I export data that includes the date & time. The data is exported
directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This
presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK.
I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I
have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day
and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

And finally....... If I format the cells as dd/mm/yyyy hh:mm and run your
routine I get mm/dd/yyyy hh:mm. However, If I alter your routine to replace
as dd/mmm/yyyy hh:mm I get dd/mm/yyyy hh:mm - all a bit odd, but I am where I
need to be. Again, many thanks for your help and guidance.
--
Glenn


"Roger Govier" wrote:

Hi Glenn

The following seemed to work for me

Sub test()

Dim c As Range
For Each c In Range("G2:G13")
c.Value = Format(Replace(c, ".", "/"), "dd/mm/yyyy hh:mm")
Next

End Sub


--
Regards
Roger Govier

"Brampton76" wrote in message
...
I export data that includes the date & time. The data is exported
directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This
presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK.
I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I
have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day
and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn

You may want to format the cells in an unambiguous format.

I like: mmmm dd, yyyy hh:mm:ss

Then check to see if the converted values actually represent the dates from the
original source.

I'm wondering if 01.02.2008 12:30
is being converted to: January 2, 2008 12:30
or if it's getting converted to: February 1, 2008 12:30

Sometimes, just because the results look like dates doesn't mean that they are
the correct dates.

Brampton76 wrote:

And finally....... If I format the cells as dd/mm/yyyy hh:mm and run your
routine I get mm/dd/yyyy hh:mm. However, If I alter your routine to replace
as dd/mmm/yyyy hh:mm I get dd/mm/yyyy hh:mm - all a bit odd, but I am where I
need to be. Again, many thanks for your help and guidance.
--
Glenn

"Roger Govier" wrote:

Hi Glenn

The following seemed to work for me

Sub test()

Dim c As Range
For Each c In Range("G2:G13")
c.Value = Format(Replace(c, ".", "/"), "dd/mm/yyyy hh:mm")
Next

End Sub


--
Regards
Roger Govier

"Brampton76" wrote in message
...
I export data that includes the date & time. The data is exported
directly
into Excel with the date & time as follows: dd.mm.yyy hh:mm. This
presents
no great problem because a simple find & replace on the column gives me
dd/mm/yyyy hh:mm which Excel recognises as a date and my formulas work OK.
I
thought a Macro would save me some time, however, no amount of trying
prevents the Macro from formating the date mm/dd/yyyy hh:mm. I think I
have
tried all the formatting options (including checking the system date and
time) and even tried including the formatting in the Macro and editing the
date in VBE. But............. it will insist on giving me the month, day
and
year in that order. I thought it may be Excel 2000 (at work) but it also
happens in Excel 2003 (at home). Has anyone any thoughts please?
--
Glenn


--

Dave Peterson