Re: [libreoffice-users] Date number has 5 digits
Den tors 31 dec. 2020 kl 10:43 skrev Johnny Rosenberg < gurus.knu...@gmail.com>: > > > Den tis 29 dec. 2020 kl 15:29 skrev Peter Dutton : > >> >> >> In cell >> A4 is the following code; >> >> >> ="Monday >> "+January.$A5 >> > > Okay, that would give #VALUE! as a result. With you so far. > > >> >> January.$A5 >> has the number 27 in it (December 27) >> > > December 27 gives me the value 44192, not 27. 27 december 2020 is 44192 > dags from 1899-12-30, so 44192 makes way more sense than 27 (which would > rather be 26 january 1900), but let's go on. > Days, not ”dags”. Day=dag in my language, sorry for the confusion… Kind regards Johnny Rosenberg > > >> >> >> What >> appears in cell A4 as a result is- Monday44557 >> > > No, it's #VALUE! I tested it a few seconds ago. > >> >> >> What is >> needed is- Monday27 >> >> >> I've >> tried reformatting cell A4 to a date or text or a number to no >> success. >> > > If you want 27 December to display as Monday 27, then just format the cell > that way. > > 1. Enter the date you want to format in a cell. Use the format that > corresponds to your language settings. In my case I would type: > 20-12-27 > or > 2020-12-27 > Now use the following as your cell's number format: > D > > Result (in my case): > söndag 27 > If I set the language to US English, it will display: > Sunday 27 > > Why not Monday, you might ask? Because 27 December 2020 was a Sunday. > > >> >> >> >> Any >> idea how to fix this? >> > > If you want 2020-12-27 to be a Monday, then no. Otherwise, see above. > > > > Kind regards > > Johnny Rosenberg > > >> >> >> >> >> Thanks, >> >> >> Peter >> >> >> -- >> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org >> Problems? >> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette >> List archive: https://listarchives.libreoffice.org/global/users/ >> Privacy Policy: https://www.documentfoundation.org/privacy >> > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Date number has 5 digits
Den tis 29 dec. 2020 kl 15:29 skrev Peter Dutton : > > > In cell > A4 is the following code; > > > ="Monday > "+January.$A5 > Okay, that would give #VALUE! as a result. With you so far. > > January.$A5 > has the number 27 in it (December 27) > December 27 gives me the value 44192, not 27. 27 december 2020 is 44192 dags from 1899-12-30, so 44192 makes way more sense than 27 (which would rather be 26 january 1900), but let's go on. > > > What > appears in cell A4 as a result is- Monday44557 > No, it's #VALUE! I tested it a few seconds ago. > > > What is > needed is- Monday27 > > > I've > tried reformatting cell A4 to a date or text or a number to no > success. > If you want 27 December to display as Monday 27, then just format the cell that way. 1. Enter the date you want to format in a cell. Use the format that corresponds to your language settings. In my case I would type: 20-12-27 or 2020-12-27 Now use the following as your cell's number format: D Result (in my case): söndag 27 If I set the language to US English, it will display: Sunday 27 Why not Monday, you might ask? Because 27 December 2020 was a Sunday. > > > > Any > idea how to fix this? > If you want 2020-12-27 to be a Monday, then no. Otherwise, see above. Kind regards Johnny Rosenberg > > > > > Thanks, > > > Peter > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Date number has 5 digits
Hi,Even more simple:Just create a number format equal to 'nnn" "d' (without the single quotes) and set the content of the cell to $January.A5. The nnn is the day of the week and the d is the day in the month in a date format. The only thing to remember is that the "d" is valid for English-similar locales (my French locale equivalent is "j"). If you want to create something that is multi-locale compatible, you will then need to use the TEXT function with a dynamic format, something like this which will work for both the French and English locales: =TEXT($January.A5,IF(TEXT(DATE(2020,1,1),"jj")="jj","nnn"" ""d","nnn"" ""j")). The format can be calculated on another sheet (or in a hidden cell) and referred to using a cell name, which would give a formula like this: =TEXT($January.A5,DATE_FORMAT) if the name of the cell is DATE_FORMAT. I hope this helps.Rémy. Le mardi 29 décembre 2020 à 16:34 +0100, Rob Jasper a écrit : > Peter, > Not sure what you are trying to accomplish. > 1- The date in January.$A5 is 12/27/2021. You can see that by > formatting cell January.$A5 as date. > 2- The date is internally represented as number 44557. You can see > this by formatting January.$A5 as number. > 3- To get the day of the month use formula =DAY(January.$A5) > 4- Your formula generates a error because you try to do arithmetic > with a string (“Monday “). Use ‘&' i.s.o. ‘+’ to append. The > formula then becomes: > ="Monday “($January.$A5) > To het the weekday of the specified day use the next formula: > =TEXT(WEEKDAY($January.A5);"NNN")&" "($January.A5) > Success,Rob. > > > > > Op 29 dec. 2020, om 15:27 heeft Peter Dutton > > het volgende geschreven: > > > > > > In cell A4 is the following code; > > > > ="Monday "+January.$A5 > > > > January.$A5 has the number 27 in it (December 27) > > > > What appears in cell A4 as a result is- Monday44557 > > > > What is needed is- Monday27 > > > > I've tried reformatting cell A4 to a date or text or a > > number to no success. > > > > > > > > Any idea how to fix this? > > > > > > > > Thanks, > > > > Peter > > > > -- To unsubscribe e-mail to: > > users+unsubscr...@global.libreoffice.org > > Problems? > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > > Posting guidelines + more: > > https://wiki.documentfoundation.org/Netiquette > > List archive: https://listarchives.libreoffice.org/global/users/ > > Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Date number has 5 digits
At 09:27 29/12/2020 -0500, Peter Dutton wrote: In cell A4 is the following code; ="Monday"+January.$A5 Do you mean that plus sign - or perhaps an ampersand? January.$A5 has the number 27 in it (December 27). I'm not sure what you think you mean by adding "(December 27)"! What I suspect you have in that cell is not the number 27 but instead the date 27 December 2021 (which indeed will be a Monday) - but formatted so as to display only the numerical day part of that date. What appears in cell A4 as a result is- Monday 44557. Good. You are concatenating the text "Monday" with the value of that date, and in this context no regard is had to the formatting of the cell. The actual value in your cell for 27 December 2021 is 44557, which is the number of days from the start date until that day. What is needed is- Monday 27 I've tried reformatting cell A4 to a date or text or a number to no success. Reformatting a cell already containing a value does not change the value - merely the way it is displayed. Your cell still contains the number 44557. Any idea how to fix this? You need to extract the day number part of the date value before you concatenate it with the text "Monday". To do that, use ="Monday "(January.$A5) Depending on exactly what you need, have you considered using =TEXT(January.$A5;"D") instead? I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Date number has 5 digits
Peter, Not sure what you are trying to accomplish. 1- The date in January.$A5 is 12/27/2021. You can see that by formatting cell January.$A5 as date. 2- The date is internally represented as number 44557. You can see this by formatting January.$A5 as number. 3- To get the day of the month use formula =DAY(January.$A5) 4- Your formula generates a error because you try to do arithmetic with a string (“Monday “). Use ‘&' i.s.o. ‘+’ to append. The formula then becomes: ="Monday “($January.$A5) To het the weekday of the specified day use the next formula: =TEXT(WEEKDAY($January.A5);"NNN")&" "($January.A5) Success, Rob. > Op 29 dec. 2020, om 15:27 heeft Peter Dutton het volgende > geschreven: > > > > In cell > A4 is the following code; > > > ="Monday > "+January.$A5 > > > January.$A5 > has the number 27 in it (December 27) > > > What > appears in cell A4 as a result is- Monday44557 > > > What is > needed is- Monday27 > > > I've > tried reformatting cell A4 to a date or text or a number to no > success. > > > > > Any > idea how to fix this? > > > > > Thanks, > > > Peter > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Date number has 5 digits
Put that number in E1 and formated it to a date field. Comes up as December 27, 2021 Used this formula ="Monday "(DAY(E1),"##") Using a + I get an error since you are adding a number to a string? ="Monday " seems to give the number value of the date. On 29 Dec 2020 at 9:27, Peter Dutton wrote: To: users@global.libreoffice.org From: Peter Dutton Subject: [libreoffice-users] Date number has 5 digits Date sent: Tue, 29 Dec 2020 09:27:20 -0500 > > > In cell > A4 is the following code; > > > ="Monday > "+January.$A5 > > > January.$A5 > has the number 27 in it (December 27) > > > What > appears in cell A4 as a result is- Monday 44557 > > > What is > needed is- Monday 27 > > > I've > tried reformatting cell A4 to a date or text or a number to no > success. > > > > > Any > idea how to fix this? > > > > > Thanks, > > > Peter > > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy ++ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ ++ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Date number has 5 digits
In cell A4 is the following code; ="Monday "+January.$A5 January.$A5 has the number 27 in it (December 27) What appears in cell A4 as a result is- Monday 44557 What is needed is- Monday 27 I've tried reformatting cell A4 to a date or text or a number to no success. Any idea how to fix this? Thanks, Peter -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
