Date format

Topic

New Reply

A database was sent to me with the dates 01/02/48 -with two spaces before the 01 for some reason.
I would like it to be 01/02/1948. Right click format does not work. Here is a copy of the database dates
I’m using excel 2003

Reply | Quote

Replies

In your sample file, it is THREE spaces not TWO before the date value.
You could convert by using a formulaa in column [B} e.g.
=DATE(RIGHT(A2,2),MID(A2,4,2),MID(A2,7,2))

..and copying down

zeddy

Reply | Quote

Looks like your “dates” are actually text strings even though the cell format is “date” so you’ll have to convert them to a date using Datevalue()

You should also watch out for Excel’s default for deciding a 2 digit year is in the 20th or 21st centuries.

Reply | Quote

You can find all spaces and replace with nothing and excel will convert them to dates. You will have to adjust some as mentioned by iansavell, since the digit year “20” is presumed to be 2020 and not 1920. (00-29 are presumed to be 2000-2029 and 30-99 will be 1930-1999).

Steve

Reply | Quote