[libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Hi :) I think you are not fully subscribed to the list yet. Is there a "confirmation email" waiting in your spam/junk folder? Don't you just format the cells as Dates rather than numbers or text and then just do one cell - the other and have the answer cell formatted as date too? Regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/How-to-enter-Dates-in-a-format-to-allow-the-counting-of-days-lapsed-between-a-from-a-date-cell-and-al-tp3671720p3672798.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
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: http://user.services.openoffice.org/en/forum/viewtopic.php?f=75&t=39529 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
This is my reply to all of you that made suggestions or stated your views. They were all very useful. With regards to the question about what exactly I was asking, the first issue was ...should I enter the date as "24 January 2012," or "January 24 2012" or '01/24/2012' or 24/01/2012 for the software to take it as a number to be deducted from the number represention the second date input of another cell to form the "from date1 to date2" proposition for it to come up with the number of days between those 2 dates? The second issue was the actual way to enter a formula to get the actual number od days between the 2 cells involved. Hope I made myself totally clear now. Thank you -- View this message in context: http://nabble.documentfoundation.org/How-to-enter-Dates-in-a-format-to-allow-the-counting-of-days-lapsed-between-a-from-a-date-cell-and-al-tp3671720p3684597.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Am 24.01.2012 13:58, MSwhip wrote: This is my reply to all of you that made suggestions or stated your views. They were all very useful. With regards to the question about what exactly I was asking, the first issue was ...should I enter the date as "24 January 2012," or "January 24 2012" or '01/24/2012' or 24/01/2012 for the software to take it as a number to be deducted from the number represention the second date input of another cell to form the "from date1 to date2" proposition for it to come up with the number of days between those 2 dates? The second issue was the actual way to enter a formula to get the actual number od days between the 2 cells involved. Hope I made myself totally clear now. Thank you -- View this message in context: http://nabble.documentfoundation.org/How-to-enter-Dates-in-a-format-to-allow-the-counting-of-days-lapsed-between-a-from-a-date-cell-and-al-tp3671720p3684597.html Sent from the Users mailing list archive at Nabble.com. The number of days is simply the difference between the 2 cell values (=B1-A1 for instance) because all spreadsheet dates are day numbers actually. The possible input formats for dates depend mostly on the locale you are using. Not knowing your locale setting, it is impossible to tell any details. There may be differences between US English and other English locales. That is Tools>Options>LanguageSettings>Languages>Locale Setting (2nd option) and it can be overridden in the cell format dialog. But regardless of how exactly you type some date, the result will always be displayed in *one particular* number format which is either the number format you applied the cell or some standard format for dates if you did not apply any number format. Why don't you simply fill out some cells and get a feeling for it? There are many ways to enter a date. There are no clear rules because the details depend on the localization teams for the respective locale. If your input looks exactly like what you typed and is bound to the left cell border then the input is a text which is *not* a valid date. If your input is right bound and the formula bar shows 2 digits for day and month and 4 digits for the year then you have entered a date. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Am 24.01.2012 15:17, Tom Davies wrote: Hi :) I think for data-entry it is best to enter the date as 01/24/2012 If you want to enter dates of the current month (January 2012 at the time of this writing) then you simply enter 1/ 13/ 4/ 30/ 15/ This will enter the *correct cell values* _unless_ the cells had been formatted as text *before* entry. If there are some February dates: 1/2 3/2 28/2 except for the US locale where you type 2/1 3/2 2/28 Of course you can enter full dates with long or short month name, but why? How exactly the correct cell values look like depends firstly on the locale setting, secondly on the number format. Once you entered the correct cell values, there are literally thousands of ways to change the appearance of these values (dozends of predefined formats for each locale plus user-defined formats). Once you entered the correct cell values, no formatting attribute will ever change your value, not even number format "Text" (which many users believe to be a magic conversion feature). I'm in England so it feels a tad weird to type the day and month 'the wrong way around', lol. I think it picks-up on whatever your global localisation is set at. Mine is set to UK so i type dd/mm/. Regards from This global setting applies to all types of table cells (Writer, Calc, Base), Writer fields, Calc cell styles, form controls (Base), numeric chart axes and may be more. All of these elements have a cell format dialog where you can override the locale for the current element. You can change it for the current Calc document by changing the locale of the default cell style which propagates to all subsequent styles which do not have an explicit locale setting. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Here is how I do it. I use Column A for the beginning date,Column B for the ending date, and Row 1 for the column headings. I enter the first beginning date in cell A2 and first ending date in cell B2. I continue adding the beginning and ending dates down these two columns. In cell C2, I enter this formula: =B2-A2. That is I enter the equal sign followed by B2 followed by a minus sign followed by A2. Use the Enter key to enter this formula into cell C2. Click cell C2, and you should see a little box at the bottom right of the outline of this cell. Use the mouse cursor to drag it down column C until you come to the last pair of beginning and ending dates. This will give you the number of days that exist between each pair of dates. --Dan On Thu, 2012-01-19 at 08:25 -0800, Tom wrote: > Hi :) > I think you are not fully subscribed to the list yet. Is there a > "confirmation email" waiting in your spam/junk folder? > > Don't you just format the cells as Dates rather than numbers or text and > then just do one cell - the other and have the answer cell formatted as date > too? > Regards from > Tom :) > > -- > View this message in context: > http://nabble.documentfoundation.org/How-to-enter-Dates-in-a-format-to-allow-the-counting-of-days-lapsed-between-a-from-a-date-cell-and-al-tp3671720p3672798.html > Sent from the Users mailing list archive at Nabble.com. > -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Hi: You only have to input like this example: in cell A1 : 10/12/2011 in cell B1 : 12/12/2011 in cell C1 : +B1-A1 ---> Result = 2 in cell C1 Regards, Jorge Rodríguez ___ El jue, 19-01-2012 a las 08:25 -0800, Tom escribió: > Hi :) > I think you are not fully subscribed to the list yet. Is there a > "confirmation email" waiting in your spam/junk folder? > > Don't you just format the cells as Dates rather than numbers or text and > then just do one cell - the other and have the answer cell formatted as date > too? > Regards from > Tom :) > > -- > View this message in context: > http://nabble.documentfoundation.org/How-to-enter-Dates-in-a-format-to-allow-the-counting-of-days-lapsed-between-a-from-a-date-cell-and-al-tp3671720p3672798.html > Sent from the Users mailing list archive at Nabble.com. > -- Atentamente, Jorge Rodríguez -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Hi :) I think for data-entry it is best to enter the date as 01/24/2012 and then have the cells formatted to display that in whichever way you want. It feels a bit weird to enter a date as numbers and then see it displayed as word&numbers but doing it that way makes everything else fall into place neatly. I'm in England so it feels a tad weird to type the day and month 'the wrong way around', lol. I think it picks-up on whatever your global localisation is set at. Mine is set to UK so i type dd/mm/. Regards from Tom :) --- On Tue, 24/1/12, MSwhip wrote: From: MSwhip Subject: [libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell To: users@global.libreoffice.org Date: Tuesday, 24 January, 2012, 12:58 This is my reply to all of you that made suggestions or stated your views. They were all very useful. With regards to the question about what exactly I was asking, the first issue was ...should I enter the date as "24 January 2012," or "January 24 2012" or '01/24/2012' or 24/01/2012 for the software to take it as a number to be deducted from the number represention the second date input of another cell to form the "from date1 to date2" proposition for it to come up with the number of days between those 2 dates? The second issue was the actual way to enter a formula to get the actual number od days between the 2 cells involved. Hope I made myself totally clear now. Thank you -- View this message in context: http://nabble.documentfoundation.org/How-to-enter-Dates-in-a-format-to-allow-the-counting-of-days-lapsed-between-a-from-a-date-cell-and-al-tp3671720p3684597.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell
Hi :) Thanks Andreas. It works! There are all sorts of neat tricks but that is the fastest way to enter dates. I hope that helps our op. Thanks and regards from Tom :) --- On Tue, 24/1/12, Andreas Säger wrote: From: Andreas Säger Subject: [libreoffice-users] Re: How to enter Dates in a format to allow the counting of days lapsed between a "from a date" cell and a "to a date' cell To: users@global.libreoffice.org Date: Tuesday, 24 January, 2012, 14:38 Am 24.01.2012 15:17, Tom Davies wrote: > Hi :) > I think for data-entry it is best to enter the date as > 01/24/2012 If you want to enter dates of the current month (January 2012 at the time of this writing) then you simply enter 1/ 13/ 4/ 30/ 15/ This will enter the *correct cell values* _unless_ the cells had been formatted as text *before* entry. If there are some February dates: 1/2 3/2 28/2 except for the US locale where you type 2/1 3/2 2/28 Of course you can enter full dates with long or short month name, but why? How exactly the correct cell values look like depends firstly on the locale setting, secondly on the number format. Once you entered the correct cell values, there are literally thousands of ways to change the appearance of these values (dozends of predefined formats for each locale plus user-defined formats). Once you entered the correct cell values, no formatting attribute will ever change your value, not even number format "Text" (which many users believe to be a magic conversion feature). > I'm in England so it feels a tad weird to type the day and month 'the wrong > way around', lol. I think it picks-up on whatever your global localisation > is set at. Mine is set to UK so i type dd/mm/. > Regards from This global setting applies to all types of table cells (Writer, Calc, Base), Writer fields, Calc cell styles, form controls (Base), numeric chart axes and may be more. All of these elements have a cell format dialog where you can override the locale for the current element. You can change it for the current Calc document by changing the locale of the default cell style which propagates to all subsequent styles which do not have an explicit locale setting. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
