Date Format Problem

[nnadimi19]

Good Morning;

I have a sheet with a column called "Print Date" there are 10,000 rows to this column. The date in this column is defined as *3/14/81 the definition for this kind of format is "Date formats display date and time serial numbers as date values. Except for items that have an asterisk (*), applied formats do not switch date orders with the operating system". But I need to change the format to be "yyyy-mm-dd hh:mm:ss" which has already be defined in the custom format. How can I achieve this?????

*3/14/81 to be changed to 1981-03-14 00:00:00

 

[NBVC]

Try this formula in an adjacent cell, substituting A1 for your reference cell.
Then apply your custom format

=DATE(RIGHT(A1,2),MID(A1,FIND("*",A1)+1,FIND("/",A1)-2),MID(A1,FIND("#",SUBSTITUTE(A1,"/","#",1))+1,FIND("#",SUBSTITUTE(A1,"/","#",2))-FIND("#",SUBSTITUTE(A1,"/","#",1))-1))

 

[nnadimi19]

Thanks I will try that

 

[nnadimi19]

Well I tried the formula and it did not work. I think the issue is that there is physically not an "*" infront of the date but that the format in the cell definition has an "*"

 

[NBVC]

I can't understand then why you can't just change the format.

Perhaps in the cell next to it, try applying the format you want and then type

=A1, assuming your date is in A1.

What happens then?

 

[nnadimi19]

Because it got something to do with the way the cell has originally been formatted. If you click on cell and then higlight Date format you will see there that there is some documentation about dates with "*" formated dates.

 

[NBVC]

I know that,

but if you don't see the * in the actual cell, then I think you should be able to change the format. Did you try changing through custom formats.

What if you re-type the date over the old one, does that change the format anyway?

Other than this, I am not sure. I haven't come across that before. Perhaps it was downloaded from some other software and that messed things up.

What about try Data|Text to Columns and skip to the third dialogue and then select the Date radio button, then select the date format from the drop down. Does that help?

 

[nnadimi19]

NBVC: You are absolutely correct. Where there is a will there is a way. Your last suggestion worked.

Thanks