Date Format Problem

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Jul 14, 2002
Messages
240
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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

Board Regular
Joined
Jul 14, 2002
Messages
240
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Board Regular
Joined
Jul 14, 2002
Messages
240
NBVC: You are absolutely correct. Where there is a will there is a way. Your last suggestion worked.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,747
Messages
5,638,118
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top