# orgmode table time string delta with formula

I wish to calculate time string delta as below:

``````| date       | delta |
|------------+-------|
| 2018/12/28 |     0 |
| 2018/12/31 |    -1 |
#+TBLFM: \$2=if(2==@#, 0, (\$1 - @-1\$1); %.0f
``````

The expected answer should be 3 but now it's -1. I do not wish to change the time string format in table.

How can I modify the formula to covert timestring to datetime correctly?

• Look at the first column of your table. What calc does is the computation `2018/(12*18)` which gives `6.00595238096` as a float (dependent on the current calc-mode) and `2018/(12*31)` giving `5.42473118281` as a float. The int-casted result is `-1`. Sep 26, 2019 at 12:30

Just do the conversion into standard iso format and `calc-eval` in Elisp:

``````| date       | delta |
|------------+-------|
| 2018/12/28 |     0 |
| 2018/12/31 |     3 |
#+TBLFM: \$2='(if (= @# 2) 0 (calc-eval (concat "<" (substitute ?- ?/ \$1) ">-<" (substitute ?- ?/  @-1\$1) ">") )); %.0f
``````

You can cleanup the code by defining the following lisp function at an appropriate place (e.g., a source block in your org-file or your init files):

``````(defun my-orgtbl-time-diff (date1 date2)
"Compute the day difference of two dates given in a format like 2019-09-23."
(let* ((date-map (lambda (str)
(and (string-match "\\([0-9]\\{4\\}\\)/\\([0-9]\\{2\\}\\)/\\([0-9]\\{2\\}\\)" str)
(concat "<" (match-string 1 str) "-" (match-string 2 str) "-" (match-string 3 str) ">"))))
(date1 (funcall date-map date1))
(date2 (funcall date-map date2)))
(if (and date1 date2)
(calc-eval (concat date1 "-" date2))
0)))
``````

Employing that function your table formula has the following form.

``````| date       | delta |
|------------+-------|
| 2018/12/28 |     0 |
| 2018/12/31 |     3 |
#+TBLFM: \$2='(my-orgtbl-time-diff \$1 @-1\$1)
``````

Although you said you do not want to change the date format that is what you have to do to use the \$1 syntax in the formula. For that to work, org mode functions will need to know that you are dealing with dates.

The first prerequisite is the brackets around the dates. Secondly, I had to go to full ISO format date for org to recognize the date. That might depend on my settings.

``````| date         | delta |
|--------------+-------|
| [2018-12-28] |     0 |
| [2018-12-31] |     3 |
#+TBLFM: \$2=if(2==@#, 0, (\$1 - @-1\$1); %.0f
``````

The alternative is to put together a very long formula using custom elisp to do the same thing.

• The date field can be an active date `<YYYY-MM-DD>` or an inactive date `[YYYY-MM-DD]` but indeed it cannot be a bare date as the OP wants it: org mode will not recognize it. It also has to be an ISO date: org mode can be customized to output dates in other formats (generally as an overlay of some sort) but it only understands ISO dates on input. Dec 30, 2018 at 12:45