At 22:43 24/05/2009 +0530, H.S. Rai wrote:
http://www.goldentemplephotos.com/datesheet_new.xls has row no 362,
363 when opened using OpenOffice 3.0.1 (on Ubuntu 9.04) build 9379 shown as:
29/5/2009 EVENING 6TH CE-304 Geotechnical Engg. CIVL A0619
06/01/2009 EVENING 6TH CE-306 Irrigation Engineering-I CIVL A0620
While Google Doc as well MSExcell shows 01/06/2009 for row no. 363
(which is correct), that mean 1st of June is shown as 6th of Jan.
Such error is there for many rows in same spreadsheet.
Where is problem? With excel spreadsheet or with OpenOffice?
Probably neither!
Whoever created this spreadsheet or entered the data has done so
inconsistently. Most of the data in column A, though it looks like
dates, is actually text strings. Select A362, for example, and look
in the Input Line: you will see the tell-tale leading apostrophe,
indicating that what is there is the text string "29/5/2009" and not
a date (which could display the same way). But some values,
including your example of A363, have been entered as dates. A363
actually contains the sixth of January, not the first of June.
How has this happened? The display of dates in spreadsheets follows
the locale setting. For a US locale, for example, dates will be
interpreted as meaning month/day/year, but for many other locales
(are you in India?), the more logical day/month/year is used. Very
probably, someone had the locale set to US and managed to enter the
wrong dates so that they displayed as they expected with the wrong
locale setting! This shows up only when you use the spreadsheet with
a different locale: the genuine dates change their display but the
text strings do not. (Are your Google and Excel using a US style?)
The simple solution would be to change the cell format to
MM/DD/YYYY. You can do this for the entire column, in fact, since it
will not affect the text strings. But there is a danger in having a
mixture of values expressed in different ways (as you have
demonstrated!), so the best route would be to edit your date values
to change them so that all are either in one form or the other.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
