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

[JohnDohe]

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 either
entry to 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,
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!

 

[McScooby]

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 either
entry to 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,
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!

Format cells, date & change custom option to what you want, then copy & re-paste to same column for results you already have?

 

[JohnDohe]

I tried a Custom format: yyyy/mm/dd

When I entered 20201008 I got #################
after entering or tabbing.

What am I doing incorrectly?

Thanks!

 

[mj_]

The problem is the way Excel interprets integer numbers as dates. Basically, it starts with day 1 on January 1, 1900. Thus, when you set your cell's format to Date (YYYY-MM-DD) it will interpret 20201008 as the 20,201,008 (twenty million twohundered and one thousand and eighth) day after January 1, 1900. In other words: May 7, 55307 (55307-05-07).

What you need to do is set a custom format of ####-##-##:

 

[JohnDohe]

The problem is the way Excel interprets integer numbers as dates. Basically, it starts with day 1 on January 1, 1900. Thus, when you set your cell's format to Date (YYYY-MM-DD) it will interpret 20201008 as the 20,201,008 (twenty million twohundered and one thousand and eighth) day after January 1, 1900. In other words: May 7, 55307 (55307-05-07).

What you need to do is set a custom format of ####-##-##:

View attachment 964259

Hi,

That works!

Now I need to expand my original post to included dates added
as yymmdd.

But since I need to refer back to that date for other calculations,
I need the cell to be an Excel date format.

What I'm thinking is to create a formula for a cell that will use
the contents of the yyddmm cell, convert yyddmm cell to an Excel
date format, display yyddmm as yy-mm-dd IN the original yyddmm
cell . . . and then I hide the column that does this work.

The goal is to be able to just enter yyddmm into the cell that displays,
press enter or tab, and the hidden column still works to convert yyddmm
to yy-dd-mm -but- as an Excel date. This way I can still refer back to
the yy-dd-mm cell for date calculations.

Will this work?

I'm not a regular Excel user so I appreciate someone who is more
experienced than me to review that idea, test it, and see if it works.

Thanks!

 

[McScooby]

If you've already got a column of numbers in the old format, highlight them all, cut and copy values to another column.
Then change formatting on the whole column you took them from to your custom settings.
Copy the moved column back to the original with values only, & that'll then change the format to how you want it, and anything entered afterwards will be in the new custom format.

 

[JohnDohe]

If you've already got a column of numbers in the old format, highlight them all, cut and copy values to another column.
Then change formatting on the whole column you took them from to your custom settings.
Copy the moved column back to the original with values only, & that'll then change the format to how you want it, and anything entered afterwards will be in the new custom format.

Hi,

Well, I'm looking to have Excel do all that copying, pasting format changing etc.

Can this whole process be automated?

Thanks!

 

[McScooby]

I'm a bit confused, Have you already got a sheet with values like your old format? It you do, then cutting and pasting sorts the old values, it's not needed for anything new you type in.
If you're starting afresh with no previous data, then ignore the cutting & copying and just type as normal, the cell format takes care of the way you want it (no macros/formulas needed)