Re: spreadsheet table limitations, specifically summing hours?

[Carsten Dominik <carsten.dominik@gmail.com>]

Hi Nick, these are useful functions, thanks!  Maybe add the to org- 
hacks?

Also, I could think of generalizations:

Allow 94h and 94:16h and 94:16:22h.

I would, in fact, strip the "h" and just use 94:16

- Carsten

On May 14, 2010, at 4:49 PM, Nick wrote:

> Earlier I wrote:
>> There seem to be two problems;
>>
>> c) Summing HH:MM values (which org-table-sum seems to manage -  
>> although oddly
>>    without including the minutes - but not vsum)
>
> Excuse me answering my own question,  but time is money, and so I've  
> hastily
> written my own elisp functions to do this (appended).  Perhaps this  
> will help
> someone else.
>
> Given these functions defined in a .emacs config or similar, I can do:
>
>
>
>
> |   | week ending | hh:mm  | Total H | Total |    VAT | Total+VAT |
> | ! | week        | hhmm   |   hours | total |    vat |           |
> |---+-------------+--------+---------+-------+--------+-----------|
> |   | 28/02/10    | 20:11h |         |       |        |           |
> |   | 07/03/10    | 21:11h |         |       |        |           |
> |   | 14/03/10    | 25:40h |         |       |        |           |
> |   | 21/03/10    | 27:16h |         |       |        |           |
> |---+-------------+--------+---------+-------+--------+-----------|
> | # | 22/03/10    | 94:18h |      94 |  2820 | 493.50 |   3313.50 |
> |---+-------------+--------+---------+-------+--------+-----------|
> |   | 28/03/10    | 26:24h |         |       |        |           |
> |   | 04/04/10    | 21:15h |         |       |        |           |
> |   | 11/04/10    | 23:15h |         |       |        |           |
> |---+-------------+--------+---------+-------+--------+-----------|
> | # | 27/02/10    | 70:54h |      71 |  2130 | 372.75 |   2502.75 |
> |---+-------------+--------+---------+-------+--------+-----------|
> #+TBLFM: $3='(reduce 'my-sum-hhmm '(@-I..@-II))::$4='(round (my-hhmm- 
> to-hours
> $hhmm))::$5=$hours*$hourlyrate::$6=$total*$vatrate;%.2f::$7=$total+ 
> $vat;%.2f
> #+CONSTANTS: vatrate=0.175 hourlyrate=35
>
>
> Although I'd still be interested if someone can show me a better way.
>
> Cheers,
>
> N
>
> ps I wonder if a long set of formulae like this could be split over  
> several
> +TBLFM lines?  The answer seems to be no.
>
> ----
> ;; Parse an HH::MM date into a list containing a pair of numbers,  
> (HH MM)
> (defun my-parse-hhmm (hhmm)
>  (let ((date-re "\\([0-9]+\\):\\([0-9]+\\)h?")
> 	hours
> 	minutes)
>    (unless (string-match date-re hhmm)
>      (error "Argument is not a valid date: '%s'" hhmm))
>    (setq hours (string-to-number (match-string 1 hhmm))
> 	  minutes (string-to-number (match-string 2 hhmm)))
>    (list hours minutes)))
>
> ;; Convert a HH:MM date to a (possibly fractional) number of hours
> (defun my-hhmm-to-hours (hhmm)
>  (let* ((date (my-parse-hhmm hhmm))
> 	(hours (first date))
> 	(minutes (second date)))
>    (+ (float hours) (/ (float minutes) 60.0))))
>
> 	
> ;; Date summing
> ;; This can be used in a table formula like this:
> ;; #+TBLFM: $3='(reduce 'my-sum-dates '(@-I..@-II))
> (defun my-sum-hhmm (a b)
>  (let* (;; parse a
> 	 (a-date (my-parse-hhmm a))
> 	 (a-hours (first a-date))
> 	 (a-minutes (second a-date))
> 	
> 	 ;; parse b
> 	 (b-date (my-parse-hhmm b))
> 	 (b-hours (first b-date))
> 	 (b-minutes (second b-date))
> 	
> 	 ;; add the parts together
> 	 (minutes (+ a-minutes b-minutes))
> 	 (hours (+ a-hours b-hours))
> 	 (carry (floor (/ minutes 60)))
> 	 (remainder (mod minutes 60)))
>    (format "%d:%02dh" (+ hours carry) remainder)))
>
>
>
>
> _______________________________________________
> Emacs-orgmode mailing list
> Please use `Reply All' to send replies to the list.
> Emacs-orgmode@gnu.org
> http://lists.gnu.org/mailman/listinfo/emacs-orgmode

- Carsten