From mboxrd@z Thu Jan 1 00:00:00 1970 From: Nick Subject: Re: spreadsheet table limitations, specifically summing hours? Date: Fri, 14 May 2010 15:49:33 +0100 Message-ID: <4BED62FD.1010502@letterboxes.org> References: <4BED25AD.3080203@letterboxes.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Return-path: Received: from [140.186.70.92] (port=55878 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1OCwCp-0004PJ-As for emacs-orgmode@gnu.org; Fri, 14 May 2010 10:49:40 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1OCwCk-00069O-V8 for emacs-orgmode@gnu.org; Fri, 14 May 2010 10:49:37 -0400 Received: from udon.noodlefactory.co.uk ([80.68.88.167]:40060) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1OCwCk-00069I-P1 for emacs-orgmode@gnu.org; Fri, 14 May 2010 10:49:34 -0400 Received: from 87-194-154-6.bethere.co.uk ([87.194.154.6] helo=[192.168.0.100]) by udon.noodlefactory.co.uk with esmtpsa (SSL 3.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1OCwCj-00058C-Vr for emacs-orgmode@gnu.org; Fri, 14 May 2010 15:49:34 +0100 In-Reply-To: <4BED25AD.3080203@letterboxes.org> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.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)))