Using your own Emacs Lisp functions in Org Mode table calculations: easier dosage totals

Posted: - Modified: | emacs, org, elisp

UPDATE 2015-06-17: In the comments below, Will points out that if you use proper dates ([yyyy-mm-dd] instead of yyyy-mm-dd), Org will do the date arithmetic for you. Neato! Here’s what Will said:

Hi Sacha. Did you know you can do date arithmetic directly on org’s inactive or active timestamps? It can even give you an answer in fractional days if the time of day is different in the two timestamps:

| Start                  | End                    | Interval |
|------------------------+------------------------+----------|
| [2015-06-16 Tue]       | [2015-06-23 Tue]       |        7 |
| <2015-06-13 Sat>       | <2015-06-15 Mon>       |        2 |
| [2015-06-10 Wed 20:00] | [2015-06-17 Wed 08:00] |      6.5 |
#+TBLFM: $3=$2 - $1 

Here’s my previous convoluted way of doing things… =)
—-

I recently wrote about calculating how many doses you need to buy using an Org Mode table. On reflection, it’s easier and more flexible to do that calculation using an Emacs Lisp function instead of writing a function that processes and outputs entire tables.

First, we define a function that calculates the number of days between two dates, including the dates given. I put this in my Emacs config.

(defun my/org-days-between (start end)
  "Number of days between START and END.
This includes START and END."
  (1+ (- (calendar-absolute-from-gregorian (org-date-to-gregorian end))
         (calendar-absolute-from-gregorian (org-date-to-gregorian start)))))

Here’s the revised table. I moved the “Needed” column to the left of the medication type because this makes it much easier to read and confirm.

| Needed | Type         | Per day |      Start |        End | Stock |
|--------+--------------+---------+------------+------------+-------|
|     30 | Medication A |       2 | 2015-06-16 | 2015-06-30 |     0 |
|      2 | Medication B |     0.1 | 2015-06-16 | 2015-06-30 |   0.2 |
#+TBLFM: @2$1..@>$1='(ceiling (- (* (my/org-days-between $4 $5) (string-to-number $3)) (string-to-number $6)))

C-c C-c on the #+TBLFM: line updates the values in column 1.

@2$1..@>$1 means the cells from the second row (@2) to the last row (@>) in the first column ($1).  '  tells Org to evaluate the following expression as Emacs Lisp, substituting the values as specified ($4 is the fourth column’s value, etc.).

The table formula calculates the value of the first column (Needed) based on how many you need per day, the dates given (inclusive), and how much you already have in stock. It rounds numbers up by using the ceiling function.

Because this equation uses the values from each row, the start and end date must be filled in for all rows. To quickly duplicate values downwards, set org-table-copy-increment to nil, then use S-return (shift-return) in the table cell you want to copy. Keep typing S-return to copy more.

This treats the calculation inputs as strings, so I used string-to-number to convert some of them to numbers for multiplication and subtraction. If you were only dealing with numbers, you can convert them automatically by using the ;N flag, like this:

| Needed | Type         | Per day | Days | Stock |
|--------+--------------+---------+------+-------|
|      6 | Medication A |       2 |    3 |     0 |
|      1 | Medication B |     0.1 |    3 |   0.2 |
#+TBLFM: @2$1..@>$1='(ceiling (- (* $3 $4) $5)));N
You can comment with Disqus or you can e-mail me at sacha@sachachua.com.