# How to transform abbrevated dates in org table

The input column represents the year without 201 and month and day without leading zeros. I must transform these into full date strings.

After a couple of hours to take step 1, I'm wondering if there is a more elegant way to achieve the steps left:

1. Year: Find first first digit from left: Append 201 before (done)
2. Month: Find 5th digit from 1 to 9: Append a 0 before
3. Day: Find last digit from right which is between 1 to 9: Append a 0 after
4. Insert hyphen after year, month and date
``````|-------+----------+------------|
| input |   output |   expected |
|-------+----------+------------|
|  9725 |  2019725 | 2019-07-25 |
|   811 |   201811 | 2018-01-01 |
| 71212 | 20171212 | 2017-12-12 |
|-------+----------+------------|
#+TBLFM: \$2='(baz @0\$1)
``````
``````(defun baz (cell)
(concat "201" cell))
``````

Explanation: The input represents the batch processing of approx. thousand grocery receipts. The goal was to hit as few key strokes as possible.

• How do you distinguish 11-1 from 1-11? Oct 22, 2019 at 14:15
• @Juancho Thank you. I didn't consider that :/. In that case the input contains a leading zero e.g. 80111 => 2018-01-11 to distuingish from 8111 => 2018-11-01
– jjk
Oct 23, 2019 at 15:35

If you don't mind using the date command, you can try the code below which I think addresses the four points you describe. It works fine with your example, however AFAICT it's impossible to distinguish between 11-1 and 1-11, so here I'm assuming the month is always two digits when the date is 7 digits long.

``````(defun fix-date-string (date)
(let ((yy (substring date 0 4))
(mm (substring date 4 5))
(dd (substring date 5 6))
(mmdd (substring date 4)))
(cond ((= (length date) 6)
(concat yy "0" mm "0" dd))
((= (length date) 7)
(concat yy "0" mmdd))
(t date))))

(defun baz (date)
(let* ((it (concat "201" date))
(it (fix-date-string it))
(it (format "date -d '%s'" it))
(it (shell-command-to-string it))
(it (string-trim it))
(it (date-to-time it))
(it (format-time-string "%F" it)))
it))
``````