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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,567
Messages
5,838,146
Members
430,531
Latest member
pmisner

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