Re: [libreoffice-users] Adding a / (forward slash)
Glory! You solved the problem! Thank you so very much! Peterbody,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small } a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; } a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; } comment { display:none; }From:Brian Barker [mailto:b.m.bar...@btinternet.com] Subject:[libreoffice-users] Adding a / (forward slash) Date:Saturday, April 4, 2020, 8:45 AM To:users@global.libreoffice.org Cc:Peter Dutton At 07:50 04/04/2020 -0400, Peter Dutton wrote:Thanks- the "/" works perfectly.Good-oh!Nothing's easy in this world.Many things are.What has been created in the calc sheet is the day number of the year which is followed by the "/". In the cell beside the result is the remaining number of days in the year. Here's an example of what I'd like to see for this date (February 10, 2020)41 / 325In this case 325 is the remaining number of days in the year 2020 from the date Feb. 10. The cell in which the formula used to obtain the figure of 325 is-=365-S4+1"S4" is the cell where the day number of the year is located returned by the formula, as mentioned below-=DATEDIF($Begin_Here.$E$76,R4,"d")" / "I still don't think this is the clearest or best formula for what you need. (And you've lost the ampersand, though I suspect that's a "feature" of your mail system.)What happens to the remaining days number in cell S4 the dreaded error - #VALUE! is returned. I suspect this has something to do with the formatting of the cell but can't figure it out.It's nothing to do with formatting: it's to do with, er, values. It's hardly surprising, since - as I made clear - what you have now put in S4 is not the number 41 but the *string* "41 / ", and that is not a number. You cannot calculate with strings (unless they happen to represent numbers in a simple way). What do you expect if you try to divide "three" by "two"? "one point five"?!Any ideas?Yes. Take the concatenated slash off your formula so that it creates the number 41 in S4, as before. Then use=S4" / "366-S4for your result.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] Adding a / (forward slash)
On Sat, 04 Apr 2020 13:45:38 +0100 Brian Barker wrote: > At 07:50 04/04/2020 -0400, Peter Dutton wrote: > >Thanks- the "/" works perfectly. > > Good-oh! > > >Nothing's easy in this world. > > Many things are. > > >What has been created in the calc sheet is the day number of the > >year which is followed by the "/". In the cell beside the result is > >the remaining number of days in the year. Here's an example of what > >I'd like to see for this date (February 10, 2020) > >41 / 325 > > > >In this case 325 is the remaining number of days in the year 2020 > >from the date Feb. 10. The cell in which the formula used to obtain > >the figure of 325 is- > >=365-S4+1 > > > >"S4" is the cell where the day number of the year is located > >returned by the formula, as mentioned below- > >=DATEDIF($Begin_Here.$E$76,R4,"d")" / " > > I still don't think this is the clearest or best formula for what you > need. (And you've lost the ampersand, though I suspect that's a > "feature" of your mail system.) > > >What happens to the remaining days number in cell S4 the dreaded > >error - #VALUE! is returned. I suspect this has something to do with > >the formatting of the cell but can't figure it out. > > It's nothing to do with formatting: it's to do with, er, values. It's > hardly surprising, since - as I made clear - what you have now put in > S4 is not the number 41 but the *string* "41 / ", and that is not a > number. You cannot calculate with strings (unless they happen to > represent numbers in a simple way). What do you expect if you try to > divide "three" by "two"? "one point five"?! > > >Any ideas? > > Yes. Take the concatenated slash off your formula so that it creates > the number 41 in S4, as before. Then use > =S4&" / "&366-S4 > for your result. Or keep the two numbers in separate columns as before, but insert another column just containing '/' in between them. It depends what the result is to be used for. > 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] Adding a / (forward slash)
At 07:50 04/04/2020 -0400, Peter Dutton wrote: Thanks- the "/" works perfectly. Good-oh! Nothing's easy in this world. Many things are. What has been created in the calc sheet is the day number of the year which is followed by the "/". In the cell beside the result is the remaining number of days in the year. Here's an example of what I'd like to see for this date (February 10, 2020) 41 / 325 In this case 325 is the remaining number of days in the year 2020 from the date Feb. 10. The cell in which the formula used to obtain the figure of 325 is- =365-S4+1 "S4" is the cell where the day number of the year is located returned by the formula, as mentioned below- =DATEDIF($Begin_Here.$E$76,R4,"d")" / " I still don't think this is the clearest or best formula for what you need. (And you've lost the ampersand, though I suspect that's a "feature" of your mail system.) What happens to the remaining days number in cell S4 the dreaded error - #VALUE! is returned. I suspect this has something to do with the formatting of the cell but can't figure it out. It's nothing to do with formatting: it's to do with, er, values. It's hardly surprising, since - as I made clear - what you have now put in S4 is not the number 41 but the *string* "41 / ", and that is not a number. You cannot calculate with strings (unless they happen to represent numbers in a simple way). What do you expect if you try to divide "three" by "two"? "one point five"?! Any ideas? Yes. Take the concatenated slash off your formula so that it creates the number 41 in S4, as before. Then use =S4&" / "&366-S4 for your result. 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] Adding a / (forward slash)
Thanks- the"/" works perfectly. Nothing's easy in this world. What has been created in the calc sheet is the day number of the year which is followed by the "/". In the cell beside the result is the remaining number of days in the year. Here's an example of what I'd like to see for this date (February 10, 2020) 41 / 325 In this case 325 is the remaining number of days in the year 2020 from the date Feb. 10. The cell in which the formula used to obtain the figure of 325 is- =365-S4+1 "S4" is the cell where the day number of the year is located returned by the formula, as mentioned below- =DATEDIF($Begin_Here.$E$76,R4,"d")" / " What happens to the remaining days number in cell S4 the dreaded error- #VALUE! is returned. I suspect this has something to do with the formatting of the cell but can't figure it out. Any ideas? Thanks, Peter From:Brian Barker [mailto:b.m.bar...@btinternet.com] Subject:[libreoffice-users] Adding a / (forward slash) Date:Friday, April 3, 2020, 9:55 PM To:users@global.libreoffice.org Cc:Peter Dutton At 20:24 03/04/2020 -0400, Peter Dutton wrote:Here's a formula I'm using=DATEDIF($Begin_Here.$E$76,R4,"d")The above formula returns the day number of the year where $Begin_Here.$E$76 [...] the date of 12/31/19Cell R4 has the date 10 (which is Monday, February 10, 2020)"10" is not a date - unless you mean the date that is internally stored as the number 10, which would be 9 January 1900! And that would be an error for the function, since the end date needs to be later than the start date."d" is the intervalWell, it's the unit in which you want the returned interval specified.Wouldn't it be easier to use=DAYS("2020-02-10";$Begin_Here.$E$76)or just="2020-02-10"-$Begin_Here.$E$76?Even more easily, abandon your "Begin_Here" value and try (with your 10 February 2020 date in R4)=R4-DATE(YEAR(R4)-1;12;31)This will produce the number 41 - providing the result cell is appropriately formatted.It would be nice to have a / (forward slash) after the day number of the year which is returned by the above formula. How can this be done?You can concatenate strings using the "" operator, so just put"/" after any of these formulae, such as=R4-DATE(YEAR(R4)-1;12;31)"/"The numerical value 41 is implicitly converted to a string and concatenated with the slash to create the *string* 41/ .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] Adding a / (forward slash)
At 20:24 03/04/2020 -0400, Peter Dutton wrote: Here's a formula I'm using =DATEDIF($Begin_Here.$E$76,R4,"d") The above formula returns the day number of the year where $Begin_Here.$E$76 [...] the date of 12/31/19 Cell R4 has the date 10 (which is Monday, February 10, 2020) "10" is not a date - unless you mean the date that is internally stored as the number 10, which would be 9 January 1900! And that would be an error for the function, since the end date needs to be later than the start date. "d" is the interval Well, it's the unit in which you want the returned interval specified. Wouldn't it be easier to use =DAYS("2020-02-10";$Begin_Here.$E$76) or just ="2020-02-10"-$Begin_Here.$E$76 ? Even more easily, abandon your "Begin_Here" value and try (with your 10 February 2020 date in R4) =R4-DATE(YEAR(R4)-1;12;31) This will produce the number 41 - providing the result cell is appropriately formatted. It would be nice to have a / (forward slash) after the day number of the year which is returned by the above formula. How can this be done? You can concatenate strings using the "&" operator, so just put &"/" after any of these formulae, such as =R4-DATE(YEAR(R4)-1;12;31)&"/" The numerical value 41 is implicitly converted to a string and concatenated with the slash to create the *string* 41/ . 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
[libreoffice-users] Adding a / (forward slash)
Here's a formula I'm using =DATEDIF($Begin_Here.$E$76,R4,"d") The above formulareturns the day number of the year where; $Begin_Here.$E$76 $Begin_Here is a sheet and $E$76 is a cell (on that sheet) with the date of 12/31/19 Cell R4 has the date 10 (which is Monday, February 10, 2020) "d" is the interval It would be nice to have a/(forward slash) after the day number of the year which is returned by the above formula. How can this be done? Thank you, 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