Org date from spreadsheet

Question

I am able to compute dates thus:

#+NAME: mytbl
| # | [2014-12-14 Sun] | 30 | <2015-01-13 Tue> |
#+TBLFM: $4=$2+$3;D

but what I want to then do is make that date appear as an agenda date, something like:

DEADLINE: $remote(mytbl,@1$4)

but that doesn't work. I can't find any syntax, even a macro, that will allow anything but a literal date for an agenda entry.

Any ideas?

There is an overlay function used to make the timestamp look like something other than what it really is. It would probably be easiest to just tweak that functionality and make it overlay your own format -- underneath the overlay will be the standard timestamp which is used by numerous functions throughout `org-mode` and cannot easily be changed (in my opinion) -- there is even a note in the doc-string for the timestamp variable that advises against tweaking with it. — lawlist, Dec 23 '14 at 23:46
`org-time-stamp-formats` is a variable defined in `org.el`. Its value is `("<%Y-%m-%d %a>" . "<%Y-%m-%d %a %H:%M>")` This variable may be risky if used as a file-local variable. Documentation: Formats for `format-time-string` which are used for time stamps. **It is not recommended to change this constant.** See also, the custom time stamp format: http://orgmode.org/manual/Custom-time-format.html#Custom-time-format — lawlist, Dec 23 '14 at 23:49
I think I did not explain it correctly. I am not trying to change the look of the timestamp. I am fine with the normal <%Y-%m-%d %a> form. What I want to end up with is the equivalent of "DEADLINE: <2015-01-13 Tue>" but rather than type it in myself I want it to come from the spreadsheet, which has been computed. Is there a way to transport that information? Or is there another way to provide something after "DEADLINE:" (like maybe elisp, where I can compute the date) where it will be interpreted as a date? — jtgd, Dec 25 '14 at 02:26
I do not have an answer, but would like to throw out some ideas. In general, the org-agenda buffer is just a pretty copy and paste from the raw org-agenda-files. In a recent thread, I scroll through the org-agenda-files backwards and stop at the headline of each entry to treat the time-stamp as if it were x number of days AD to do some further calculations (e.g., whether something is due today, or overdue). http://emacs.stackexchange.com/a/5700/2287 In theory, you could stop at each heading and do your calculations and generate a custom agenda buffer. This is non-trivial, but interesting. — lawlist, Dec 25 '14 at 05:21
Well thanks for your response lawlist, I figured there had to be a way to get a date into the agenda besides typing in a literal string. Maybe I can dig into the functions used when the agenda is being generated. I wish there was something like an 'eval' function that would rescan the output of a function. — jtgd, Dec 25 '14 at 20:27

Melioratus · Answer 1 · 2014-12-28T18:01:20.723

Try this:

Compute the deadline in a spreadsheet a.k.a table.

#+NAME: mytbl
| # | [2014-12-14 Sun] | 30 | <2015-01-13 Tue> |
| # | [2014-12-15 Mon] | 10 | <2014-12-25 Thu> |
#+TBLFM: $4=$2+$3;D

Create a new 1x1 table that references computed deadline from first table using remote(NAME-OR-ID,REF) function.

#+NAME: mydeadline
| <2015-01-13 Tue> |
#+TBLFM: $1=remote(mytbl,@1$4)

Pass 1x1 deadline table as variable via header :var name=value to named SRC block, e.g. set_deadline elisp code block below.

#+NAME: set_deadline
#+HEADER: :var the_date=mydeadline
#+HEADER: :results  raw replace output 
#+begin_src elisp
   (princ (format "DEADLINE: %s" ( car (car the_date))))
#+end_src

Evaluate the SRC block using C-c C-c and the deadline will be added to the org-mode file under a #+RESULTS: block.

Adding the :results raw header to code block forces the output into format org-mode will recognize as regular org statement.

#+RESULTS: set_deadline
DEADLINE: <2015-01-13 Tue>

As an added bonus, using a named src blocks also encourages code reuse via inline function calls.

As before, evaluate each inline function using C-c C-c and a new deadline will be added to the org-mode file.

#+NAME: first-deadline
call_set_deadline(the_date=mydeadline)[ :results raw ]

#+RESULTS: first-deadline
DEADLINE: <2015-01-13 Tue>

#+NAME: my-other-deadline
| <2014-12-25 Thu> |
#+TBLFM: $1=remote(mytbl,@2$4)

#+NAME: second-deadline
call_set_deadline(the_date=my-other-deadline)[ :results raw ]

#+RESULTS: second-deadline
DEADLINE: <2014-12-25 Thu>

Hope that helped!

Note: This code was tested using the following versions of emacs and org-mode.

GNU Emacs 24.4.1 (x86_64-apple-darwin14.0.0, NS apple-appkit-1343.14)
Org-mode version 8.2.10 (8.2.10-29-g89a0ac-elpa)

@jtgd **Thank you! You made my day!** In regards to the `replace` not behaving as expected, try updating `raw` header with `drawer` header. After I switched, everything worked as expected. Hope that Helped! Thanks for asking your question and your elisp code! — Melioratus, Jan 10 '15 at 03:41
@jtgd - I just posted [another example](http://emacs.stackexchange.com/a/7260/388) that uses tiny code blocks written in eLisp, Perl, bash, Python and Ruby to turn text into a list of checkboxes. — Melioratus, Jan 11 '15 at 16:38
Oh, thanks again! The `drawer` thing works perfect now. So much to learn about org-mode, nice to have wizards like you around. As for your other posted examples I tried one and it works with `raw` without appending. But my application works so I'm happy. — jtgd, Jan 12 '15 at 04:32

score 1 · Answer 2 · answered Jan 09 '15 at 06:54

Thank you so much Melioratus, what an outstanding answer! It's amazing how you used the various mechanisms of Org mode to do this. The most significant part of this for me was :results raw which outputted text to be rescanned by the agenda creation. That was the key.

When I looked at how I could expand this to handle many dates I wondered if I couldn't just do the date calculations in elisp. I'm still very new to elisp but I hacked around a bit and came up with this.

My task is calculating the date my meds run out by adding 30 days to the day I start the bottle. What I did was this.

In my .emacs I wrote some functions:

(defun date-high-low (secs)
  "Split int into high-low words"
  (list (/ secs 65536) (% secs 65536)))

(defun date-plus-days (datestr days)
  "Generate org-style date string from date + days offset"
  (format-time-string "<%Y-%m-%d %a>" (date-high-low (truncate (+ (org-time-string-to-seconds datestr) (* days 86400))))))

(defun org-header-date-plus-days (fmt datestr days)
  "Use above to build final org header entry from format string"
  (princ (format fmt (date-plus-days datestr days))))

I tried to keep these functions generic and not focused on the meds for reusability. There might be better ways to do this, like I said I'm a newbie.

Then in my .org file I do this:

#+HEADER: :results raw output replace
#+begin_src elisp
    (org-header-date-plus-days "** MED LAST DAY Med-1\nDEADLINE: %s\n" "[2014-12-14 Sun]" 30)
    (org-header-date-plus-days "** MED LAST DAY Med-2\nDEADLINE: %s\n" "[2014-12-22 Mon]" 54)
    (org-header-date-plus-days "** MED LAST DAY Med-3\nDEADLINE: %s\n" "[2015-01-02 Fri]" 60)
#+end_src

Nice and neat, one line per instance. Doing C-c C-c on the block generates the headline and DEADLINE with date and it shows up perfectly in the agenda. It works well and I'm happy with the reults. Thanks again for the tip to you and lawlist.

p.s. The only thing that doesn't work is the replace. It always appends to the #+RESULTS block so I have to delete it before generating it. It's minor but I don't understand why.

Org date from spreadsheet

2 Answers2