Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

Am 19.01.2012 09:27, MSwhip wrote:
I am interested in learning how to enter dates in cells in a format that
would allow me to calculate number of days lapsed between a "from date" and
a "to date" entered in 2 different cells.

Could anyone please help

Thank you

Date entry works almost independently from cell formatting. The one and only crucial setting is the *number format locale*. By default this is the same as in Tools>Options>Languages... 12/4 entered in a spreadsheet cell should always give 12th of April in this year ...
... unless the locale is US English. Then you get 4th of December.
... unless the number format is of category "Text". Then you get a literal text value of 4 characters "12/4". ... unless the number format is of category "Fraction". Then you get a cell value of 3. ... unless the number format is of category "Percent" which results in a text value "12/4%" which won't evaluate to 3%.

*Any other* number format setting will enter the *correct value* 41011 or 41247 which is the day number of 2012-04-12 or US English 2012-12-04 respectively.

12/ entered into a cell gives the 12th day of the current month, even with US English locale. 12th day of January 2012 is day number 40920 when you apply some decimal format. 40920 is *exactly the same value* as formatted numbers 2012-01-12, 12/1/2012, 1/12/2012(US), 4092000%, and thousands of other formattings of that very same number. However, the 5-digit text "40920" is a completely different value and so are the character sequences "2012-01-12", "12/1/2012", "1/12/2012"(US) and "4092000%" All spreadsheet formulas ignore the entire formatting thing. They perform all arithmetics with numbers and nothing but numbers. Once a cell has a value (number or text, calculated or entered), no formatting attribute will ever change that value, no color, no border, no font, nor number format. This is a true feature and all this is not Calc specific.

[Tutorial] Ten concepts that every Calc user should know:

For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.