From mboxrd@z Thu Jan 1 00:00:00 1970 From: Carsten Dominik Subject: Re: spreadsheet table limitations, specifically summing hours? Date: Sat, 15 May 2010 16:27:19 +0200 Message-ID: <1082EFD6-82C3-431B-BC1F-6C97EFA86705@gmail.com> References: <4BED25AD.3080203@letterboxes.org> <4BED61D0.4040404@noodlefactory.co.uk> Mime-Version: 1.0 (Apple Message framework v936) Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Return-path: Received: from [140.186.70.92] (port=35014 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1ODIKr-0002iy-Jo for emacs-orgmode@gnu.org; Sat, 15 May 2010 10:27:26 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1ODIKp-0000Jd-Nl for emacs-orgmode@gnu.org; Sat, 15 May 2010 10:27:25 -0400 Received: from mail-ew0-f216.google.com ([209.85.219.216]:35811) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1ODIKp-0000JU-8s for emacs-orgmode@gnu.org; Sat, 15 May 2010 10:27:23 -0400 Received: by ewy8 with SMTP id 8so1073711ewy.8 for ; Sat, 15 May 2010 07:27:22 -0700 (PDT) In-Reply-To: <4BED61D0.4040404@noodlefactory.co.uk> 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: Nick Stokoe Cc: emacs-orgmode@gnu.org Hi Nick, what is the purpose of resending this message? - Carsten On May 14, 2010, at 4:44 PM, Nick Stokoe 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