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

  • Thread starter JDD
  • Start date
  • #1
JDD
15
0
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!
 

Answers and Replies

  • #2
jtbell
Mentor
15,803
4,039
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.
 
  • #3
JDD
15
0
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!
 
  • #4
Nugatory
Mentor
13,452
6,487
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.
 
  • #5
pbuk
Science Advisor
Gold Member
2,354
1,091
Yes you could achieve this with a macro something like this:
Code:
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"
 
  • #6
JDD
15
0
Yes you could achieve this with a macro something like this:
Code:
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!
 
  • #7
BWV
914
952
=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
 
  • #8
Vanadium 50
Staff Emeritus
Science Advisor
Education Advisor
27,244
11,236
=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.
 
  • #9
BWV
914
952
Just use the formula, can’t why it’s not the better option for someone who has never coded
 
Last edited:
  • #10
pbuk
Science Advisor
Gold Member
2,354
1,091
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".
 
  • Like
Likes Vanadium 50

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

  • Last Post
Replies
2
Views
5K
  • Last Post
Replies
2
Views
3K
  • Last Post
Replies
21
Views
2K
  • Last Post
Replies
6
Views
2K
  • Last Post
Replies
1
Views
754
Replies
3
Views
15K
Replies
2
Views
2K
Replies
8
Views
1K
Replies
5
Views
1K
Replies
1
Views
2K
Top