Re: org table calc and lisp for hh:mm timetable

[Christian Moe <mail@christianmoe.com>]

Hi,

This is ingenious! But I have a different solution that borrows 
conversion functions from org-timer.el.

To avoid an insanely long formula, I'll alias those functions with 
shorter names which don't seem to colide with anything in my Emacs.

#+begin_src emacs-lisp
   (defun sec (arg)
     (org-timer-hms-to-secs arg))

   (defun hms (arg)
     (org-timer-secs-to-hms arg))
#+end_src

Now, just do this:

|    Start |    Lunch |     Back |      End |  Sum    |
|----------+----------+----------+----------+---------|
| 08:00:00 | 12:20:00 | 13:00:00 | 17:00:00 | 8:20:00 |
#+TBLFM: $5='(hms (+ (- (sec $4) (sec $3)) (- (sec $2) (sec $1))))

This already works for me, because my main interest here is in keeping 
track of my jogging, but those seconds are spurious precision for your 
use case, and take up space. So rewrite sec and hms:

#+begin_src emacs-lisp
   (defun sec (arg)
     (if (string-match org-timer-re arg)
         (org-timer-hms-to-secs arg)
       (org-timer-hms-to-secs (concat arg ":00"))))

   (defun hms (arg)
     (if (integerp (/ arg 60))
         (substring (org-timer-secs-to-hms arg) 0 -3)
       (org-timer-secs-to-hms arg)))
#+end_src

Now sec will correctly convert hh:mm stamps, too, and hms will convert 
to hh:mm format if the argument is in whole minutes, otherwise to 
hh:mm:ss. So:

| Start | Lunch |  Back |   End |  Sum |
|-------+-------+-------+-------+------|
| 08:00 | 12:20 | 13:00 | 17:00 | 8:20 |
#+TBLFM: $5='(hms (+ (- (sec $4) (sec $3)) (- (sec $2) (sec $1))))


Yours,
Christian


On 3/15/11 8:49 PM, Martin Halder wrote:
>
>>> I was trying to generate a simple table with time format "hh:mm" and
>>> auto calculate daily sum.. clocking working time was too much so I
>>> thought this would be easy but ended up with the following.. it works
>>> but is not beautiful (apply formula twice and same information
>>> multiple times) and I would like to get rid of the "hms", "hh" and
>>> "mm" columns and therefore call "hmconcat" directly somehow.. Any help
>>> is highly appreciated..
>>>
>>> Thanks,
>>> Martin
>>>
>>> | Date             | Start | Lunch |  Back |   End |   Sum | hms       | hh | mm |
>>> |------------------+-------+-------+-------+-------+-------+-----------+----+----|
>>> | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" |  8 | 20 |
>>> #+TBLFM: $6='(hmconcat $8 $9)::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7)
>>>
>>> (defun hmconcat (hh mm) (interactive)
>>>     (if (>  (length hh) 1)
>>>         (setq temp (concat hh ":")) (setq temp (concat "0" hh ":")))
>>>     (if (>  (length mm) 1)
>>>         (concat temp mm) (concat temp "0" mm)))
>>
>> Martin,
>>
>> glad to see you got further with this!
>>
>> You can definitely get rid of hmconcat by using a combination of
>> string-to-number and format (and I'm sure it's possible to get this done
>> with simpler elisp):
>>
>> --8<---------------cut here---------------start------------->8---
>>
>> | Date             | Start | Lunch |  Back |   End |   Sum | hms       | hh | mm |
>> |------------------+-------+-------+-------+-------+-------+-----------+----+----|
>> | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" |  8 | 20 |
>> #+TBLFM: $6='(format "%02d:%02d" (string-to-number $8) (string-to-number $9))::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7)
>> --8<---------------cut here---------------end--------------->8---
>
> Hi Eric,
>
> yes and thanks for the previous help, too.. the good old printf.. I would like to pass the result of time() directly to a lisp function, like:
> #+TBLFM: $6='(coolfunc (time(...$5)-time(...$4)))
>
> If I would know how to pass the result, eg as a string, to a lisp function I could sort it out, I guess.
>
> Thanks,
> Martin
>