I have an org-table for my timesheets. Part of it looks this:

| Start              | End                    | End - Start |
| <2020-03-05 10:00> | <2020-03-05 Thu 14:30> |      0.1875 |
#+TBLFM: $3=$2-$1

How can I get that "0.1875" to turn into "4:30"?

  • (Spac)emacs/calc/org-mode has undestood that they are dates (org-toggle-time-stamp-overlays works on them and the colours change). Perhaps this is the

    algorithm generally “does the right thing”

    from Free-Form Dates <- Date Formats <- Display Modes <- Mode Settings <- Calc-manual

  • And the subtraction is correct (24 hours * 0.1875 = 4.5 hours)

  • But it gives me the duration as a decimal number of a 24 hour day, as per

    Subtracting two date forms computes the number of days between them (represented as a simple number)

    from Date Forms <- Data Types <- Calc-manual


  • "floor($3*24) to get just the hours, and then separate out the minutes, get the result into the same cell, perhaps even with the formatting I want. Ugly, and seems unnecessary. The software already has some understanding of dates
  • time($2-$1) - I just happened to try this right before starting to write this post. floor had worked, so why not?

| Start | End | time(End - Start) | | <2020-03-05 10:00> | <2020-03-05 Thu 14:30> | 4@ 30' 0" | #+TBLFM: $3=time($2-$1)

That's more like it. I recognise this format. It's mentioned in HMS Forms <- Data Types <- Calc-manual

And time is one of the functions from the Index of Algebraic Functions

Now in HMS Forms, it says

Display format for HMS forms is quite flexible. See HMS Formats

HMS Formats <- Display Modes <- Mode Settings <- Calc-manual

The [(calc-hms-notation)] command controls the display of HMS (hours-minutes-seconds) forms. It prompts for a string which consists basically of an “hours” marker, optional punctuation, a “minutes” marker, more optional punctuation, and a “seconds” marker. Punctuation is zero or more spaces, commas, or semicolons. The hours marker is one or more non-punctuation characters. The minutes and seconds markers must be single non-punctuation characters.

But entering these seems to have no effect. I've tried hms No change

h;m;s No change

hh, mm, ss which gives error "Bad hours-minutes-seconds format"

hh;mm;ss which also does, so maybe it's the double h

h;mm;ss this one too, so maybe it's the double anything

No changes at all, which makes me think I've messed up somewhere else. I've restarted Spacemacs as well, and that didn't help.

It seems to me that I should not use org-mode's Durations and time values, because

Input duration values must be of the form ‘HH:MM[:SS]’

and I want time and date to be in the same cell. I sometimes work past midnight

Somewhat related:

Calculating with timestamps in org-mode tables

Org mode time sheet

Edit 8mar2020:

Table formula for dates delta is similar. I'd say this question is different in that I ask whether HH:MM is possible without an extra day-column, and without editing (spac)emacs' config.

  • 1
    Duplicate of emacs.stackexchange.com/questions/46859/… ?
    – jue
    Mar 7, 2020 at 18:13
  • 2
    Does this answer your question? Table formula for dates delta
    – jue
    Mar 7, 2020 at 18:14
  • Almost. Is it possible to get HH:MM(:SS) without the days-column? (Same question a_subscriber had in 46859). I prefer a solution without adding to config so much that I'd either stay with the time($2-$1)-solution, or have an extra column for day
    – Jonta
    Mar 8, 2020 at 17:45
  • 2
    Emacs is all about writing additional code into your config. There is no ready made solution for every special problem. If you think it is important for many people, you could contribute the known solution to the org-mode project. Anyways: If you don't want to put this code into your config, you could embed that code into the org-file itself. Put it into a emacs-lisp source block, give that block a name and use that name from within your table formula.
    – jue
    Mar 10, 2020 at 9:56


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy