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

  • Thread starter JDD
  • Start date
  • Tags
    2016 Mac
In summary, the conversation discusses a request for Excel to automatically convert and display a date entry as a specific format without the need for a separate column or formula. Several solutions are suggested, including using a custom date format and using a macro. The potential drawbacks of using a macro are also mentioned.
  • #1
JDD
15
0
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!
 
Computer science news on Phys.org
  • #2
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.
 
  • Like
Likes pbuk
  • #3
jtbell said:
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
JDD said:
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.
 
  • Like
Likes pbuk
  • #5
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
pbuk said:
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
=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
BWV said:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
JDD said:
I don't want to have another column set up that uses a formula to convert
20201008 or 201008 to 2020-10-08.
 
  • #9
Just use the formula, can’t why it’s not the better option for someone who has never coded
 
Last edited:
  • #10
JDD said:
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?).
JDD said:
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

1. How do I set up Excel for Mac 2016 to automatically convert date entries from yymmdd to yyyy-mm-dd?

To set up automatic date conversion in Excel for Mac 2016, follow these steps:

  1. Open your Excel spreadsheet.
  2. Select the cells that contain the date entries you want to convert.
  3. Right-click on the selected cells and choose "Format Cells" from the drop-down menu.
  4. In the Format Cells dialog box, click on the "Number" tab.
  5. In the Category list, select "Date".
  6. In the Type box, choose the format "yyyy-mm-dd".
  7. Click "OK" to apply the changes.

2. Can I set up Excel for Mac 2016 to automatically convert date entries from any format to yyyy-mm-dd?

Yes, you can set up Excel for Mac 2016 to automatically convert date entries from any format to yyyy-mm-dd. Follow the same steps as mentioned in the first question, but in the Type box, select the desired date format. You can choose from a variety of date formats such as mm/dd/yyyy, dd/mm/yyyy, etc.

3. Is it possible to set up conditional formatting for automatic date conversion in Excel for Mac 2016?

Yes, it is possible to set up conditional formatting for automatic date conversion in Excel for Mac 2016. You can create a custom conditional formatting rule that converts the date entries to yyyy-mm-dd format based on specific criteria. For example, you can set a rule to convert all date entries in a certain column to the desired format.

4. Can I change the default date format in Excel for Mac 2016 to automatically convert entries to yyyy-mm-dd?

Yes, you can change the default date format in Excel for Mac 2016 to automatically convert entries to yyyy-mm-dd. To do this, go to Excel Preferences > Edit > Date, and select "Custom" as the default date format. In the Type box, enter "yyyy-mm-dd" and click "OK". This will change the default date format for all new Excel documents.

5. Will automatic date conversion in Excel for Mac 2016 affect my existing data?

No, automatic date conversion in Excel for Mac 2016 will not affect your existing data. It will only apply to the selected cells or the cells for which you have set up conditional formatting. Your existing data will remain in its original format unless you manually change it.

Similar threads

  • MATLAB, Maple, Mathematica, LaTeX
Replies
3
Views
204
  • Aerospace Engineering
Replies
2
Views
7K
Back
Top