LinkBack URL
About LinkBacks
I want to format some dates in a column that are from the late 1800's. I would like to have the format as follows, January 3, 1890. Using the excel format I can only get a format of 1/3/1890.
Jim O
I want to format some dates in a column that are from the late 1800's. I would like to have the format as follows, January 3, 1890. Using the excel format I can only get a format of 1/3/1890.
Jim O
Last edited by JO505; 10-20-2011 at 06:22 PM.
excel doesn't recognise dates before 1900 so you can't format those because they are treated as text - the entries will stay as they are entered - if you are using XDATE functions I think they may need to remain in a format that XDATE understands......
Audere est facere
...one possibility is to convert the date with a formula in another column, e.g. with "date" in J2 try this formula in the next column
=SUBSTITUTE(TEXT(REPLACE(J2,LEN(J2)-3,2,22),"mmmm d, yyyy"),", 22",", 18")
Assumes that year is in the 1800s......
That works, but as shown in the sample we have some problems.
Jim O
OK, try this version
=IF(ISERR(LEFT(J3)+0),J3,SUBSTITUTE(TEXT(IF(ISNUMBER(J3),J3,REPLACE(J3,LEN(J3)-3,2,22)),"mmmm d, yyyy"),", 22",", 18"))
That appears to be the ticket.
Thanks for your time and rapid response.
Jim O
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks