Cell Format Won't Change Because of Error

[bjitima]

We have exported a Visual Foxpro .DBF file to an Excel .XLS file. Included in the fields are dates formatted as 01/01/07. When the new .XLS file is opened, all of the date fields are then listed as 01-Jan-07 with an error marker. I try to format within Excel so that it changes to 01/01/07 the way we want it, but it is not changed. The error reports that "This cell contains a date string represented with only a 2 digits for the year."

If I correct each cell's error, one by one, it will convert to the date format that we want. However, there are several hundred cells with the date field and changing them by hand is not really practical.

I've tried telling the Error Checking to ignore this particular error, but it still will not let me reformat the cells.

Does anyone know of a way to either completely ignore this so that I can have the date formatted correctly right off the bat, or a way to correct the error in bulk instead of each cell, one by one?

 

[xlbo]

in a cell next to the dates, enter

=VALUE(A1)
where the date is in A1

Format this cell as dd/mm/yy to check that it has worked

Copy down and paste values over the original dates

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bjitima]

Well, that worked, but this was something we were trying to set up as a more automated type of thing. The cells are in bunches of about 8-10 each, with several columns so it still adds more manual work than what we were shooting for.

Basically, we're trying to take our database and send it back to food vendors in a format they can read telling them exactly what we have ordered from them for specific dates. We're trying to schedule it and automatically attach it to an e-mail so basically we turn it on and never touch it again.

 

[bjitima]

It looks like it is going to be easiest to use a four digit year instead of a two digit year and completely avoid the problem altogether.

 

[xlbo]

yup - although there is a "Times 1" fix that should work as well

Enter a 1 into any blank cell
copy it
Select all data
Edit>Paste Special Values & Multiply

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question