Solution with calc hms duration strings
If you are a bit more flexible with the duration format you can use the hms form of calc. The advantage is that you can further process these values with calc formulas. For an example you can do the total sum of the durations.
The following lisp function avoids truncation errors in the time calculations. You can copy it to your init file if you like.
"Start date" and "End date" can also differ in day. Full days are counted with worktime-per-day hours. Only business days are counted.
(defun calcFunc-dateDiffToHMS (date1 date2 worktime-per-day)
"Calculate the difference of DATE1 and DATE2 in HMS form.
Each day counts with WORKTIME-PER-DAY hours."
(cl-labels ((dateTrunc (date)
(calcFunc-date (calcFunc-year date)
(calcFunc-month date)
(calcFunc-day date)))
(datep (date)
(and (listp date)
(eq (car date) 'date))))
(if (and (datep date1)
(datep date2))
(let* ((business-days (calcFunc-bsub
(dateTrunc date1)
(dateTrunc date2))))
(calcFunc-add
(calcFunc-hms (calcFunc-mul business-days worktime-per-day) 0 0)
(calcFunc-sub (calcFunc-time date1) (calcFunc-time date2)))
)
0)))
Example for the usage:
| | Start date | End date | State | Duration |
|---+------------------------+------------------------+-------+------------|
| # | <2019-01-02 Wed 10:30> | <2019-01-02 Wed 12:35> | Taken | 2@ 5' 0" |
| # | <2019-01-02 Thu 13:00> | <2019-01-31 Thu 18:00> | Taken | 173@ 0' 0" |
|---+------------------------+------------------------+-------+------------|
| | | | | 175@ 5' 0" |
#+TBLFM: $5=dateDiffToHMS($3,$2,8)::@>$>=vsum(@I$>..@II$>)
Simple method to get colon-separated duration strings
A simple but hacky way to get a format that is close to your desired format would be to set:
(setq org-calc-default-modes (plist-put org-calc-default-modes 'calc-hms-format "%s:%s:%s"))
Example for the usage:
| | Start date | End date | State | Duration |
|---+------------------------+------------------------+-------+----------|
| # | <2019-01-02 Wed 10:30> | <2019-01-02 Wed 12:35> | Taken | 2:5:0 |
| # | <2019-01-02 Thu 13:00> | <2019-01-31 Thu 18:00> | Taken | 173:0:0 |
|---+------------------------+------------------------+-------+----------|
| | | | | #ERROR |
#+TBLFM: $5=dateDiffToHMS($3,$2,8)::@>$>=vsum(@I$>..@II$>)
The example already shows the benefits and the problems. The benefit is that you get the colon-separated form of the duration string. The problems are that
- one-digit numbers are not padded with zeros and
- one cannot use the colon-spearated duration strings for further calculations such as the total sum in the last cell.
More complicated but very general solution
The following lisp code defines filters for org table formulas in the customizable variable org-table-filters. These filters can be used for filtering the input values of table formulas after reading them off the table and for filtering the output values of those formulas before writing them into the table.
Two filters are currently pre-defined:
org-to-hms for transforming org duration strings like 2:05:00 into calc hms strings like 2@ 5' 0"hms-to-org for the inverse operation of org-to-hms
Thereby org-to-hms and hms-to-org are actually the filter-ids (short FID). A FID is a shortcut for the actual function defined in org-table-filters.
You can use <(FID) and >(FID) as equation modes to apply the filter FID as input and output filter, respectively.
There follows the required Elisp code and afterwards an application example.
If you like the code you can copy and paste it into your init file.
(defcustom org-table-filters '(("org-to-hms" . org-time-string-to-calc-hms)
("hms-to-org" . org-calc-hms-to-org-time-string))
"Alist of filters for org table formulas.
They can be applied for reading the arguments and writing the results.
The `car' of each member is the identifier of the filter
the `cdr' is the function to be called."
:group 'org-table
:type '(repeat (cons (string :tag "Identifier of the filter") (symbol "Filter function"))))
(defun org-calc-hms-to-org-time-string (str)
"Transform calc hms duration to org time string in STR and visa versa."
(if (string-match "-?\\(?:\\([0-9]+\\)@\\)? *\\([0-9]+\\)' *\\([0-9.eE+-]+\\)\"" str)
(let ((hour (string-to-number (or (match-string 1 str) "0")))
(min (string-to-number (match-string 2 str)))
(sec (string-to-number (match-string 3 str))))
(format "%d:%02d:%02d" hour min sec))
str))
(defun org-time-string-to-calc-hms (str)
"Transform org time string STR into calc hms format."
(if (string-match "\\(-?\\)\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" str)
(let ((minus (match-string 1 str))
(hour (string-to-number (or (match-string 2 str) "0")))
(min (string-to-number (match-string 3 str)))
(sec (string-to-number (match-string 4 str))))
(if minus
(format "-(%d@ %d' %d\")" hour min sec)
(format "%d@ %d' %d\"" hour min sec)))
str))
(defmacro org-table-filter (flags kind)
"Return filter in FLAGS.
KIND may be \"<\" for input filter and \">\" for output filter.
If there is no filter of the requested kind in FLAGS return `identity'."
`(if (string-match ,(concat kind "(\\([^)]+\\))") ,flags)
(prog1
(or (cdr (assoc-string (match-string 1 ,flags) org-table-filters)) #'identity)
(setq ,flags (replace-match "" nil nil ,flags)))
#'identity))
(defun org-table-input-filter (str filter)
"Apply FILTER to string STR.
STR can also be a list.
In that case apply \\fn to each element of that list."
(cond
((listp str)
(mapcar (lambda (el)
(org-table-input-filter el filter))
str))
((stringp str)
(funcall filter str))
(t str)))
(defun org-table-eval-formula-filters (oldfun _arg equation &rest _args)
"Apply filters to the arguments and the result of a table EQUATION.
This is an :override advice for OLDFUN `org-table-eval-formula'."
(cl-multiple-value-bind
(eq flags) (split-string equation ";")
(if (stringp flags)
(let ((input-filter (org-table-filter flags "<"))
(output-filter (org-table-filter flags ">")))
(cl-letf* ((old-justify-field-maybe (symbol-function 'org-table-justify-field-maybe))
(old-table-make-reference (symbol-function 'org-table-make-reference))
((symbol-function 'org-table-make-reference)
(lambda (elements &rest __args)
(apply old-table-make-reference
(org-table-input-filter elements input-filter)
__args)))
((symbol-function 'org-table-justify-field-maybe)
(lambda (&optional new)
(funcall old-justify-field-maybe (and (stringp new)
(funcall output-filter new))))))
(apply oldfun _arg (concat eq ";" flags) _args)))
(apply oldfun _arg equation _args))))
(advice-add 'org-table-eval-formula :around #'org-table-eval-formula-filters)
Usage example:
| | Start date | End date | State | Duration |
|---+------------------------+------------------------+-------+----------|
| # | <2019-01-02 Wed 10:30> | <2019-01-02 Wed 12:35> | Taken | 2:05:00 |
| # | <2019-01-02 Thu 13:00> | <2019-01-03 Thu 18:00> | Taken | 13:00:00 |
| # | <2019-01-03 Thu 13:00> | <2019-01-03 Thu 13:30> | Taken | 0:30:00 |
|---+------------------------+------------------------+-------+----------|
| | | | | 15:35:00 |
#+TBLFM: $5=dateDiffToHMS($3,$2,8);>(hms-to-org)::@>$>=vsum(@I$>..@II$>);>(hms-to-org)<(org-to-hms)
The results have exactly the required format and calculating the total sum of org durations is possible.
Tested with...
emacs-version: GNU Emacs 26.1 (build 1, x86_64-unknown-cygwin, GTK+ Version 3.22.28) of 2018-05-28org-version: 9.1.14
