Re: spreadsheet table limitations, specifically summing hours?

[Nick <oinksocket@letterboxes.org>]

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)))