Re: [libreoffice-users] Calc date formulae
If the date to check is in B3, try the following formula: =IF(WEEKDAY(B3)=1;B3-2;IF(WEEKDAY(B3)=7;B3-1;B3)) Don't forget to format the target cell as a date ;-) Succes Met vriendelijke groeten, Salutations distinguées, Kind Regards, DRIES FEYS CORPORATE SERVICES • Specialist Software Developer TVH GROUP NV Brabantstraat 15 • BE-8790 WAREGEM T +32 56 43 42 11 • F +32 56 43 44 88 • www.tvh.com Watch our company movies on www.tvh.tv On 12 June 2015 at 09:29, Gordon Burgess-Parker gordo...@mail.com wrote: Hi, I have a series of future dates calculated according to certain criteria. I would like to: 1. Add a formula in a cell that says if this date is a Saturday, subtract one day, if this date is a Sunday subtract 2 days, if this date is a weekday, do nothing 2. I don't know if this is even possible - could I use this future date to generate a reminder in Lightning calendar? Thanks -- To unsubscribe e-mail to: users+unsubscr...@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 -- DISCLAIMER http://www.tvh.com/glob/en/email-disclaimer This message is delivered to all addressees subject to the conditions set forth in the attached disclaimer, which is an integral part of this message. -- To unsubscribe e-mail to: users+unsubscr...@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] Calc date formulae
On 06/12/2015 07:29 AM, Gordon Burgess-Parker wrote: could I use this future date to generate a reminder in Lightning calendar? Lightning uses SQLite. SQLite does no data validation. It has no objections to putting a 5 MB binary blob into a field that is supposed to be a single integer. The Lightning database has a number of tables, most of which are fairly complex. Consequently, you would have to verify that the data being exported to the Lightning database, is both of the correct data type, and within the parameters that Lightning can handle. If you have the programming savvy, you could write a macro/extension that exports the appropriate data in the spreadsheet, to Lightning. I'm not aware of anything for Firefox/Thunderbird/Sunbird or LibO/AOo/EU/NO that can read ODF spreadsheets and write to a Lightning Calendar. jonathon -- To unsubscribe e-mail to: users+unsubscr...@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] Calc date formulae
On 12 Jun 2015 at 8:49, Gordon Burgess-Parker wrote: Date sent:Fri, 12 Jun 2015 08:49:35 +0100 From:Gordon burgess-parkergordo...@mail.com To:users@global.libreoffice.org Subject:Re: [libreoffice-users] Calc date formulae On 12/06/15 08:44, Dries Feys wrote: If the date to check is in B3, try the following formula: =IF(WEEKDAY(B3)=1;B3-2;IF(WEEKDAY(B3)=7;B3-1;B3)) Don't forget to format the target cell as a date ;-) Succes Great! Brilliant! Thanks very much! Another option to do it without if's. Column A has dates, formula in column b uses col a, but if date is a sat or sunday, it subtracts the value. Friday, June 12, 2015 Friday, June 12, 2015 '=A1-(MOD(INT(A1),7)2)*(MOD(INT(A1),7)+1) Saturday, June 13, 2015 Friday, June 12, 2015 Sunday, June 14, 2015 Friday, June 12, 2015 Monday, June 15, 2015 Monday, June 15, 2015 Tuesday, June 16, 2015 Tuesday, June 16, 2015 Wednesday, June 17, 2015 Wednesday, June 17, 2015 Thursday, June 18, 2015 Thursday, June 18, 2015 Friday, June 19, 2015 Friday, June 19, 2015 Saturday, June 20, 2015 Friday, June 19, 2015 Sunday, June 21, 2015 Friday, June 19, 2015 Monday, June 22, 2015 Monday, June 22, 2015 Tuesday, June 23, 2015 Tuesday, June 23, 2015 Wednesday, June 24, 2015 Wednesday, June 24, 2015 -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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] Calc date formulae
On 12 Jun 2015 at 8:49, Gordon Burgess-Parker wrote: Date sent: Fri, 12 Jun 2015 08:49:35 +0100 From: Gordon Burgess-Parker gordo...@mail.com To: users@global.libreoffice.org Subject:Re: [libreoffice-users] Calc date formulae On 12/06/15 08:44, Dries Feys wrote: If the date to check is in B3, try the following formula: =IF(WEEKDAY(B3)=1;B3-2;IF(WEEKDAY(B3)=7;B3-1;B3)) Don't forget to format the target cell as a date ;-) Succes Not sure why what I see in email sent isn't what I get on the list? Column A has the date Friday, June 12, 2015 Column B has the formula =A1-(MOD(INT(A1),7)2)*(MOD(INT(A1),7)+1) Which uses the value of A1, Logical math then does if it is true that it is a Saturday or Sunday, it will then subtract the 1 or 2 to get the previous Friday Date. Long ago, used a Zeller congruency formula to calculate day of week using mod. 0 - sat 1-sun ... Hopefully, this will show up correctly. Great! Brilliant! Thanks very much! -- To unsubscribe e-mail to: users+unsubscr...@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 +--+ Michael D. Setzer II - Computer Science Instructor Guam Community College Computer Center mailto:mi...@kuentos.guam.net mailto:msetze...@gmail.com http://www.guam.net/home/mikes Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +--+ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS ROSETTA 30650812.183751 | SETI54443841.296543 ABC 16613838.513356 | EINSTEIN63454530.308699 -- To unsubscribe e-mail to: users+unsubscr...@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] Calc date formulae
On 12/06/15 08:44, Dries Feys wrote: If the date to check is in B3, try the following formula: =IF(WEEKDAY(B3)=1;B3-2;IF(WEEKDAY(B3)=7;B3-1;B3)) Don't forget to format the target cell as a date ;-) Succes Great! Brilliant! Thanks very much! -- To unsubscribe e-mail to: users+unsubscr...@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] Calc date formulae
=On 12 Jun 2015 at 18:39, Gordon Burgess-Parker , users wrote: From:=Michael D. Setzer iimi...@kuentos.guam.net To:! -- To unsubscribe e-mail to: users+unsubscr...@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] Calc date formulae
Hi, I have a series of future dates calculated according to certain criteria. I would like to: 1. Add a formula in a cell that says if this date is a Saturday, subtract one day, if this date is a Sunday subtract 2 days, if this date is a weekday, do nothing 2. I don't know if this is even possible - could I use this future date to generate a reminder in Lightning calendar? Thanks -- To unsubscribe e-mail to: users+unsubscr...@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
