format cell: problem with date

[svigno]

Hi everybody,

This is my first thread in this forum. I have problem regarding the format of the cell. My colleague made an excel file with a row of cells with TIME format but his intention was to make a DATE format, that means the date of birth of the persons engage on one study are expressed as dd.mm.yy Reading the file on OOo I get that row as dd:mm:yy but the format is TIME and not DATE! I dont want to change manually more than 500 data, so is there a quickest way to do it?

Thanks
Enrico
Italy

[Villeroy]

Could you please post some example values?
For instance:
Excel-editor puts date 31.12.1999
What I see in Calc is a time 31:12:1999
And -most important- the cell's value is 1.32314. Use =N(A1) to show the real numeric value of cell A1.

[svigno]

The problem is that even Excel treats that cells as TIME format and not DATE format. I think I will have to change mnually all the cells....

[keme]

Do try to test the values first, like Villeroy suggested. Answers to the follwing questions may help us to help you:

What values (dates) do you enter (actual examples)?
How do you enter them? (Keyboard input, CSV import, Excel worksheet, DDE?)
How are the values displayed (what do you see, again actual examples, please)?
What are the stored numerical values? (Reformat date/time cell as number, or use =N() in a different cell to get the vaue.)
What language (and locale) versions do you use for your operating system and for OpenOffice? (Different locales may treat dates differently.)

[svigno]

The main problem was in a file made in Excel.
In a row of cell the date of birth of people was entered in this way

Name Date of birth
John 12.10.05

That "12.10.05" was entered by my collegue with the intention to put as DATE, that means 12 September 2005 (in italy we put the day before the month). But he didnt know that both excel and OO treat that cell as TIME not as a DATE, that means 12 hour 10 mins 05 sec. OK? Now I want to change all that row from the format 12.10.05 to 12/10/05 without changing manually all the 512 cells.

Thanks
Enrico

[jwr]

[huwg]

In that case, first you need to be certain that all the dates were in the years 2000 to 2059.

Any that were not will now be wrong because the month will have been incremented, and only those in December will be easily detectable eg. 03.12.75 becomes 03.13.15 and there is no 13th month.

Any such as 05.07.61 will still look like dates. ie. 05.08.01

Even 06.08.99 will still look like a date ie. 06.09.39

If you know your dates all fell in the range 2000 to 2059 you can use Find & Replace to search for . and replace with /

Make sure Regular expressions is OFF under More Options, and you might need to select your wrongly entered dates first, and turn ON the Current selection only option, to prevent other data that include the . character from being corrupted.

It's that easy.

[svigno]

Thanks huwg.