Conditional in table formula with times

Conditional in table formula with times

[Loris Bennett]

Hi,

I'm trying to keep track of total time spend at work, but I am having
trouble with a conditional in table formula:

| *Day*            | *Came* | *Went* | *Worked* | *Required* |   *Diff* |
|------------------+--------+--------+----------+------------+----------|
| [2014-01-06 Mon] |   8:00 |  17:00 | 09:00:00 |   00:00:00 | 09:00:00 |
| [2014-01-07 Tue] |        |        | 00:00:00 |   00:00:00 | 00:00:00 |
| [2014-01-08 Wed] |   8:10 |  16:30 | 08:20:00 |   00:00:00 | 08:20:00 |
| [2014-01-09 Thu] |   7:55 |  17:05 | 09:10:00 |   00:00:00 | 09:10:00 |
| [2014-01-10 Fri] |   8:00 |  17:05 | 09:05:00 |   00:00:00 | 09:05:00 |
#+TBLFM: $4=$3-$2;T::$5=if($4 > 0,"8:18:00","00:00:00");T::$6=$4-$5;T

Debugging the formula gives me for @2$5:

,---------------------------------------------
| Substitution history of formula
| Orig:   if($4 > 0,"8:18:00","00:00:00");T
| $xyz->  if($4 > 0,"8:18:00","00:00:00")
| @r$c->  if($4 > 0,"8:18:00","00:00:00")
| $1->    if((32400) > 0,"8:18:00","00:00:00")
| Result: 00:00:00
| Format: NONE
| Final:  00:00:00
`---------------------------------------------

and for @3$5:

,------------------------------------------
| Substitution history of formula
| Orig:   if($4 > 0,"8:18:00","00:00:00");T
| $xyz->  if($4 > 0,"8:18:00","00:00:00")
| @r$c->  if($4 > 0,"8:18:00","00:00:00")
| $1->    if((0) > 0,"8:18:00","00:00:00")
| Result: 00:00:00
| Format: NONE
| Final:  00:00:00
`------------------------------------------

Can anyone enlighten me as to why I get "00:00:00" in the first case?

Cheers,

Loris

-- 
This signature is currently under construction.

Re: Conditional in table formula with times

[Sebastien Vauban]

"Loris Bennett" wrote:
> I'm trying to keep track of total time spend at work, but I am having
> trouble with a conditional in table formula:
>
> | *Day*            | *Came* | *Went* | *Worked* | *Required* |   *Diff* |
> |------------------+--------+--------+----------+------------+----------|
> | [2014-01-06 Mon] |   8:00 |  17:00 | 09:00:00 |   00:00:00 | 09:00:00 |
> | [2014-01-07 Tue] |        |        | 00:00:00 |   00:00:00 | 00:00:00 |
> | [2014-01-08 Wed] |   8:10 |  16:30 | 08:20:00 |   00:00:00 | 08:20:00 |
> | [2014-01-09 Thu] |   7:55 |  17:05 | 09:10:00 |   00:00:00 | 09:10:00 |
> | [2014-01-10 Fri] |   8:00 |  17:05 | 09:05:00 |   00:00:00 | 09:05:00 |
> #+TBLFM: $4=$3-$2;T::$5=if($4 > 0,"8:18:00","00:00:00");T::$6=$4-$5;T
>
> Can anyone enlighten me as to why I get "00:00:00" in the first case?

Not answering your question...

Just wanted to mention that, for this type of task, you probably should
have a look at the "clocking" mechanisms (C-c C-x C-i and the like), and
use them to generate tables like the above, or bills for clients, etc.

Using proper clocking brings you many advantages, whose main (IMO) is
the "clock check" feature, where you can check that you don't have
unintended clocking gaps during the day, nor clocking overlap...

Best regards,
  Seb

-- 
Sebastien Vauban

Re: Conditional in table formula with times

[Loris Bennett]

Hi Seb,

"Sebastien Vauban" <sva-news@mygooglest.com>
writes:

> "Loris Bennett" wrote:
>> I'm trying to keep track of total time spend at work, but I am having
>> trouble with a conditional in table formula:
>>
>> | *Day*            | *Came* | *Went* | *Worked* | *Required* |   *Diff* |
>> |------------------+--------+--------+----------+------------+----------|
>> | [2014-01-06 Mon] |   8:00 |  17:00 | 09:00:00 |   00:00:00 | 09:00:00 |
>> | [2014-01-07 Tue] |        |        | 00:00:00 |   00:00:00 | 00:00:00 |
>> | [2014-01-08 Wed] |   8:10 |  16:30 | 08:20:00 |   00:00:00 | 08:20:00 |
>> | [2014-01-09 Thu] |   7:55 |  17:05 | 09:10:00 |   00:00:00 | 09:10:00 |
>> | [2014-01-10 Fri] |   8:00 |  17:05 | 09:05:00 |   00:00:00 | 09:05:00 |
>> #+TBLFM: $4=$3-$2;T::$5=if($4 > 0,"8:18:00","00:00:00");T::$6=$4-$5;T
>>
>> Can anyone enlighten me as to why I get "00:00:00" in the first case?
>
> Not answering your question...
>
> Just wanted to mention that, for this type of task, you probably should
> have a look at the "clocking" mechanisms (C-c C-x C-i and the like), and
> use them to generate tables like the above, or bills for clients, etc.
>
> Using proper clocking brings you many advantages, whose main (IMO) is
> the "clock check" feature, where you can check that you don't have
> unintended clocking gaps during the day, nor clocking overlap...
>
> Best regards,
>   Seb

I do already use clocking for certain areas of activity.  However, I
would need "nested" clocking, i.e. a clock for "at work/not at work"
which I can clock into at the beginning of the day an out of at the end
of the day and within that the ability to clock into to and out of
various activities.  Is that possible?

Cheers,

Loris

-- 
This signature is currently under construction.

Re: Conditional in table formula with times

[Sebastien Vauban]

"Loris Bennett" wrote:
> "Sebastien Vauban" writes:
>> "Loris Bennett" wrote:
>>> I'm trying to keep track of total time spend at work, but I am having
>>> trouble with a conditional in table formula:
>>>
>>> | *Day*            | *Came* | *Went* | *Worked* | *Required* |   *Diff* |
>>> |------------------+--------+--------+----------+------------+----------|
>>> | [2014-01-06 Mon] |   8:00 |  17:00 | 09:00:00 |   00:00:00 | 09:00:00 |
>>> | [2014-01-07 Tue] |        |        | 00:00:00 |   00:00:00 | 00:00:00 |
>>> | [2014-01-08 Wed] |   8:10 |  16:30 | 08:20:00 |   00:00:00 | 08:20:00 |
>>> | [2014-01-09 Thu] |   7:55 |  17:05 | 09:10:00 |   00:00:00 | 09:10:00 |
>>> | [2014-01-10 Fri] |   8:00 |  17:05 | 09:05:00 |   00:00:00 | 09:05:00 |
>>> #+TBLFM: $4=$3-$2;T::$5=if($4 > 0,"8:18:00","00:00:00");T::$6=$4-$5;T
>>>
>>> Can anyone enlighten me as to why I get "00:00:00" in the first case?
>>
>> Not answering your question...
>>
>> Just wanted to mention that, for this type of task, you probably should
>> have a look at the "clocking" mechanisms (C-c C-x C-i and the like), and
>> use them to generate tables like the above, or bills for clients, etc.
>>
>> Using proper clocking brings you many advantages, whose main (IMO) is
>> the "clock check" feature, where you can check that you don't have
>> unintended clocking gaps during the day, nor clocking overlap...
>
> I do already use clocking for certain areas of activity.  However, I
> would need "nested" clocking, i.e. a clock for "at work/not at work"
> which I can clock into at the beginning of the day an out of at the end
> of the day and within that the ability to clock into to and out of
> various activities.  Is that possible?

I do have personal tasks into personal.org, and work tasks in work.org
(and many others).

Those files have FILETAGS (set to :personal: and :work:) so that I don't
have to manually tag every section, etc.

Then, in your clocktable, you can include/exclude clock information
based on the tag.

So, yes, what you ask seems possible to me!

Best regards,
  Seb

-- 
Sebastien Vauban

Re: Conditional in table formula with times

[Loris Bennett]

"Sebastien Vauban" <sva-news@mygooglest.com>
writes:

> "Loris Bennett" wrote:
>> "Sebastien Vauban" writes:
>>> "Loris Bennett" wrote:
>>>> I'm trying to keep track of total time spend at work, but I am having
>>>> trouble with a conditional in table formula:
>>>>
>>>> | *Day*            | *Came* | *Went* | *Worked* | *Required* |   *Diff* |
>>>> |------------------+--------+--------+----------+------------+----------|
>>>> | [2014-01-06 Mon] |   8:00 |  17:00 | 09:00:00 |   00:00:00 | 09:00:00 |
>>>> | [2014-01-07 Tue] |        |        | 00:00:00 |   00:00:00 | 00:00:00 |
>>>> | [2014-01-08 Wed] |   8:10 |  16:30 | 08:20:00 |   00:00:00 | 08:20:00 |
>>>> | [2014-01-09 Thu] |   7:55 |  17:05 | 09:10:00 |   00:00:00 | 09:10:00 |
>>>> | [2014-01-10 Fri] |   8:00 |  17:05 | 09:05:00 |   00:00:00 | 09:05:00 |
>>>> #+TBLFM: $4=$3-$2;T::$5=if($4 > 0,"8:18:00","00:00:00");T::$6=$4-$5;T
>>>>
>>>> Can anyone enlighten me as to why I get "00:00:00" in the first case?
>>>
>>> Not answering your question...
>>>
>>> Just wanted to mention that, for this type of task, you probably should
>>> have a look at the "clocking" mechanisms (C-c C-x C-i and the like), and
>>> use them to generate tables like the above, or bills for clients, etc.
>>>
>>> Using proper clocking brings you many advantages, whose main (IMO) is
>>> the "clock check" feature, where you can check that you don't have
>>> unintended clocking gaps during the day, nor clocking overlap...
>>
>> I do already use clocking for certain areas of activity.  However, I
>> would need "nested" clocking, i.e. a clock for "at work/not at work"
>> which I can clock into at the beginning of the day an out of at the end
>> of the day and within that the ability to clock into to and out of
>> various activities.  Is that possible?
>
> I do have personal tasks into personal.org, and work tasks in work.org
> (and many others).
>
> Those files have FILETAGS (set to :personal: and :work:) so that I don't
> have to manually tag every section, etc.
>
> Then, in your clocktable, you can include/exclude clock information
> based on the tag.
>
> So, yes, what you ask seems possible to me!

OK, so I would clock into a generic ":office:" activity, which might be
interrupted by ":programming:", ":bofhing:", ":meeting:", ":lunch:" and
":snooze:".  I would then create a clock table including ":office:",
":programming:", ":bofhing:" and ":meeting:", but excluding ":lunch:"
and ":snooze:".

But would this approach allow me to do stuff like subtracting how long I
should have spent at work each day, so that I can work out how much
overtime I have?

Cheers,

Loris

-- 
This signature is currently under construction.

Re: Conditional in table formula with times

[Sebastien Vauban]

"Loris Bennett" wrote:
> "Sebastien Vauban" <sva-news-D0wtAvR13HarG/iDocfnWg@public.gmane.org> writes:
>> "Loris Bennett" wrote:
>>> "Sebastien Vauban" writes:
>>>> "Loris Bennett" wrote:
>>>>> I'm trying to keep track of total time spend at work, but I am having
>>>>> trouble with a conditional in table formula:
>>>>>
>>>>> | *Day*            | *Came* | *Went* | *Worked* | *Required* |   *Diff* |
>>>>> |------------------+--------+--------+----------+------------+----------|
>>>>> | [2014-01-06 Mon] |   8:00 |  17:00 | 09:00:00 |   00:00:00 | 09:00:00 |
>>>>> | [2014-01-07 Tue] |        |        | 00:00:00 |   00:00:00 | 00:00:00 |
>>>>> | [2014-01-08 Wed] |   8:10 |  16:30 | 08:20:00 |   00:00:00 | 08:20:00 |
>>>>> | [2014-01-09 Thu] |   7:55 |  17:05 | 09:10:00 |   00:00:00 | 09:10:00 |
>>>>> | [2014-01-10 Fri] |   8:00 |  17:05 | 09:05:00 |   00:00:00 | 09:05:00 |
>>>>> #+TBLFM: $4=$3-$2;T::$5=if($4 > 0,"8:18:00","00:00:00");T::$6=$4-$5;T
>>>>>
>>>>> Can anyone enlighten me as to why I get "00:00:00" in the first case?
>>>>
>>>> Just wanted to mention that, for this type of task, you probably should
>>>> have a look at the "clocking" mechanisms (C-c C-x C-i and the like), and
>>>> use them to generate tables like the above, or bills for clients, etc.
>>>>
>>>> Using proper clocking brings you many advantages, whose main (IMO) is
>>>> the "clock check" feature, where you can check that you don't have
>>>> unintended clocking gaps during the day, nor clocking overlap...
>>>
>>> I do already use clocking for certain areas of activity.  However, I
>>> would need "nested" clocking, i.e. a clock for "at work/not at work"
>>> which I can clock into at the beginning of the day an out of at the end
>>> of the day and within that the ability to clock into to and out of
>>> various activities.  Is that possible?
>>
>> I do have personal tasks into personal.org, and work tasks in work.org
>> (and many others).
>>
>> Those files have FILETAGS (set to :personal: and :work:) so that I don't
>> have to manually tag every section, etc.
>>
>> Then, in your clocktable, you can include/exclude clock information
>> based on the tag.
>>
>> So, yes, what you ask seems possible to me!
>
> OK, so I would clock into a generic ":office:" activity, which might be
> interrupted by ":programming:", ":bofhing:", ":meeting:", ":lunch:" and
> ":snooze:".  I would then create a clock table including ":office:",
> ":programming:", ":bofhing:" and ":meeting:", but excluding ":lunch:"
> and ":snooze:".

Best is that you clock into such tasks:

--8<---------------cut here---------------start------------->8---
* Work                                                 :work:

** TODO Read and reply to emails

** TODO Meetings

** TODO Write report

* Personal                                            :personal:

(Do you really want to clock personal stuff?  You can, though)
--8<---------------cut here---------------end--------------->8---

Then, you can generate a clocktable view for all your :work: activities.

> But would this approach allow me to do stuff like subtracting how long I
> should have spent at work each day, so that I can work out how much
> overtime I have?

I think, but I'm not sure, that you could name the clocktable view, and
access some of its fields. If so, then, you could make your substract in
a summary table.

Best regards,
  Seb

-- 
Sebastien Vauban

Re: Conditional in table formula with times

[Eric S Fraga]

Hi,

Have you tried the numeric equivalent of your times?  I believe the if
expression is interpreted as returning a number of seconds.  The
following table format line works for me:

#+TBLFM: $4=$3-$2;T::$5=if($4 > 0,29880,0);T::$6=$4-$5;T

HTH,
eric
-- 
: Eric S Fraga (0xFFFCF67D), Emacs 24.3.1, Org release_8.2.5h-585-g5f0ca0

Re: Conditional in table formula with times

[Loris Bennett]

Hi Eric,

Eric S Fraga <e.fraga@ucl.ac.uk> writes:

> Hi,
>
> Have you tried the numeric equivalent of your times?  I believe the if
> expression is interpreted as returning a number of seconds.  The
> following table format line works for me:
>
> #+TBLFM: $4=$3-$2;T::$5=if($4 > 0,29880,0);T::$6=$4-$5;T
>
> HTH,
> eric

Works for me, too.

Thanks,

Loris

-- 
This signature is currently under construction.