Date/Time Conversion

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.

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

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?

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.

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)

Code:

---

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

---

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.

:)

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...

VB Code:

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