Convert displayed date to mm/yyyy format, and convert that to text

[metertreater]

I am attempting to shorten my keystroke times for data entry of several hundred dates.

First, I need to convert the number format of six digits (ex: 072012) or five digits (ex: 72012) to display mm/yyyy (ex: 07/2012) in Excel. I used the custom cell format 00\/0000. That part was easy.

Next, I want to change what is displayed in that column to text in a new column that is actually in the mm/yyyy format (with the slash included). Ultimately, I need to take that text data and use it in Access in that same mm/yyyy format. Can anybody help? Do I need to write a VBA macro for this?

[#2]

@metertreater 

The format doesn't really matter - a date is a date regardless of how it is displayed.

So it is sufficient to convert the values to 'real' dates. After importing them into Access, apply the custom format mm/yyyy there.

Here is a macro. Select the range with the values to be converted before you run the macro.

Sub Num2Date()
    Dim rng As Range
    Dim s As String
    Application.ScreenUpdating = False
    For Each rng In Selection
        s = rng.Value
        rng = DateSerial(Right(s, 4), Left(s, Len(s) - 4), 1)
    Next rng
    Selection.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

[#3]

@Hans Vogelaar 

Thank you, Hans.

Now, would I use that macro in Excel prior to the import, or is it used afterwards in Access?

[#4]

@metertreater 

This is a macro to be run in Excel.

[#5]

I'll try it out. thank you