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

[John Dohe]

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!

 

[kanewolf]

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!

If you want it to be an Excel date then you will have to have another column. If you just want to format your number with dashes then you can build a custom number format. For example, go into format cells and create a custom format of "####-##-##"

 

[John Dohe]

If you want it to be an Excel date then you will have to have another column. If you just want to format your number with dashes then you can build a custom number format. For example, go into format cells and create a custom format of "####-##-##"

Hi!

That was helpful! It did allow me to enter 20201008 and have it display as 2020-10-08.

However, I do need to have the cell formatted as a date for other calculations.

Any other ideas?

Thanks!

 

[kanewolf]

Hi!

That was helpful! It did allow me to enter 20201008 and have it display as 2020-10-08.

However, I do need to have the cell formatted as a date for other calculations.

Any other ideas?

Thanks!

Numbers and dates are different things in excel. I don't know of anything easy to the conversion.

 

[ex_bubblehead]

You must set the data type for those cells to "Date"

 

[USAFRet]

Hi!

That was helpful! It did allow me to enter 20201008 and have it display as 2020-10-08.

However, I do need to have the cell formatted as a date for other calculations.

Any other ideas?

Thanks!

Well, it is either a Number or a Date.
Not both.

 

[USAFRet]

You must set the data type for those cells to "Date"

I tried that. Didn't work...it still just saw it as a bunch of numbers.

 

[John Dohe]

Well, it is either a Number or a Date.
Not both.

Hi,

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!

 

[ex_bubblehead]

I tried that. Didn't work...it still just saw it as a bunch of numbers.

Works fine here:

I just set date format in A1,B1 (used mm/dd/yy).

Entered 10/08/20 in A1 and 10/09/20 in B1, then set C1 to =B1-A1

Got the correct answer of 1 in C1.

Date fields showed the correct dates at all times.

Been using this for years.

 

[ex_bubblehead]

Hi,

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!

That's not how Excel works. The results of your calculations will reside in the cell that does the calculation. If it were possible to do it the way you described it would form an infinite loop.

 

[USAFRet]

Works fine here:

I just set date format in A1,B1 (used mm/dd/yy).

Entered 10/08/20 in A1 and 10/09/20 in B1, then set C1 to =B1-A1

Got the correct answer of 1 in C1.

Date fields showed the correct dates at all times.

Been using this for years.

Oh yes, that works.

The issue here is taking the String or Number 20201011 and making that a "date".

 

[ex_bubblehead]

Oh yes, that works.

The issue here is taking the String or Number 20201011 and making that a "date".

That requires a macro written to take all possible ways of entering that date into account, unless one is willing to live with the errors that will occur. Basically what has been asked is not doable without some (lots of) extra coding.

 

[USAFRet]

That requires a macro written to take all possible ways of entering that date into account, unless one is willing to live with the errors that will occur. Basically what has been asked is not doable without some extra coding.

Exactly.

"I want to type, say, 20201008 or 201008 into a cell and.... "
Then the magic happens.

 

[John Dohe]

Oh yes, that works.

The issue here is taking the String or Number 20201011 and making that a "date".

@USAFRet

Hi!

I'm getting a little confused with all the replies! Having a time keeping track
of who says what, the way this forum presents.

So, can you give me an actual example of what works?

What to put into what cell, how to make it do the conversion of yymmdd
to yyyy-mm-dd, what the formulas are, and if you could include screenshots
I would be very appreciative!

Thanks!

 

[ex_bubblehead]

@USAFRet

Hi!

I'm getting a little confused with all the replies! Having a time keeping track
of who says what, the way this forum presents.

So, can you give me an actual example of what works?

Thanks!

Simple. Set the cell/s to "date format" with the appropriate display format chosen. Dates will then have to be entered in that format (no getting around it). You can then do date math in any other cell/s (just make sure to set those cells to "date" to get a proper display)

You can also use the date picker for a more point-shoot entry.

How to insert calendar in Excel (Date Picker & printable calendar template)

See how to insert calendar in Excel (Date Picker control) and how to quickly create a printable calendar based on an Excel calendar template.

www.ablebits.com

 

[USAFRet]

If you want it to be a "Date", you must enter it in some sort of date format.

Here, the shaded cells are Date format.
E1 entered as 2020-10-11. Excel sees this as a Date, and you can do whatever.
E2 entered as 20201011. Excel sees this as just a string of numbers
E3 entered as 20-10-11. Excel sees this as a String.

Converting from "20201011" to an actual Date would require some bit of macro/VB coding, and lots of error checking.

Entered as 120611. Is that the June 11, 1200, or Jan 11, 1206?

 

[John Dohe]

If you want it to be a "Date", you must enter it in some sort of date format.

Here, the shaded cells are Date format.
E1 entered as 2020-10-11. Excel sees this as a Date, and you can do whatever.
E2 entered as 20201011. Excel sees this as just a string of numbers
E3 entered as 20-10-11. Excel sees this as a String.

Converting from "20201011" to an actual Date would require some bit of macro/VB coding, and lots of error checking.

Entered as 120611. Is that the June 11, 1200, or Jan 11, 1206?

What if I limit the date entry to yymmdd?

Would that make it better?

 

[USAFRet]

What if I limit the date entry to yymmdd?

Would that make it better?

If you're going to do that, you might as well enter it as Date.
yyyy-mm-dd
Unambiguous.

 

[John Dohe]

What if I limit the date entry to yymmdd?

Would that make it better?

 

[John Dohe]

Simple. Set the cell/s to "date format" with the appropriate display format chosen. Dates will then have to be entered in that format (no getting around it). You can then do date math in any other cell/s (just make sure to set those cells to "date" to get a proper display)

You can also use the date picker for a more point-shoot entry.

How to insert calendar in Excel (Date Picker & printable calendar template)

See how to insert calendar in Excel (Date Picker control) and how to quickly create a printable calendar based on an Excel calendar template.

www.ablebits.com

THAT may be the plan!

Can the dropdown calandar be activated and navigated to the desired date
by using just the cursor keys ?