Very easy I'm sure

[themax16]

Afternoon..
This is the easiest thing to do i'm sure but i cant!!

I need to enter a date into a cell format. 170983 and it change it to
show. 17/09/1983.

I cant find this in the formatting options as all show in mmddyyyy
format.

Thanks
James

 

[Jim Rech]

Morning..<g>

Excel number formatting (including date/time, etc.) is generally not an
input template that tells Excel how to intrepret an entry. Rather it is a
method of controlling the display format of a number _after_ it is entered.
So, no matter how you format a cell, Excel does not look to it in
intrepreting an entry like 17091983. It sees it for what it looks like (a
number). If you want Excel to know an entry is a date, your input (not your
formatting) has to tell it.


--
Jim
message |
| Afternoon..
| This is the easiest thing to do i'm sure but i cant!!
|
| I need to enter a date into a cell format. 170983 and it change it to
| show. 17/09/1983.
|
| I cant find this in the formatting options as all show in mmddyyyy
| format.
|
| Thanks
| James
|
|
| --
| themax16
| ------------------------------------------------------------------------
| themax16's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=23622
| View this thread: http://www.excelforum.com/showthread.php?threadid=478218
|

 

[Dave O]

However, if you enter your 170983 into cell A1 with the following
formula in B1
=DATE(RIGHT(A1,2),MID(A1,3,2),LEFT(A1,2))
.... and format it for dd/mm/yyyy format Excel will convert it to a
serial date.