Editing a text string for date formatting

[Kim]

I am pasting dates from Primavera Project Planner into an
Excel spreadsheet. When the dates have an A at the end of
the string it means it is an actual date rather than a
proposed date. I want to edit all fields with an "A" at
the end to remove the A and leave the field with the date
formatting that all other date fields in the spreadsheet
have. I have tried the RIGHT and LEFT functions and keep
getting an error.

The data looks like this when it comes in 08JUL03A. I want
those cells that have the "A" to remove the "A" and then
format the remaining data to 08-Jul-03.

Any ideas?

 

[Bob Phillips]

Kim,

Use this formula

=DATEVALUE(LEFT(A23,7))

and format the cells as dd-mmm-yy

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)