Date/Time Conversion

[RobDog888]

I am importing a tab delimited file into Excel and there are a
couple for field that are not importing correctly. The first field is a
date field formatted as YYYYMMDD and Excel thinks it is a number.
I need it to translate to MM/DD/YYYY. The second field is a Time
field formatted as military time without any separators - 1600 =
4:00 PM. What would be the simplest way to solve these issues?

Thanks in advance.

[Ecniv]

import them in.
Create an extra field for each.
Use excels formulae to convert as needed
(value,Left, mid right functions)

Example for the time :
=VALUE(LEFT(B2,2)&":"&RIGHT(B2,2))
Set the format to date / time to the style you want


Vince

[RobDog888]

There is no easier way, like a built in formatting function. Its just
a different date format.

This code would need to be put into a macro for the user to do.
How would I reference each cell without hard coding or
performing a loop?

[TheFIDDLER]

I will look at this tonight.
This is very similar to some work we did here several weeks ago.

I'll post a reply later today.

[Garratt]

I'm a little boerd so I worked out a solution. I'm sure someone smarter could find a more quick and simple way to do this, but here's what I came up with:


(Assuming that Column A is filled with your dates, and Column B is filled with your times, and they have the format you described in your post, I did a test with fake data and this worked)

Public Sub Hello()
Dim LastCell As Integer
Dim TempDate As Date
Dim TempString As String

LastCell = FindLastCell("A")   'Find the last used Cell of Column A
For X = 1 To LastCell
    TempString = Cells(X, 1).Value  ' (row,column)
    TempDate = Mid$(TempString, 5, 2) & "/" & Right$(TempString, 2) & "/" & Left$(TempString, 4)
    Cells(X, 1).Value = ""
    Cells(X, 1).NumberFormat = "mm/dd/yyyy"
    Cells(X, 1).Value = TempDate
Next X

LastCell = FindLastCell("B") 'Find the last used Cell of Column B
For X = 1 To LastCell
    TempString = Cells(X, 2).Value
    If Len(TempString) = 3 Then TempString = "0" & TempString
    TempString = Left$(TempString, 2) & ":" & Right$(TempString, 2)
    Cells(X, 2).NumberFormat = "hhmm"
    Cells(X, 2).Value = TempString
    Cells(X, 2).NumberFormat = "hh:mm AM/PM"

Next X
End Sub

'-----------------------------------------------------------------------

Private Function FindLastCell(MyColumn As String)
  Dim LastCell As Range
  With ActiveSheet
    Set LastCell = .Cells(.Rows.Count, MyColumn).End(xlUp)
    If IsEmpty(LastCell) Then
      'do nothing
    Else
      Set LastCell = LastCell.Offset(1, 0)
    End If
  End With
FindLastCell = LastCell.Row - 1
End Function

[RobDog888]

Thanks Garratt, looks like this will work. I'll just need to make
some mods on the time formatting. They want it to be military
time (24 hours) now. I have been sooo burned out lately -
burning the midnight oil.

[RobDog888]

Garratt, how come in the second loop for the time fis we have to
set the .NumberFormat twice? If I set it once it doesn't work. I
don't need the AM/PM format. We are keeping it 24 hour military
time. So I had to do this...

LastCell = FindLastCell("B") 'Find the last used Cell of Column B
For X = 1 To LastCell
    TempString = Cells(X, 2).Value
    TempString = Right$("0000" & TempString, 4)
    TempString = Left$(TempString, 2) & ":" & Right$(TempString, 2)
    Cells(X, 2).NumberFormat = "hhmm"
    Cells(X, 2).Value = TempString
    Cells(X, 2).NumberFormat = "hh:mm"
Next

Thanks