2

I enter my daily work hours in a spreadsheet. My daily target working time is 8 hours. In the first line my overtime is calculated with this:

| Overtime |  2.5 |
|----------+------|
| Day 1    |    8 |
| Day 2    | 10.5 |
| Day 3    |    8 |
| Day 4    |    8 |
#+TBLFM: @1$2=((vsum(@<<<..@>))-((vlen(@<<<..@>))*8)

With integers this is no problem. But with hours:minutes it does not work anymore (note the wrong result in the first row):

| Overtime | 26:29 |
|----------+-------|
| Day 1    |  8:00 |
| Day 2    | 10:30 |
| Day 3    |  8:00 |
| Day 4    |  8:00 |
#+TBLFM: @1$2=((vsum(@<<<..@>))-((vlen(@<<<..@>))*8);U

What is wrong with my formula?

1

1 Answer 1

2

When you turn on the Org Table Formula Debugger, e.g., M-x org-table-formula-debugger RET and re-calculate the table, you see that calc actually calculates with seconds.

It is also described in the Org manual that integers count as seconds:

Negative duration values can be manipulated as well, and integers are considered as seconds in addition and subtraction.

Also note that you miss Day 1 with the row @<<<. Better use @I instead.

So the corrected version of your formula would be:

| Overtime | 02:30 |
|----------+-------|
| Day 1    |  8:00 |
| Day 2    | 10:30 |
| Day 3    |  8:00 |
| Dafy 4   |  8:00 |
#+TBLFM: @1$2=((vsum(@I..@>))-(vlen(@I..@>))*3600*8);U
1
  • Thank you very much for your help and the details about integers vs. seconds. Also thanks for the hint with @I. Very appreciated!
    – ontheair
    Aug 1, 2023 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.