How to have "Excel for Mac 2016" Auto-convert entry of yymmdd to yyyy-mm-dd

[JDD]

TL;DR Summary

Excel for Mac 2016

Hi,


I want to type, say, 20201008 or 201008 into a cell and as soon as I press
the enter or tab key have "Excel for Mac 2016" immediately convert and
display either entry as 2020-10-08.


I don't want to have another column set up that uses a formula to convert
20201008 or 201008 to 2020-10-08.


I want Excel to do the conversion in the background, or as a formula for
that cell, again, in the background, but that it works to allow me to type
into that cell 20201008 or 201008 and as soon as I press the enter or tab
key the cell displays 2020-10-08.


Anyone got a way to do this?


Thanks!

 

[jtbell]

Are you OK with entering the dashes yourself? If yes, then:

1. Go to the Format menu and choose Cells...
2. Select the Number tab if it's not already selected.
3. In the Category: box, choose Custom.
4. In the Type: box, enter yyyy-mm-dd.
5. Click the OK button.

This defines a new date format for this workbook and applies it to the currently-selected cell(s). To apply it to other cells, select them, then go to Format --> Cells... and select that format.

You can enter 2020-10-09 and it will be a proper Excel date. In the cell itself you should see 2020-10-09, and in the formula bar you should see 10/9/2020 (at least if you're in the US).

You can enter the month and/or day as a single digit, and it will add zeroes to pad them in the cell. For example entering 2020-9-5 produces 2020-09-05 in the cell and 9/5/2020 in the formula bar.

I use this custom format for dates, myself.

 

[JDD]

Are you OK with entering the dashes yourself? If yes, then:

1. Go to the Format menu and choose Cells...
2. Select the Number tab if it's not already selected.
3. In the Category: box, choose Custom.
4. In the Type: box, enter yyyy-mm-dd.
5. Click the OK button.

This defines a new date format for this workbook and applies it to the currently-selected cell(s). To apply it to other cells, select them, then go to Format --> Cells... and select that format.

You can enter 2020-10-09 and it will be a proper Excel date. In the cell itself you should see 2020-10-09, and in the formula bar you should see 10/9/2020 (at least if you're in the US).

You can enter the month and/or day as a single digit, and it will add zeroes to pad them in the cell. For example entering 2020-9-5 produces 2020-09-05 in the cell and 9/5/2020 in the formula bar.

I use this custom format for dates, myself.

No, I'd prefer not to have to input dashes.

Got another solution?

Thanks!

 

[Nugatory]

Got another solution?

An excel macro run on the cell when it changes should do the trick. You’ll still need to write a bit of VB code to get the automatic execution of the macro.

 

[pbuk]

Yes you could achieve this with a macro something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("AutoDateInput")) Is Nothing Then
        ' Use a temporary variable in case ConvertDate aborts.
        NewValue = ConvertDate(Target.Value)
        ' Disable events to stop infinite recursion.
        Application.EnableEvents = False
        Target.Value = NewValue
        Application.EnableEvents = True
    End If

End Sub

But

• you will need to make sure your ConvertDate function deals properly with edge cases (for instance if the user presses F2 Enter and so Target.Value will be an Excel date value)
• If I was doing this for a client I would say "look, I can do this with a macro but it will be a lot easier to maintain (and therefore cheaper in the long run) if I just use a formula in an adjacent column"

 

[JDD]

Yes you could achieve this with a macro something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("AutoDateInput")) Is Nothing Then
        ' Use a temporary variable in case ConvertDate aborts.
        NewValue = ConvertDate(Target.Value)
        ' Disable events to stop infinite recursion.
        Application.EnableEvents = False
        Target.Value = NewValue
        Application.EnableEvents = True
    End If

End Sub

But

• you will need to make sure your ConvertDate function deals properly with edge cases (for instance if the user presses F2 Enter and so Target.Value will be an Excel date value)
• If I was doing this for a client I would say "look, I can do this with a macro but it will be a lot easier to maintain (and therefore cheaper in the long run) if I just use a formula in an adjacent column"

I am not a coder.

I've never made a macro in "Excel for Mac 2016".

Can you give me instruction on how to go about creating the
macro you provided?

Like, I don't know where to begin.

But, I can learn if I'm shown the path.

Much appreciated!

 

[BWV]

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

where A1 is the data. If you want to enter either a 6 or 8 character date, just use if with the Len() function

 

[Vanadium 50]

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

I don't want to have another column set up that uses a formula to convert
20201008 or 201008 to 2020-10-08.

 

[BWV]

Just use the formula, can’t why it’s not the better option for someone who has never coded

 

[pbuk]

I am not a coder.

I've never made a macro in "Excel for Mac 2016".

Then this is not a good time to start: just create another (possibly hidden or collapsed) column for the calculation and live with whatever problems you perceive this as causing (why do you not want to do this?).

Can you give me instruction on how to go about creating the
macro you provided?

Like, I don't know where to begin.

But, I can learn if I'm shown the path.

Lots of videos on the internet, search for "excel vba worksheet_change".