spreadsheet table limitations, specifically summing hours?

spreadsheet table limitations, specifically summing hours?

[Nick]

Hi,

Having used orgmode to track my TODO list and related working hours, I have been
trying to use it to work out my invoices.

Although the spreadsheet is quite neat, I have been having trouble getting it to
do some things.  In particular, after reading the manual I initially thought I
could, but empirically find I can't:

 a) write formulas like below, which both use and assign to column names, e.g.
    $total=$vat+$fee

 b) Use underscores or hyphens (or less surprisingly, spaces) in column names

Correct? Or is there something I've missed?


Anyway, I can work around those, by using column numbers, and not using
underscores etc.; but I'm still trying to discover a workable way of summing the
hours.

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)

 d) writing a formula to sum items above it, ideally summing values in between
    two hlines



[Later: I solved d) as by putting the sums in a separate hline section and using
vsum(@-I..@-II), see below]


|   | week ending | hh:mm  | Fee   | VAT   | Total |
| ! | week        | hours  | fee   | vat   | total |
|---+-------------+--------+-------+-------+-------|
|   | 27/12/09    | 10:19h |       |       |       |
|   | 10/01/10    | 3:00h  |       |       |       |
|   | 17/01/10    | 18:50h |       |       |       |
|   | 24/01/10    | 13:00h |       |       |       |
|---+-------------+--------+-------+-------+-------|
| # | 25/01/10    | ??:??h | ??.?? | ??.?? | ??.?? |
|---+-------------+--------+-------+-------+-------|
|   | 31/01/10    | 19:47h |       |       |       |
|   | 07/02/10    | 19:19h |       |       |       |
|   | 14/02/10    | 23:14h |       |       |       |
|---+-------------+--------+-------+-------+-------|
| # | 22/02/10    | ??:??h | ??.?? | ??.?? | ??.?? |
|---+-------------+--------+-------+-------+-------|
#+TBLFM:
$hours=vsum(@-I..@-II)::$fee=$hours*$hourlyrate;%.2f::$vat=$total*$vatrate;%.2f::total=$vat+$fee
#+CONSTANTS: vatrate=0.175 hourlyrate=35

[Note: my mail client wraps the +TBLFM line]

I suspect there maybe a way of using elisp to sum the hours, but on a cursory
inspection of the source, org-table-sum seems not to be designed for the job,
and I don't see another one I could use off-the-shelf.

(I'm using org-mode v6.35 in emacs 23.0.91.1)
	

Thanks in retrospect for earlier answers and in advance future help.


Cheers,

Nick

Re: spreadsheet table limitations, specifically summing hours?

[Nick Stokoe]

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

Re: spreadsheet table limitations, specifically summing hours?

[Carsten Dominik]

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

Re: spreadsheet table limitations, specifically summing hours?

[Nick]

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

Re: spreadsheet table limitations, specifically summing hours?

[Stephan Schmitt]

Hi Nick,

are you aware of the table editor?  Type C-c ' (single quote) when the 
point is somewhere in the table.

Greetings,
	Stephan

Also sprach Nick:
> ps I wonder if a long set of formulae like this could be split over several
> +TBLFM lines?  The answer seems to be no.

Re: spreadsheet table limitations, specifically summing hours?

[Nick]

Stephan Schmitt wrote:
> are you aware of the table editor?  Type C-c ' (single quote) when the
> point is somewhere in the table.

Thanks - I did use it once or twice, and indeed it does makes it easier to edit
the formulae, but doesn't seem to prevent the resulting +TBLFM line from
disappearing off the side of the screen?

My guess is, since the +TBLFM line is, or can be automatically re-generated, it
is more convenient to parse and re-generate when restricted to one line.

Something I can live with, anyway.

N

Re: spreadsheet table limitations, specifically summing hours?

[Carsten Dominik]

On May 14, 2010, at 11:59 PM, Stephan Schmitt wrote:

> Hi Nick,
>
> are you aware of the table editor?  Type C-c ' (single quote) when  
> the point is somewhere in the table.

And, if you have a longish elisp formula, press TAB in the first line  
of the formula to get the formula spread over multiple lines and  
back.....

- Carsten

>
> Greetings,
> 	Stephan
>
> Also sprach Nick:
>> ps I wonder if a long set of formulae like this could be split over  
>> several
>> +TBLFM lines?  The answer seems to be no.
>
> _______________________________________________
> 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

Re: spreadsheet table limitations, specifically summing hours?

[Carsten Dominik]

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

Re: spreadsheet table limitations, specifically summing hours?

[oinksocket]

Carsten Dominik wrote:
> Hi Nick, these are useful functions, thanks!  Maybe add the to org-hacks?

You mean here?

 http://orgmode.org/worg/org-hacks.php

I didn't know about that, thanks.   My initial question is "how?" but I see
there is some information here:

 http://orgmode.org/worg/worg-about.php

Will need to read up and follow those instructions first.


> 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

Good suggestions, I will see what I can do.

(I'm starting to think it's going to need some test cases, and I'll need to
polish my elisp somewhat to find out how to do that. Are there any suggested
unit-testing schemes for elisp?)

Cheers,

N

Re: spreadsheet table limitations, specifically summing hours?

[Štěpán Němec]

oinksocket@letterboxes.org writes:

> (I'm starting to think it's going to need some test cases, and I'll need to
> polish my elisp somewhat to find out how to do that. Are there any suggested
> unit-testing schemes for elisp?)

http://news.gmane.org/group/gmane.emacs.devel/thread=122140