date calculation in org table

emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed

* date calculation in org table
@ 2013-05-23  6:43 The Dude
  2013-05-23  7:22 ` Christian Moe
  2013-05-23  8:24 ` Michael Brand
  0 siblings, 2 replies; 6+ messages in thread
From: The Dude @ 2013-05-23  6:43 UTC (permalink / raw)
  To: emacs-orgmode

Hi all,

I'm having hard time to find a solution to my problem.  I've got a
simple table with a dates column where I set an $sdate variable in the
first row.  What I want to achieve is that the dates in the column 4 are
the sum of the date from the row above plus the number of days in column
3 in the same row apart from the field @4$4 which uses the $sdate
variable; thus my naïve column and field formulae:
@4$4=$sdate + $3::$4=@-1$4+$3

--8<---------------cut here---------------start------------->8---
* Test date table

|   | WP | Days | Arrival Date     |
|---+----+------+------------------|
| # |    |      | <2013-06-05 Wed> |
| ^ |    |      | sdate            |
|---+----+------+------------------|
|   |  1 |    4 | <2013-06-09 Sun> |
|   |  - |    2 | <2013-06-11 Tue> |
|   |  2 |    4 | <2013-06-15 Sat> |
|   |  - |    2 | <2013-06-17 Mon> |
|   |  3 |    1 | <2013-06-18 Tue> |
|   |  4 |    2 | <2013-06-20 Thu> |
|   |  5 |    1 | <2013-06-21 Fri> |
|   |  - |    2 | <2013-06-23 Sun> |
|   |  6 |    1 | <2013-06-24 Mon> |
|   |  7 |    2 | <2013-06-26 Wed> |
|   |  - |    1 | <2013-06-27 Thu> |
|   |  8 |    1 | <2013-06-28 Fri> |
|   |  9 |    1 | <2013-06-29 Sat> |
|   |  - |    3 | <2013-07-02 Tue> |
|   | 10 |    3 | <2013-07-05 Fri> |
|   |  - |    1 | <2013-07-06 Sat> |
|   | 11 |    2 | <2013-07-08 Mon> |
|---+----+------+------------------|
|   |    |   33 | <2013-07-08 Mon> |
#+TBLFM: @4$4=$sdate + $3::$4=@-1$4+$3::@21$3=vsum(@II..@III)::@21$4=@III-1::$sdate=<2013-06-05 Wed>
--8<---------------cut here---------------end--------------->8---

The problem is that when I change the $sdate and recalculate the whole
table with `C-u C-u C-c C-c' only the element @4$4 is set.  It seems
like the column formula doesn't apply to the other fields.

So the question is what am I doing wrongly?  Didn't I get how the column
formula works or is it a problem with the date calculation?

Any help would be very much appreciated,
cheers
-- 
-dude

Too brief? Here's why! http://emailcharter.org

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: date calculation in org table
  2013-05-23  6:43 date calculation in org table The Dude
@ 2013-05-23  7:22 ` Christian Moe
  2013-05-23  7:51   ` The Dude
  2013-05-23  8:24 ` Michael Brand
  1 sibling, 1 reply; 6+ messages in thread
From: Christian Moe @ 2013-05-23  7:22 UTC (permalink / raw)
  To: The Dude; +Cc: emacs-orgmode




Hi, Dude,

Not quite sure why that doesn't work (nor gives an error message about
trying to set the same cell). However, it will work if you specify the
cell range:

> @4$4=$sdate + $3::$4=@-1$4+$3
                    ^^   
  @4$4=$sdate + $3::@5$4..@20$4=@-1$4+$3
                    ^^^^^^^^^^^

BTW, it's OK to omit the reference to the same column, so you can save
two characters:

  @4$4=$sdate + $3::@5$4..@20$4=@-1$4+$3
                                   ^^
  @4$4=$sdate + $3::@5$4..@20$4=@-1+$3 

Yours,
Christian

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: date calculation in org table
  2013-05-23  7:22 ` Christian Moe
@ 2013-05-23  7:51   ` The Dude
  0 siblings, 0 replies; 6+ messages in thread
From: The Dude @ 2013-05-23  7:51 UTC (permalink / raw)
  To: emacs-orgmode

-> Christian Moe writes:

> Hi, Dude,

> Not quite sure why that doesn't work (nor gives an error message about
> trying to set the same cell). However, it will work if you specify the
> cell range:

>> @4$4=$sdate + $3::$4=@-1$4+$3
>                     ^^   
>   @4$4=$sdate + $3::@5$4..@20$4=@-1$4+$3
>                     ^^^^^^^^^^^

Yep, that works perfectly.  The only drawback is when I have to add a
new row etc. but I can live with that.  Thanks!

> BTW, it's OK to omit the reference to the same column, so you can save
> two characters:

>   @4$4=$sdate + $3::@5$4..@20$4=@-1$4+$3
>                                    ^^
>   @4$4=$sdate + $3::@5$4..@20$4=@-1+$3 

Got it.

> Yours,
> Christian

cheers
-- 
-dude

Too brief? Here's why! http://emailcharter.org

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: date calculation in org table
  2013-05-23  6:43 date calculation in org table The Dude
  2013-05-23  7:22 ` Christian Moe
@ 2013-05-23  8:24 ` Michael Brand
  2013-05-23 14:47   ` The Dude
  1 sibling, 1 reply; 6+ messages in thread
From: Michael Brand @ 2013-05-23  8:24 UTC (permalink / raw)
  To: The Dude; +Cc: Org Mode

Hi Rafal

On Thu, May 23, 2013 at 8:43 AM, The Dude <rafal.kowalski@mac.com> wrote:

> The problem is that when I change the $sdate and recalculate the whole
> table with `C-u C-u C-c C-c' only the element @4$4 is set.  It seems
> like the column formula doesn't apply to the other fields.

This I consider a bug, minimal example:

| # | a | a |
|   | b |   |
#+TBLFM: $3 = $2

|   | a | a |
|   | b | b |
#+TBLFM: $3 = $2

> |   | WP | Days | Arrival Date     |
> |---+----+------+------------------|
> | # |    |      | <2013-06-05 Wed> |
> | ^ |    |      | sdate            |
> |---+----+------+------------------|
> |   |  1 |    4 | <2013-06-09 Sun> |
> |   |  - |    2 | <2013-06-11 Tue> |
> |   |  2 |    4 | <2013-06-15 Sat> |
> |   |  - |    2 | <2013-06-17 Mon> |
> |   |  3 |    1 | <2013-06-18 Tue> |
> |   |  4 |    2 | <2013-06-20 Thu> |
> |   |  5 |    1 | <2013-06-21 Fri> |
> |   |  - |    2 | <2013-06-23 Sun> |
> |   |  6 |    1 | <2013-06-24 Mon> |
> |   |  7 |    2 | <2013-06-26 Wed> |
> |   |  - |    1 | <2013-06-27 Thu> |
> |   |  8 |    1 | <2013-06-28 Fri> |
> |   |  9 |    1 | <2013-06-29 Sat> |
> |   |  - |    3 | <2013-07-02 Tue> |
> |   | 10 |    3 | <2013-07-05 Fri> |
> |   |  - |    1 | <2013-07-06 Sat> |
> |   | 11 |    2 | <2013-07-08 Mon> |
> |---+----+------+------------------|
> |   |    |   33 | <2013-07-08 Mon> |
> #+TBLFM: @4$4=$sdate + $3::$4=@-1$4+$3::@21$3=vsum(@II..@III)::@21$4=@III-1::$sdate=<2013-06-05 Wed>

Let me suggest one more variant. No row numbers, uses the fact that
the formula for $3 is not evaluated in the header and it circumvents
the bug:

|   | WP | Days | Arrival Date     |
|   |    |      | <2013-06-05 Wed> |
|---+----+------+------------------|
|   |  1 |    4 | <2013-06-09 Sun> |
|   |  - |    2 | <2013-06-11 Tue> |
|   |  2 |    4 | <2013-06-15 Sat> |
|   |  - |    2 | <2013-06-17 Mon> |
|   |  3 |    1 | <2013-06-18 Tue> |
|   |  4 |    2 | <2013-06-20 Thu> |
|   |  5 |    1 | <2013-06-21 Fri> |
|   |  - |    2 | <2013-06-23 Sun> |
|   |  6 |    1 | <2013-06-24 Mon> |
|   |  7 |    2 | <2013-06-26 Wed> |
|   |  - |    1 | <2013-06-27 Thu> |
|   |  8 |    1 | <2013-06-28 Fri> |
|   |  9 |    1 | <2013-06-29 Sat> |
|   |  - |    3 | <2013-07-02 Tue> |
|   | 10 |    3 | <2013-07-05 Fri> |
|   |  - |    1 | <2013-07-06 Sat> |
|   | 11 |    2 | <2013-07-08 Mon> |
|---+----+------+------------------|
|   |    |   33 | <2013-07-08 Mon> |
#+TBLFM: $4 = @-1 + $3 :: @>$3 = vsum(@I..@II) :: @>$4 = @-1

Michael

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: date calculation in org table
  2013-05-23  8:24 ` Michael Brand
@ 2013-05-23 14:47   ` The Dude
  2013-05-23 15:02     ` Michael Brand
  0 siblings, 1 reply; 6+ messages in thread
From: The Dude @ 2013-05-23 14:47 UTC (permalink / raw)
  To: emacs-orgmode

-> Michael Brand writes:

[...]

> Let me suggest one more variant. No row numbers, uses the fact that
> the formula for $3 is not evaluated in the header and it circumvents
> the bug:

> |   | WP | Days | Arrival Date     |
> |   |    |      | <2013-06-05 Wed> |
> |---+----+------+------------------|
> |   |  1 |    4 | <2013-06-09 Sun> |
> |   |  - |    2 | <2013-06-11 Tue> |
> |   |  2 |    4 | <2013-06-15 Sat> |
> |   |  - |    2 | <2013-06-17 Mon> |
> |   |  3 |    1 | <2013-06-18 Tue> |
> |   |  4 |    2 | <2013-06-20 Thu> |
> |   |  5 |    1 | <2013-06-21 Fri> |
> |   |  - |    2 | <2013-06-23 Sun> |
> |   |  6 |    1 | <2013-06-24 Mon> |
> |   |  7 |    2 | <2013-06-26 Wed> |
> |   |  - |    1 | <2013-06-27 Thu> |
> |   |  8 |    1 | <2013-06-28 Fri> |
> |   |  9 |    1 | <2013-06-29 Sat> |
> |   |  - |    3 | <2013-07-02 Tue> |
> |   | 10 |    3 | <2013-07-05 Fri> |
> |   |  - |    1 | <2013-07-06 Sat> |
> |   | 11 |    2 | <2013-07-08 Mon> |
> |---+----+------+------------------|
> |   |    |   33 | <2013-07-08 Mon> |
> #+TBLFM: $4 = @-1 + $3 :: @>$3 = vsum(@I..@II) :: @>$4 = @-1

That's a very nice variant and works well.  Thanks.  

BTW, what is the difference between `C-u C-c C-c' and `C-u C-*'?  I
thought they're doing the same thing.

> Michael

cheers
-- 
-dude

Too brief? Here's why! http://emailcharter.org

^ permalink raw reply	[flat|nested] 6+ messages in thread

* Re: date calculation in org table
  2013-05-23 14:47   ` The Dude
@ 2013-05-23 15:02     ` Michael Brand
  0 siblings, 0 replies; 6+ messages in thread
From: Michael Brand @ 2013-05-23 15:02 UTC (permalink / raw)
  To: The Dude; +Cc: Org Mode

Hi Rafal

On Thu, May 23, 2013 at 4:47 PM, The Dude <rafal.kowalski@mac.com> wrote:
> BTW, what is the difference between `C-u C-c C-c' and `C-u C-*'?  I
> thought they're doing the same thing.

- `C-u C-c *' or `C-u C-c C-c': recalculate once
- `C-u C-u C-c *' or `C-u C-u C-c C-c' (`org-table-iterate'): repeat
  recalculation until no further changes occur
See http://orgmode.org/org.html#Updating-the-table

Michael

^ permalink raw reply	[flat|nested] 6+ messages in thread

end of thread, other threads:[~2013-05-23 15:04 UTC | newest]

Thread overview: 6+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2013-05-23  6:43 date calculation in org table The Dude
2013-05-23  7:22 ` Christian Moe
2013-05-23  7:51   ` The Dude
2013-05-23  8:24 ` Michael Brand
2013-05-23 14:47   ` The Dude
2013-05-23 15:02     ` Michael Brand

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).