CODEWITHSUNDEEP
emacsorg-mode
Wizard
Wizard

Reputation: 22113

Copy the date in org table

Suppose such a spreadsheet in org table 

|------------+-------+------------+--------+--------+------------|
| Date       | Items | Unit Price | Amount | Amount | Categories |
|------------+-------+------------+--------+--------+------------|
| 2019/09/17 | A     |       2.64 |      1 |   2.64 | materials  |
|            | B     |      52.67 |      2 | 105.34 | diagnosis  |
|            | C     |       3.08 |      1 |   3.08 | materials  |
|            | D     |       3.85 |      2 |    7.7 | materials  |
|            | E     |      33.66 |      2 |  67.32 | materials  |
|            | F     |         40 |      1 |     40 | treatments |
|            | G     |       16.5 |      1 |   16.5 | materials  |
|            | H     |          4 |      3 |     12 | treatments |
|            | I     |         40 |      1 |     40 | bed        |
|            | M     |       6    |     13 |     78 | treatments |
|------------+-------+------------+--------+--------+------------|
#+TBLFM: $5=$3*$4

How could copy the date 2019/09.17 to the bottom of data column?

Upvotes: 1

Views: 396

Answers (1)

NickD
NickD

Reputation: 6422

The link that @manandearth posted in the comments describes how to duplicate (perhaps with slight modifications) the entries in a column. Briefly, pressing S-RET in a cell duplicates its contents from the cell above (if it is not empty) - if the cell is full and the next cell is empty then it duplicates the full cell to the empty cell. If the contents are numeric, then the "duplication" involves a slight modification: it increases the value by 1. The same happens with a date: it increases the date to next day (but the date has to be in a format that Org mode recognizes: either an active date <YYYY-MM-DD> or an inactive data [YYYY-MM-DD]). The increment by default is 1 in these cases, but can be set to something else by setting the variable org-table-copy-increment to a different value. That's the "interactive" case I mention in my comment.

The other way to fill a column in a table is by using a formula. For example here's a formula to fill the first column with a copy of the first entry in the column:

#+TBLFM: @3$1..@>$1 = @2$1

This says: Set all rows from row 3 (@3) to the last row (@>) of column 1 ($1) to the value of the cell in row 2 (@2), column 1 ($1). Note that row 1 is the header. Press C-c C-c on the table formula line above and ... wait, what happened?

|------------+-------+------------+--------+--------+------------|
|       Date | Items | Unit Price | Amount | Amount | Categories |
|------------+-------+------------+--------+--------+------------|
| 2019/09/17 | A     |       2.64 |      1 |   2.64 | materials  |
|  13.196078 | B     |      52.67 |      2 | 105.34 | diagnosis  |
|  13.196078 | C     |       3.08 |      1 |   3.08 | materials  |
|  13.196078 | D     |       3.85 |      2 |    7.7 | materials  |
|  13.196078 | E     |      33.66 |      2 |  67.32 | materials  |
|  13.196078 | F     |         40 |      1 |     40 | treatments |
|  13.196078 | G     |       16.5 |      1 |   16.5 | materials  |
|  13.196078 | H     |          4 |      3 |     12 | treatments |
|  13.196078 | I     |         40 |      1 |     40 | bed        |
|  13.196078 | M     |          6 |     13 |     78 | treatments |
|------------+-------+------------+--------+--------+------------|
#+TBLFM: @3$1..@>$1 = @2$1

It does not quite work in this case for a technical reason: Org mode uses Calc in table formula calculations and Calc looks at 2019/09/17 and says: "Aha, I have to divide 2019 by 9 and then divide the result by 17", and fills the rest of the column with the result of the divisions: 13.196078. You may have meant 2019/09/17 to be a date, but Org mode does not know that: it gives it to Calc which interprets it as an arithmetic expression. The solution here is the same as in the linked answer: make Org mode aware that it's a date by making it either an active date: <2019-09-17> or an inactive date: [2019-09-17]:

|------------------+-------+------------+--------+--------+------------|
| Date             | Items | Unit Price | Amount | Amount | Categories |
|------------------+-------+------------+--------+--------+------------|
| [2019-09-17]     | A     |       2.64 |      1 |   2.64 | materials  |
| [2019-09-17 Tue] | B     |      52.67 |      2 | 105.34 | diagnosis  |
| [2019-09-17 Tue] | C     |       3.08 |      1 |   3.08 | materials  |
| [2019-09-17 Tue] | D     |       3.85 |      2 |    7.7 | materials  |
| [2019-09-17 Tue] | E     |      33.66 |      2 |  67.32 | materials  |
| [2019-09-17 Tue] | F     |         40 |      1 |     40 | treatments |
| [2019-09-17 Tue] | G     |       16.5 |      1 |   16.5 | materials  |
| [2019-09-17 Tue] | H     |          4 |      3 |     12 | treatments |
| [2019-09-17 Tue] | I     |         40 |      1 |     40 | bed        |
| [2019-09-17 Tue] | M     |          6 |     13 |     78 | treatments |
|------------------+-------+------------+--------+--------+------------|
#+TBLFM: @3$1..@>$1 = @2$1

This does not do automatic incrementation but if that's what you want, it's easy to accomplish: Calc can do calculations on dates, so we can increment daily by adding to the date in each row the row number minus 2 (e.g. row 3 would get an increment of 3 - 2 = 1, row 4 would get 4 - 2 = 2, etc). To accomplish this, you have to get the row number of the current row: the idiom is @#. Then the formula becomes:

#+TBLFM: @3$1..@>$1 = @2$1 + @# - 2

and the table becomes:

|------------------+-------+------------+--------+--------+------------|
| Date             | Items | Unit Price | Amount | Amount | Categories |
|------------------+-------+------------+--------+--------+------------|
| [2019-09-17]     | A     |       2.64 |      1 |   2.64 | materials  |
| [2019-09-18 Wed] | B     |      52.67 |      2 | 105.34 | diagnosis  |
| [2019-09-19 Thu] | C     |       3.08 |      1 |   3.08 | materials  |
| [2019-09-20 Fri] | D     |       3.85 |      2 |    7.7 | materials  |
| [2019-09-21 Sat] | E     |      33.66 |      2 |  67.32 | materials  |
| [2019-09-22 Sun] | F     |         40 |      1 |     40 | treatments |
| [2019-09-23 Mon] | G     |       16.5 |      1 |   16.5 | materials  |
| [2019-09-24 Tue] | H     |          4 |      3 |     12 | treatments |
| [2019-09-25 Wed] | I     |         40 |      1 |     40 | bed        |
| [2019-09-26 Thu] | M     |          6 |     13 |     78 | treatments |
|------------------+-------+------------+--------+--------+------------|
#+TBLFM: @3$1..@>$1 = @2$1+ @# - 2

The various anomalies of the display of dates (do we include the day of the week? do we include the time?) might be worked around using org-time-stamp-custom-formats but that gets us into waters that I have not explored.

Upvotes: 1

Related Questions