Date & Time Format

[Brampton76]

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?

 

[Gary''s Student]

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

 

[Roger Govier]

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

 

[Dave Peterson]

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]

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!

 

[Brampton76]

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

 

[Brampton76]

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.

 

[Dave Peterson]

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.

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.