Solved Date format when typing the date?

Microsoft Office 2013 professional produ...
April 9, 2015 at 04:13:21
Specs: Windows 8.1
Hello all

Is there anyway that I can type in a date like: 010199 and it then gets recognized as a date format and displays it as 01-01-1999 ?

I can do it the other way around, if I type 01-01-1999 and have modified the date format to be ddmmyy, it works, but somehow it doesnt work the other way around.

Any ideas for this? I know it is possible to make it happen by using another column and the number 010199 then gets converted to 01-01-1999, but I need this to be done automaticly (and with no macros)

Thanks in advance


See More: Date format when typing the date?

Report •

April 9, 2015 at 04:25:02
Where are you typing this address - presumably in an Excel column?

Report •

April 9, 2015 at 05:25:36
✔ Best Answer
You need to Custom Format the cell or cells.

Select the cell or range of cells you want to re-format.
Select the Home Tab
Select Format
Select Format Cells (Bottom of list)
In the Pop Up window:
Select Number Tab
Select Custom

In the Type input box enter: MM-DD-YYYY

See how that works for you.


Sorry, I misread your post

DerbyDad03 is correct, you cannot enter just the numbers you will need to also use the Dash and the format will be 01-01-2015.

The only other way is with a Macro.
The Dash or Slash tells Excel that it is a Date not just another number.


message edited by mmcconaghy

Report •

April 9, 2015 at 05:58:47
I don't think you can do it in the same cell as you are entering the value.

Any type of Date format is going to assume that the number entered is a Date Serial number and convert it to a Date. Entering 010199 in a cell formatted as a Date is going to be seen as Date Serial 10199 or 10,199 days since the start of Excel time (01-00-1900) and return 12-03-1927.

I'm pretty sure that you are going to have to use another column or a macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Related Solutions

April 9, 2015 at 06:52:07
If you are NEVER, EVER going to use the Date in any type of calculation
and only want to Display the number in a date format,
then a Custom Format like: 00"-"00"-"00
will Display the number in a Date like format,
but it will NOT be a Date.


Report •

April 9, 2015 at 07:54:23
There is an option in the (Excel) cell format menu that includes text; which means that the entry is displayed exactly as typed/entered.

But this may not be what you actually require?

Report •

Ask Question