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?

ontheair
  • 25
  • 2
  • Not quite a duplicate, but very similar: https://emacs.stackexchange.com/questions/78256/how-to-express-time-inside-an-org-table-formula – NickD Aug 02 '23 at 09:30

1 Answers1

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
Tobias
  • 32,569
  • 1
  • 34
  • 75
  • 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 01 '23 at 13:31