Time constant in emacs org-mode formula

Question

Is there any way to have time constant in org spreadsheet formula? For example, I would like to have something like this:

@>$3=@>$5-'08:00:00'*vcount(@2..@-1)

to calculate how much longer I've been at work than i have to be :) The @>$5 is the total sum of hours I've been at work and @2..@-1 are the rows with days I've been working

Thanks a lot

Answer 1

It may help you:

|     16:00 |  1 |     8:00 |
|  1d 16:00 |  3 |    16:00 |
|      8:00 |  3 |   -16:00 |
|     -8:00 |  2 | -1d 0:00 |
| -1d 16:00 | -6 |     8:00 |
|      8:00 |  1 |     0:00 |
#+TBLFM: $3='(calculate-hours $1 $2 8)

(defun calculate-hours (sumhours numdays hours-per-day)
  (if (string-match
       "\\(-*?\\)\\([0-9]*?\\)\\(?:d \\)*\\([0-9]+\\):\\([0-9]+\\)"
       sumhours)
      (let* ((input-sign (match-string 1 sumhours))
         (total-days
          (string-to-number (match-string 2 sumhours)))
         (total-hours
          (+ (* total-days 24)
         (string-to-number (match-string 3 sumhours))
         (/ (string-to-number (match-string 4 sumhours)) 60.0)))
         (forecast-hours
          (- (if (string-equal input-sign "-")
             (* -1 total-hours)
           total-hours)
         (* hours-per-day (string-to-number numdays))))
         (sign (if (>= (signum forecast-hours) 0) "" "-"))
         (forecast-hours (abs forecast-hours)))
    (if (>= forecast-hours 24)
        (let ((forecast-days
           (truncate (/ forecast-hours 24))))
          (concat
           sign
           (number-to-string forecast-days)
           "d "
           (number-to-string
        (- (/ (truncate (* forecast-hours 100)) 100) (* forecast-days 24)))
           ":00"))
      (concat
       sign
       (number-to-string
        (/ (truncate (* forecast-hours 100)) 100)) ":00"))) 0.0))