From mboxrd@z Thu Jan 1 00:00:00 1970 From: Matt Price Subject: Re: formulas in spreadsheet to increase date Date: Wed, 17 Aug 2011 09:32:17 -0400 Message-ID: References: <21851.1313566517@alphaville.dokosmarshall.org> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary=bcaec547c8115f349904aab386fd Return-path: Received: from eggs.gnu.org ([140.186.70.92]:49395) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1QtgEF-0005q7-HI for emacs-orgmode@gnu.org; Wed, 17 Aug 2011 09:32:25 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1QtgEE-00052I-88 for emacs-orgmode@gnu.org; Wed, 17 Aug 2011 09:32:19 -0400 Received: from mail-vw0-f41.google.com ([209.85.212.41]:51558) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1QtgEE-000528-4h for emacs-orgmode@gnu.org; Wed, 17 Aug 2011 09:32:18 -0400 Received: by vwm42 with SMTP id 42so778773vwm.0 for ; Wed, 17 Aug 2011 06:32:17 -0700 (PDT) In-Reply-To: <21851.1313566517@alphaville.dokosmarshall.org> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: nicholas.dokos@hp.com Cc: Org Mode --bcaec547c8115f349904aab386fd Content-Type: text/plain; charset=ISO-8859-1 On Wed, Aug 17, 2011 at 3:35 AM, Nick Dokos wrote: > Matt Price wrote: > > > Hi, > > > > It's htat time of year again and I'm putting together course syllabi. I > would really love to be able > > to dynamically calculate dates in a spreadsheet, e.g.: > > > > |Topic 1|Date1|Description1| > > |Topic2|Above Date + 7| Description2| > > |Topic3|Above Date + 7| Description3| > > > > Is there a way for me to do that? > > As usual in situations like this, you have to start things off with > a field formula for the first date. The column formula then can be > used to calculate all the *other* rows (field formulas override column > formulas - see section 3.5.6, "Column formulas", in the org manual > or evaluate (info "(org) Column formulas") to get there directly). > > | Topic 1 | <2011-08-17 Wed> | Description1 | > | Topic2 | <2011-08-24 Wed> | Description2 | > | Topic3 | <2011-08-31 Wed> | Description3 | > #+TBLFM: @1$2=<2011-08-17> :: $2 = <@-1$2> + 7 > > The column formula says: get the field from the row above and the same > column (@-1$2), interpret it as a date (<..>) and add 7 (days) to it. > The column formula can be simplified to $2 = <@-1> + 7. > > If you want to increment by e.g. 10 mins, the increment has to be > calculated as a fraction of a day: > > | Topic 1 | <2011-09-18 Sun 10:20> | Description1 | > | Topic2 | <2011-09-18 Sun 10:30> | Description2 | > | Topic3 | <2011-09-18 Sun 10:40> | Description3 | > #+TBLFM: @1$2=<2011-09-18 10:20> :: $2 = <@-1> + 10*(1/24*60)) > > > ah, supercool. thank you!! matt --bcaec547c8115f349904aab386fd Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Aug 17, 2011 at 3:35 AM, Nick Do= kos <nicholas= .dokos@hp.com> wrote:
Matt Price <mopt= op99@gmail.com> wrote:

> Hi,
>
> It's htat time of year again and I'm putting together course s= yllabi.=A0 I would really love to be able
> to dynamically calculate dates in a spreadsheet, e.g.:
>
> |Topic 1|Date1|Description1|
> |Topic2|Above Date + 7| Description2|
> |Topic3|Above Date + 7| Description3|
>
> Is there a way for me to do that?=A0

As usual in situations like this, you have to start things off with a field formula for the first date. The column formula then can be
used to calculate all the *other* rows (field formulas override column
formulas - see section 3.5.6, "Column formulas", in the org manua= l
or evaluate (info "(org) Column formulas") to get there directly)= .

| Topic 1 | <2011-08-17 Wed> | Description1 |
| Topic2 =A0| <2011-08-24 Wed> | Description2 |
| Topic3 =A0| <2011-08-31 Wed> | Description3 |
#+TBLFM: @1$2=3D<2011-08-17> :: $2 =3D <@-1$2> + 7

The column formula says: get the field from the row above and the same
column (@-1$2), interpret it as a date (<..>) and add 7 (days) to it.=
The column formula can be simplified to $2 =3D <@-1> + 7.

If you want to increment by e.g. 10 mins, the increment has to be
calculated as a fraction of a day:

| Topic 1 | <2011-09-18 Sun 10:20> | Description1 |
| Topic2 =A0| <2011-09-18 Sun 10:30> | Description2 |
| Topic3 =A0| <2011-09-18 Sun 10:40> | Description3 |
#+TBLFM: @1$2=3D<2011-09-18 10:20> :: $2 =3D <@-1> + 10*(1/24*6= 0))


ah, supercool.=A0 thank you!!

matt
--bcaec547c8115f349904aab386fd--