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