Re: [SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell
Solved the problem. All I had to do was the following; Format the selected calendar cell as follows; DD (then a bunch of spaces) WW The result returned was the correct calendar date number and the current week of the year. Now all I have to figure out is how the get the day-number-of-the-year in there. Thank you, all, for your efforts. Carl On 07/12/2018 04:12 AM, Brian Barker wrote: > At 19:30 11/07/2018 -0400, Carl Winerich wrote: >> It's back to the same old question; ... > > Indeed so - and the answer is very much the same as on the previous > two occasions you have asked this. > >> In a calendar, how can I put the day's date (number only) and week >> number and day-number-of-the-year in one cell? > > To put multiple items in one cell, you must concatenate the individual > values. You can do this using the CONCATENATE() function: > =CONCATENATE(;;) > or probably more easily using the & operator: > =&& > - where , , and represent your items - references or > formulae. > > Note that you will have to take care of spacing, so you may need > something like > =&" "&&" "& > instead. > > Note that concatenation - expressed either way - both requires and > produces text items. If you pass it numeric values, these are > converted to text automatically on the fly. If you want more control > over exactly how a value is represented, you may want to do the > conversion yourself, using the TEXT() function, which allows you to > specify the format of the converted value. So if you wanted, say, all > your dates to appear as two-digit numbers, so the first of the month > was "01" instead of just "1", your formula might start > =TEXT(;"00")&" "& ... > >> Particularly in cell A5, B5...etc which are all cells that contain >> only the day number of the month. Here's a link to the Calc calendar >> template I'm trying to use- >> https://extensions.libreoffice.org/templates/calendar-creator The >> template is very useful and I don't want to interfere or mess up the >> calendar creation - which it does perfectly. > > You'll have to puzzle out how to derive formulae for the required two > new values for yourself, unless the answers are buried somewhere in > the existing template. The functions WEEKNUM(), WEEKNUMADD(), and > DAYS() may help. You can read the existing formulae in your template > by selecting relevant cells and looking in the Input Line for each, of > course. > > 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: [SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell
On 07/12/2018 04:12 AM, Brian Barker wrote: At 19:30 11/07/2018 -0400, Carl Winerich wrote:It's back to the same old question; ...Indeed so - and the answer is very much the same as on the previous two occasions you have asked this.In a calendar, how can I put the day's date (number only) and week number and day-number-of-the-year in one cell?To put multiple items in one cell, you must concatenate the individual values. You can do this using the CONCATENATE() function:=CONCATENATE(one;two;three)or probably more easily using theoperator:=onetwothree- whereone,two, andthreerepresent your items - references or formulae.Note that you will have to take care of spacing, so you may need something like=one" "two" "threeinstead.Note that concatenation - expressed either way - both requires and produces text items. If you pass it numeric values, these are converted to text automatically on the fly. If you want more control over exactly how a value is represented, you may want to do the conversion yourself, using the TEXT() function, which allows you to specify the format of the converted value. So if you wanted, say, all your dates to appear as two-digit numbers, so the first of the month was "01" instead of just "1", your formula might start=TEXT(one;"00")" "...Particularly in cell A5, B5...etc which are all cells that contain only the day number of the month. Here's a link to the Calc calendar template I'm trying to use-https://extensions.libreoffice.org/templates/calendar-creatorThe template is very useful and I don't want to interfere or mess up the calendar creation - which it does perfectly.You'll have to puzzle out how to derive formulae for the required two new values for yourself, unless the answers are buried somewhere in the existing template. The functions WEEKNUM(), WEEKNUMADD(), and DAYS() may help. You can read the existing formulae in your template by selecting relevant cells and looking in the Input Line for each, of course.I trust this helps.Brian BarkerBrian,I understand the WEEKNUM, and DAYS functions. Maybe the real problem is within the cell A5?Existing cell (for the month of February) has the following formula;=$G30-$H30+$D32+$E32+COLUMN(A5)That stuff points to various parts of the spreadsheet and returns the correct numeric day number of the month.When I try to concat it with, say, =DAYS then Err:510 appears in the cell. Any variation of the data produces a similar error.=$G30-$H30+$D32+$E32+COLUMN(A5)" "=DAYS(A5,$January.A5)+1I've successfully used concat or =text...etc as you suggested and placed the formula in other blank cells in an unused portion of the spreadsheet and have obtained the desired result. But for simplicity it would be best to have the formula and result in the correct cell.Any ideas?Thank you,Carl -- 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
[SUSPECT] Re: [libreoffice-users] date, day number, week number in one cell
At 19:30 11/07/2018 -0400, Carl Winerich wrote: It's back to the same old question; ... Indeed so - and the answer is very much the same as on the previous two occasions you have asked this. In a calendar, how can I put the day's date (number only) and week number and day-number-of-the-year in one cell? To put multiple items in one cell, you must concatenate the individual values. You can do this using the CONCATENATE() function: =CONCATENATE(;;) or probably more easily using the & operator: =&& - where , , and represent your items - references or formulae. Note that you will have to take care of spacing, so you may need something like =&" "&&" "& instead. Note that concatenation - expressed either way - both requires and produces text items. If you pass it numeric values, these are converted to text automatically on the fly. If you want more control over exactly how a value is represented, you may want to do the conversion yourself, using the TEXT() function, which allows you to specify the format of the converted value. So if you wanted, say, all your dates to appear as two-digit numbers, so the first of the month was "01" instead of just "1", your formula might start =TEXT(;"00")&" "& ... Particularly in cell A5, B5...etc which are all cells that contain only the day number of the month. Here's a link to the Calc calendar template I'm trying to use- https://extensions.libreoffice.org/templates/calendar-creator The template is very useful and I don't want to interfere or mess up the calendar creation - which it does perfectly. You'll have to puzzle out how to derive formulae for the required two new values for yourself, unless the answers are buried somewhere in the existing template. The functions WEEKNUM(), WEEKNUMADD(), and DAYS() may help. You can read the existing formulae in your template by selecting relevant cells and looking in the Input Line for each, of course. 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