Forum Discussion

metertreater's avatar
metertreater
Copper Contributor
May 06, 2022

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

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?

excel
Formulas and Functions

4 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 06, 2022

    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

Resources