6

We have a new manager at work and he wants us to timesheet. No problem, I have org-mode. Except, he wants time reported like this:

0900-1000 Customer  Project  xMinutes
0900-1000 Customer2 Project2 yMinutes
1000-1100 Customer  Project3 zMinutes

So, in other words, if I clock time on three things in an hour he wants three lines for that hour which show the three sets of minutes that I spent, against the task. Naturally, my Org file has tasks for different customers as subheadings under those customers, so in theory those two columns should be easy.

I'm not very experienced with org hacking and I can't work out how to break the time down this way. So I thought I'd ask someone else to do it for me :)

Hopefully the answer isn't staring me in the face in the Org manual.

** Greenwich Consultancy
   :LOGBOOK:
   CLOCK: [2016-02-18 Thu 10:40]--[2016-02-18 Thu 11:10] =>  0:30
   CLOCK: [2016-01-13 Wed 14:48]--[2016-01-13 Wed 15:29] =>  0:41
   CLOCK: [2016-01-12 Tue 11:00]--[2016-01-12 Tue 16:53] =>  5:53
   :END:
** PU [2/2]
*** DONE Identify PU Open equella block
    CLOSED: [2015-10-26 Mon 09:09]
    :LOGBOOK:
    CLOCK: [2015-10-09 Fri 14:10]--[2015-10-09 Fri 16:06] =>  1:56
    :END:
*** Update UAT 
    :LOGBOOK:
    CLOCK: [2016-02-17 Wed 10:03]--[2016-02-17 Wed 10:16] =>  0:13
    CLOCK: [2015-11-17 Tue 09:12]--[2015-11-17 Tue 09:18] =>  0:06
    CLOCK: [2015-11-16 Mon 14:49]--[2015-11-16 Mon 15:00] =>  0:11
    :END:
*** PSMD Block
    :LOGBOOK:
    CLOCK: [2016-02-26 Fri 16:26]--[2016-02-26 Fri 16:53] =>  0:27
    CLOCK: [2016-02-11 Thu 14:27]--[2016-02-11 Thu 14:47] =>  0:20
    CLOCK: [2016-02-09 Tue 16:17]--[2016-02-09 Tue 16:31] =>  0:14
    CLOCK: [2016-02-09 Tue 14:24]--[2016-02-09 Tue 15:02] =>  0:38
    CLOCK: [2016-02-09 Tue 12:13]--[2016-02-09 Tue 13:01] =>  0:48
    CLOCK: [2016-02-09 Tue 10:15]--[2016-02-09 Tue 10:58] =>  0:43
    CLOCK: [2016-02-05 Fri 14:27]--[2016-02-05 Fri 16:36] =>  2:09
    CLOCK: [2016-02-05 Fri 11:11]--[2016-02-05 Fri 12:02] =>  0:51
    CLOCK: [2016-02-05 Fri 10:36]--[2016-02-05 Fri 10:53] =>  0:17
    CLOCK: [2016-02-03 Wed 09:39]--[2016-02-03 Wed 10:24] =>  0:45
    CLOCK: [2016-02-02 Tue 16:39]--[2016-02-02 Tue 17:00] =>  0:21
    CLOCK: [2016-01-29 Fri 12:27]--[2016-01-29 Fri 12:33] =>  0:06
    :END:       
*** Server tidy
**** STARTED Check TII code on UAT
     :LOGBOOK:
     CLOCK: [2016-02-25 Thu 16:59]--[2016-02-26 Fri 09:22] => 16:23
     :END:
**** DONE Update Coursework M26 branch
     CLOSED: [2016-02-25 Thu 16:59]
     - State "DONE"       from "STARTED"    [2016-02-25 Thu 16:59]
     :LOGBOOK:
     CLOCK: [2016-02-25 Thu 16:56]--[2016-02-25 Thu 16:59] =>  0:03
     :END:   
*** UAT PSMD Roamers problem
    :LOGBOOK:
    CLOCK: [2016-02-25 Thu 11:19]--[2016-02-25 Thu 11:33] =>  0:14
    :END:
*** Gradebook problem
    :LOGBOOK:
    CLOCK: [2016-02-26 Fri 15:35]--[2016-02-26 Fri 16:26] =>  0:51
    :END:

So for example, the line

CLOCK: [2016-02-05 Fri 11:11]--[2016-02-05 Fri 12:02] =>  0:51

Should produce two output lines (with tabs or comma separators):

1000-1100 PU PSMD Block 49
1100-1200 PU PSMD Block  2
  • This is best implemented by using an Org dynamic block, just like the ones producing a normal clocktable. The manual describes how to write one. I have such a function to yield me excel compatible CSV for reporting work package hours for each month. In your case, are these just daily reports that are required? The format seems a bit strange and cumbersome, but can be done... Could you give a small example of how your org file is structured in regard to customer, project, task, subtask? – dfeich Feb 26 '16 at 16:35
  • It should be feasible to extract the minutes by org-clock-sum. One can give a headline filter, a start time and a stop time as optional arguments. When I remember right, there are problems with this function in org 8.2 but it works in org 8.3.3. I am not sure about that. The overall consequence is that you should also report your emacs-version and your org-version. – Tobias Feb 26 '16 at 16:42
  • @Tobias Org 8.3.3, and Emacs 24.5.1. – Nagora Feb 27 '16 at 11:14
  • @dfeich I've added a small section of my data. The top level headings (ie, the single * level) are stuff like "sprint #13" or "Support Work" etc. The second level is the client and third+ level the specific tasks. – Nagora Feb 27 '16 at 11:48
5
+50

My solution is by no means shorter than the one by Tobias, and I probably would not have written it, had I not been interested in one of the side products: A function which collects me all clock ranges with full heading information in a certain time interval, which is the basis for further processing. Based on this function and some other helpers, I implement a dynamic block which you can use like a normal org clocktable block. So, you just write the following, and upon hitting C-c C-c on the BEGIN line, it will expand to the table:

  #+BEGIN: nagora-report :buffer "nagora-example.org" :day 2016-02-05
  #+END:

  #+BEGIN: nagora-report :buffer "nagora-example.org" :day 2016-02-25
  #+END:

These two blocks will expand to the following:

  #+BEGIN: nagora-report :buffer "nagora-example.org" :day 2016-02-05
  #+CAPTION: timesheet for day 2016-02-05
  |        Time | Customer | Task       | Minutes |
  |-------------+----------+------------+---------|
  | 10:00-11:00 | PU [2/2] | PSMD Block |      17 |
  | 11:00-12:00 | PU [2/2] | PSMD Block |      49 |
  | 12:00-13:00 | PU [2/2] | PSMD Block |       2 |
  | 14:00-15:00 | PU [2/2] | PSMD Block |      33 |
  | 15:00-16:00 | PU [2/2] | PSMD Block |      60 |
  | 16:00-17:00 | PU [2/2] | PSMD Block |      36 |
  |-------------+----------+------------+---------|
  |       TOTAL |          |            |     197 |
  #+TBLFM: @>$>=vsum(@I..@II)
  #+END:

  #+BEGIN: nagora-report :buffer "nagora-example.org" :day 2016-02-25
  #+CAPTION: timesheet for day 2016-02-25
  |        Time | Customer | Task                          | Minutes |
  |-------------+----------+-------------------------------+---------|
  | 11:00-12:00 | PU [2/2] | UAT PSMD Roamers problem      |      14 |
  | 16:00-17:00 | PU [2/2] | STARTED Check TII code on UAT |       1 |
  | 16:00-17:00 | PU [2/2] | Update Coursework M26 branch  |       3 |
  | 17:00-18:00 | PU [2/2] | STARTED Check TII code on UAT |      60 |
  | 18:00-19:00 | PU [2/2] | STARTED Check TII code on UAT |      60 |
  | 19:00-20:00 | PU [2/2] | STARTED Check TII code on UAT |      60 |
  | 20:00-21:00 | PU [2/2] | STARTED Check TII code on UAT |      60 |
  | 21:00-22:00 | PU [2/2] | STARTED Check TII code on UAT |      60 |
  | 22:00-23:00 | PU [2/2] | STARTED Check TII code on UAT |      60 |
  |-------------+----------+-------------------------------+---------|
  |       TOTAL |          |                               |     378 |
  #+TBLFM: @>$>=vsum(@I..@II)
  #+END:

The counters in brackets can be easily cleaned up if desired. Currently the dynamic block wants an open buffer (:buffer argument) but it is trivial to change that to a file path.

I still wonder at this special timesheet reporting format, because it also is not translation invariant, so the same amount of work will look different, if you begin at the start of an hour or some time later. But sometimes it is shorter to implement such a function than discuss with management ;-)

I hope this is useful. It certainly can be cleaned up a bit and made more consistent.

(defun dfeich/org-clock-get-tr-for-ivl (buffer tstart-str tend-str &optional limit)
  "Return clocking information touching a given time interval."
  (cl-assert (and buffer (get-buffer buffer)) nil "Error: :buffer must be defined")
  (with-current-buffer buffer
    (save-excursion
      (let ((re (concat "^\\(\\*+[ \t]*.*\\)\\|^[ \t]*"
            org-clock-string
            "[ \t]*\\(?:\\(\\[.*?\\]\\)-+\\(\\[.*?\\]\\)\\|=>[ \t]+\\([0-9]+\\):\\([0-9]+\\)\\)"))
        (counter 0)
        (tmphd "BEFORE FIRST HEADING")
        (tstart (org-time-string-to-seconds tstart-str))
        (tend (org-time-string-to-seconds tend-str))
        (limit (or limit (point-max)))
        headings timelst
        lvl title result ts te)
    (goto-char (point-min))
    (cl-block myblock
      (while (re-search-forward re nil t)
        (cond
         ;; found a org heading
         ((match-end 1)
          (if (> (length timelst) 0)
          (setq result (nconc result (list (list
                            (copy-sequence headings)
                            timelst)))))
          (setq tmphd (org-heading-components)
            lvl (car tmphd)
            title (nth 4 tmphd)
            timelst nil)
          ;; maintain a list of the current heading hierarchy
          (cond
           ((> lvl (length headings))
        (setq headings  (nconc headings `(,title))))
           ((= lvl (length headings))
        (setf (nth (1- lvl) headings) title))
           ((< lvl (length headings))
        (setq headings (cl-subseq headings 0 lvl))
        (setf (nth (1- lvl) headings) title))))
         ;; found a clock line with 2 timestamps
         ((match-end 3)
          (setq ts (save-match-data (org-time-string-to-seconds
                     (match-string-no-properties 2)))
            te (save-match-data (org-time-string-to-seconds
                     (match-string-no-properties 3))))
          ;; the clock lines progress from newest to oldest. This
          ;; enables skipping the rest if this condition is true
          (if (> tstart te)
          (if (re-search-forward "^\\(\\*+[ \t]*.*\\)" nil t)
              (beginning-of-line)
            (goto-char (point-max)))
        (when (> tend ts)
          (setq timelst (nconc timelst (list
                        (list (match-string-no-properties 2)
                              (match-string-no-properties 3)))))))))
        (when (>= (point) limit)
          (cl-return-from myblock))))
    (if (> (length timelst) 0)
        (setq result (nconc result (list (list (copy-sequence headings)
                           timelst)))))
    result))))

(defun dfeich/org-slice-tr (tstart-str tend-str cutstart-str cutend-str)
  "Return time slice of a time range in minutes."
  (let ((tstart (org-time-string-to-seconds tstart-str))
    (tend (org-time-string-to-seconds tend-str))
    (cutstart (if (stringp cutstart-str)
              (org-time-string-to-seconds cutstart-str)
            cutstart-str))
    (cutend (if (stringp cutend-str)
            (org-time-string-to-seconds cutend-str)
          cutend-str))
    result)
    (setq result (max 0
              (/  (- (min tend cutend) (max tstart cutstart))
              60)))))

(defun dfeich/org-clock-hourly-report (struct tstart-str tend-str)
  "Return a structure containing a per hour report within an interval."
  (let* ((tstart (org-time-string-to-seconds tstart-str))
     (tend (org-time-string-to-seconds tend-str))
     (delta 3600)
     (intvls (cl-loop for tm from tstart to (- tend delta) by delta
              collect `(,tm ,(+ tm delta))))
     result)
    ;; iterate over the intervals for the final table
    (cl-loop for iv in intvls
         collect (list
              iv
              (let* ((cutstart (car iv))
                 (cutend (cadr iv))
                 (tmsum 0.0)
                 headings trlst)
            ;; iterate over the task structure
            (cl-loop
             for item in struct
             do (progn
                  (setq headings (car item)
                    trlst (cadr item)
                    ;; sum up the parts of the time
                    ;; ranges falling into this
                    ;; interval
                    tmsum (apply
                       #'+
                       (mapcar
                        (lambda (tr)
                          (dfeich/org-slice-tr (car tr)
                                   (cadr tr)
                                   cutstart
                                   cutend))
                        trlst))))
             if (> tmsum 0) collect `(,headings ,tmsum) into lst
             finally return lst))))))

(defun org-dblock-write:nagora-report (params)
 "Fill in a dynamic timesheet reporting block."
  (let* ((buffer (plist-get params :buffer))
     (day (symbol-name (plist-get params :day)))
     (tstart (if (string-match-p "^[0-9]\\{4\\}-[0-9]\\{2\\}-[0-9]\\{2\\}$" day)
             day
           (error "Error: day format must be in YYYY-mm-dd format")))
     (tend (concat day " 23:59"))
     (table (dfeich/org-clock-hourly-report
         (dfeich/org-clock-get-tr-for-ivl buffer tstart tend)
         tstart tend)))
    (insert (format "#+CAPTION: timesheet for day %s\n" day))
    (insert "|Time|Customer| Task |Minutes|\n|------\n")
    (cl-loop
     for item in table
     do (let ((ivl (car item))
          (entries (cadr item)))
      (cl-loop for e in entries
           do (let ((headings (car e))
                (minutes (cadr e)))
            (insert (concat
                 "|"
                 (format-time-string "%H:%M" (seconds-to-time
                                  (car ivl)))
                 "-"
                 (format-time-string "%H:%M" (seconds-to-time
                                  (cadr ivl)))
                 "|" (nth 1 headings)
                 "|" (car (last headings))
                 "|" (format "%d" minutes)
                 "|\n"))))))
    (insert "|----\n|TOTAL||||\n#+TBLFM: @>$>=vsum(@I..@II)")
    (search-backward "Time")
    (org-table-align)
    (org-table-recalculate '(16))))

Note: The solution is inspired by the org-clock-sum code and analogous to that function I chose not to invoke the full org parsers, but to use a probably more efficient direct parsing, since I'm only interested in headlines and clock lines.

The regexp search function uses an interesting concept copied from org-clock-sum involving an "or"-ed regexp which either matches a headline or a clock time range. That idea is kind of nice, since it allows one to implement a simple state machine with a single regexp in this way.

  • Thanks. An embarrassment of riches. We had a meeting with the management on Thursday where the whole development team complained about the format but they would not budge. – Nagora Feb 28 '16 at 16:03
  • The org parser works with caching and should not introduce too large performance losses. I did not check how far the caching reaches. For an instance: I do not know whether org has a partial parser such as cedet/wisent. With such a partial parser it is not necessary to reparse the whole document if some part of it has been edited. – Tobias Feb 28 '16 at 17:33
  • @Tobias: I have not profiled, and it could well be that it is no big thing performance-wise for normal files. I need to look into the caching mechanism which seems interesting. I wrote the function in the way I did in part for my own usage where I have org docs with items accumulating clock information over 1-2 years. Since clocked times are progressing from newest clock interval to oldest, you can stop parsing once you reach a clock no longer in your target time interval, instead of parsing the whole. – dfeich Feb 29 '16 at 11:51
  • I've noted the summing of the minutes of the same project in the same hour in your version and adapted this to mine. Thanks. The clock items for different projects are not sorted with respect to each other. Therefore, I first collect everything and then sort w.r.t. the beginnings of the clock ranges. The sorting has n*log(n) costs all other things have costs of order n (even parsing). I work on the sorted list. I dismiss work items that end in the hour. I increment the hour only if there are really work items there else I take the hour of the first item in the (remaining) list as next hour. – Tobias Feb 29 '16 at 19:11
  • @dfeich I'm giving you the tick because I've found the code easier than Tobias' to alter to suit some other needs I had which were relatively minor. It's saved me a lot of pointless effort. Thanks. – Nagora Mar 3 '16 at 17:05
5

The command org-time-sheet defined in the following code does almost what you specified. If you call it interactively with M-x org-time-sheet you can also set the start and the end time. Only clock entries starting in this time range are included into the time sheet.

Some words about "almost":

  1. You did not specify how you differentiate between efforts on different days. org-time-sheet writes the date in the first column when it starts a new day.
  2. It is better to write out an org-table instead of inserting commas or tabs as separators. You can then put point into the table and call the menu-point Tbl -> Export to export to whatever format is supported (e.g., csv-format).
  3. Currently, I also insert a start and a end timestamp into the first two columns of the table. This makes easier for you to check whether you get what you want. When you believe that you get what you want you can customize the function org-time-sheet-time-formatter. Just remove the formatted minutes-start and minutes-end.

EDIT:

  1. Time-spans for the same project in the same hour are now summed up by default. If you do call org-time-sheet with prefix arg then these time-spans are listed separately.
  2. The time-sheet is inserted at point if you call org-time-sheet interactively. Previously, the time-sheet was inserted after the next code-block.
(defcustom org-time-sheet-date-formatter
  (lambda (day month year) (format "%4d-%02d-%02d" year month day))
  "Function to format date in time sheets.
It takes three numbers as arguments: day month year."
  :type 'function
  :group 'org-clock)

(defcustom org-time-sheet-time-formatter
  (lambda (start end hour minutes headings)
    (list (format-time-string "%F %R" (apply 'encode-time minutes-start))
          (format-time-string "%F %R" (apply 'encode-time minutes-end))
          (format "%2d00--%2d00" hour (1+ hour)) (or (nth 1 headings) "") (or (nth 2 headings) "") minutes))
  "Callback function returning one table line in a time sheet (as list).
The arguments of the function are:
START:    start time with format as in `decode-time'
END:     end time with format as in `decode-time'
MINUTES:  number of minutes between start time and end time
HEADINGS: the heading titles of the current entry and all its parents as a list starting with the top-parent."
  :type 'function
  :group 'org-clock)

(eval-when-compile
  (require 'cl-lib))
(require 'org-element)
(require 'ob-core)

(defun org-element-parent (element &optional type)
  "Get parent of ELEMENT or nil if there is none.
If TYPE is non-nil get next parent of that type."
  (let* ((props (cadr element))
         (parent (plist-get props :parent)))
    (if type
        (when parent
          (if (eq (car parent) type)
              parent
            (org-element-parent parent type)))
      parent)))

(defun org-element-timestamp-less-p (ts1 ts2 &optional end)
  "Non-nil if timestamp TS1 is less than timestamp TS2.
TS1 and TS2 is timestamp data as returned by `org-element-timestamp-parser'.
If end is non-nil the end-time of TS1 and TS2 is compared else the start time."
  (cl-assert (eq (car ts1) 'timestamp) "TS1 is not a timestamp")
  (cl-assert (eq (car ts2) 'timestamp) "TS2 is not a timestamp")
  (let ((p1 (cadr ts1))
    (p2 (cadr ts2))
    (tests '("year" "month" "day" "hour" "minute"))
    ret)
    (while (and (let* ((what (intern-soft (concat ":" (car tests) (if end "-end" "-start"))))
               (t1 (plist-get p1 what))
               (t2 (plist-get p2 what)))
          (cond
           ((< t1 t2)
            (setq ret t)
            nil)
           ((= t1 t2) t)))
        (setq tests (cdr tests))))
    ret))

(defun time-day-month-year (time)
  "Return the list (day month year) from TIME.
TIME may be the time as returned by `current-time' or by `decode-time'."
  (if (<= (length time) 4)
      (setq time (decode-time time)))
  (mapcar (lambda (el) (nth el time)) '(3 4 5)))

(defun org-element-timestamp-to-time (timestamp &optional start/end encode)
  "Convert start or end of TIMESTAMP returned by `org-element-timestamp-parser'
to time format as defined in the documentation of `decode-time'.
START/END is either the symbol 'start or 'end or nil which is equivalent to 'start.
If ENCODE is non-nil the return value is encoded as described in the documentation for `current-time'."
  (cl-assert (eq (car timestamp) 'timestamp) "Argument is not a timestamp")
  (unless start/end (setq start/end 'start))
  (let* ((p (cadr timestamp))
     (ret (append
           '(0)
           (mapcar (lambda (what) (plist-get p (intern-soft (concat ":" what "-" (symbol-name start/end))))) '("minute" "hour" "day" "month" "year"))
           (list 0 nil (car (current-time-zone))))))
    (if encode
    (apply #'encode-time ret)
      ret)))

(defmacro decoded-time-complete-timezone (t1 t2)
  "If only one of the time specifications T1 and T2 has time-zone information
append that to the other one."
  `(let ((n1 (length ,t1))
         (n2 (length ,t2)))
     (cond
      ((> n1 n2)
       (setq ,t2 (copy-sequence ,t2))
       (setf (nthcdr n2 ,t2) (nthcdr n2 ,t1)))
      ((< n1 n2)
       (setq ,t1 (copy-sequence ,t1))
       (setf (nthcdr n1 ,t1) (nthcdr n1 ,t2))))))

(defun decoded-time-less-p (t1 t2)
  "Like `time-less-p' but for decoded time values as `decode-time' returns."
  (decoded-time-complete-timezone t1 t2)
  (time-less-p (apply 'encode-time t1) (apply 'encode-time t2)))

(defun decoded-time-advance (time dt)
  "Return TIME advanced by DT but for decoded time values as `decode-time' returns.
The time zone information of time is used for the result."
  (decode-time (apply 'encode-time (append (cl-mapcar #'+ (butlast time (- (length time) 6)) (butlast dt (- (length dt) 6))) (nthcdr 6 time)))))

(defun org-time-sheet (&optional tStart tEnd dont-sum)
  "Create time sheet for time span from tStart to tEnd from current org buffer.
When called non-interactively each of the parameters tStart and tEnd may be nil
or must be decoded time (see `decode-time').
Do not sum up minutest of a project within an hour if dont-sum is non-nil.
Interactively do not sum if called with prefix arg."
   (interactive (list
                 (decode-time (org-read-date t t nil "Start time:" '(0 0)))
                 (decode-time (org-read-date t t nil "End time:"))
         current-prefix-arg))
   (org-time-sheet-shedule (org-time-sheet-collect tStart tEnd) (called-interactively-p 'any) dont-sum))

(defun org-time-sheet-collect (tStart tEnd)
  "Returns ordered time sheet collection of current buffer
for clocked items with start time within the range from tStart to tEnd."
  (if (> (length tStart) 4)
      (setq tStart (apply 'encode-time tStart)))
  (if (> (length tEnd) 4)
      (setq tEnd (apply 'encode-time tEnd)))
   (let ((tree (org-element-parse-buffer)))
     (cl-stable-sort 
      (org-element-map tree 'clock
        (lambda (clock)
          ;; get the relevant data of the clocks
          (let* ((timestamp (plist-get (cadr clock) :value))
                 (parent clock)
                 (headers (nreverse (cl-loop while (setq parent (org-element-parent parent 'headline)) collect (car (plist-get (cadr parent) :title))))))
            (cl-assert timestamp nil "Clock line without timestamp")
            (when (and (or (null tStart) (null (time-less-p (org-element-timestamp-to-time timestamp 'start t) tStart)))
                       (or (null tEnd) (time-less-p (org-element-timestamp-to-time timestamp 'end t) tEnd)))
              (list (org-element-timestamp-to-time timestamp 'start)
                    (org-element-timestamp-to-time timestamp 'end)
                    headers))
            )))
      #'time-less-p
      :key (lambda (clock) (apply 'encode-time (car clock))))))

(defun org-time-sheet-shedule (clocks &optional interactive dont-sum)
  "Creates time sheet shedule from ordered time sheet clock collection (see `org-time-sheet-collect')."
     ;; sheduling
     (when clocks
       (setq clocks (cons nil clocks))
       (let* ((start (copy-sequence (caadr clocks)))
              (day-month-year (time-day-month-year start))
              (shedule (list (list (apply org-time-sheet-date-formatter day-month-year)))))
         (setf (nth 1 start) 0) ;; clear minutes
         (while (cdr clocks)
           (let ((end (decoded-time-advance start '(0 0 1 0 0 0)))
         project-alist
                 (iter clocks))
             (while (decoded-time-less-p (cl-caadr iter) end) ;; collect clocks starting before the end of current hour
               (let* ((start-time (cl-caadr iter))
                      (end-time (cl-cadadr iter))
                      (minutes-start (if (decoded-time-less-p start-time start) start start-time))
                      (minutes-end (if (decoded-time-less-p end end-time) end end-time))
              (minutes (/ (nth 1 (time-subtract (apply 'encode-time minutes-end) (apply 'encode-time minutes-start))) 60))
                      (headlines (nth 2 (cadr iter)))
              (project (assoc headlines project-alist)))
         (if (and project (null dont-sum))
             (setcdr project (list (+ (cadr project) minutes) minutes-start minutes-end))
           (setq project-alist (cons (list headlines minutes minutes-start minutes-end) project-alist)))
                 (if (decoded-time-less-p end end-time)
                     (setq iter (cdr iter))
                   ;; delete clock that also finishes in this hour:
                   (setcdr iter (cddr iter))) ;; delete clock entry
                 ))
         (setq project-alist (nreverse project-alist))
         ;; Compose shedule for hour:
         (while project-alist
           (let ((headlines (caar project-alist))
             (minutes (nth 1 (car project-alist)))
             (minutes-start (nth 2 (car project-alist)))
             (minutes-end (nth 3 (car project-alist))))
         (setq shedule (cons (funcall org-time-sheet-time-formatter minutes-start minutes-end (nth 2 start) minutes headlines) shedule)))
           (setq project-alist (cdr project-alist)))
             ;; calculate new time:
             (when (cdr clocks)
               (let ((next-hour-start-time (decoded-time-advance start '(0 0 1 0 0 0)))
                     (next-hour-end-time (decoded-time-advance start '(0 0 2 0 0 0))))
                 (setq start (copy-sequence (caadr clocks)))
                 (setf (nth 1 start) 0) ;; minutes
                 (when (decoded-time-less-p start next-hour-end-time)
                   (setq start next-hour-start-time))
                 (let ((new-day-month-year (time-day-month-year start)))
                   (unless (equal day-month-year new-day-month-year)
                     (setq shedule (cons (list (apply org-time-sheet-date-formatter new-day-month-year)) shedule)
                           day-month-year new-day-month-year)))))))
         (setq shedule (nreverse shedule))
         (when interactive
       (insert (with-temp-buffer
             (insert "#+begin_src emacs-lisp\n#+end_src\n")
             (let ((pt (point)))
               (org-babel-insert-result shedule)
               (delete-region (point-min) pt))
             (buffer-string))))
         shedule)))

You get the following output for your example:

| 2015-10-09       |                  |            |                       |                                |    |
| 2015-10-09 15:10 | 2015-10-09 16:00 | 1400--1500 | PU                    | Identify PU Open equella block | 50 |
| 2015-10-09 16:00 | 2015-10-09 17:00 | 1600--1700 | PU                    | Identify PU Open equella block | 60 |
| 2015-10-09 17:00 | 2015-10-09 17:06 | 1700--1800 | PU                    | Identify PU Open equella block |  6 |
| 2015-11-16       |                  |            |                       |                                |    |
| 2015-11-16 14:49 | 2015-11-16 15:00 | 1400--1500 | PU                    | Update UAT                     | 11 |
| 2015-11-17       |                  |            |                       |                                |    |
| 2015-11-17 09:12 | 2015-11-17 09:18 |  900--1000 | PU                    | Update UAT                     |  6 |
| 2016-01-12       |                  |            |                       |                                |    |
| 2016-01-12 11:00 | 2016-01-12 12:00 | 1100--1200 | Greenwich Consultancy |                                | 60 |
| 2016-01-12 12:00 | 2016-01-12 13:00 | 1200--1300 | Greenwich Consultancy |                                | 60 |
| 2016-01-12 13:00 | 2016-01-12 14:00 | 1300--1400 | Greenwich Consultancy |                                | 60 |
| 2016-01-12 14:00 | 2016-01-12 15:00 | 1400--1500 | Greenwich Consultancy |                                | 60 |
| 2016-01-12 15:00 | 2016-01-12 16:00 | 1500--1600 | Greenwich Consultancy |                                | 60 |
| 2016-01-12 16:00 | 2016-01-12 16:53 | 1600--1700 | Greenwich Consultancy |                                | 53 |
| 2016-01-13       |                  |            |                       |                                |    |
| 2016-01-13 14:48 | 2016-01-13 15:00 | 1400--1500 | Greenwich Consultancy |                                | 12 |
| 2016-01-13 15:00 | 2016-01-13 15:29 | 1500--1600 | Greenwich Consultancy |                                | 29 |
| 2016-01-29       |                  |            |                       |                                |    |
| 2016-01-29 12:27 | 2016-01-29 12:33 | 1200--1300 | PU                    | PSMD Block                     |  6 |
| 2016-02-02       |                  |            |                       |                                |    |
| 2016-02-02 16:39 | 2016-02-02 17:00 | 1600--1700 | PU                    | PSMD Block                     | 21 |
| 2016-02-03       |                  |            |                       |                                |    |
| 2016-02-03 09:39 | 2016-02-03 10:00 |  900--1000 | PU                    | PSMD Block                     | 21 |
| 2016-02-03 10:00 | 2016-02-03 10:24 | 1000--1100 | PU                    | PSMD Block                     | 24 |
| 2016-02-05       |                  |            |                       |                                |    |
| 2016-02-05 10:36 | 2016-02-05 10:53 | 1000--1100 | PU                    | PSMD Block                     | 17 |
| 2016-02-05 11:11 | 2016-02-05 12:00 | 1100--1200 | PU                    | PSMD Block                     | 49 |
| 2016-02-05 12:00 | 2016-02-05 12:02 | 1200--1300 | PU                    | PSMD Block                     |  2 |
| 2016-02-05 14:27 | 2016-02-05 15:00 | 1400--1500 | PU                    | PSMD Block                     | 33 |
| 2016-02-05 15:00 | 2016-02-05 16:00 | 1500--1600 | PU                    | PSMD Block                     | 60 |
| 2016-02-05 16:00 | 2016-02-05 16:36 | 1600--1700 | PU                    | PSMD Block                     | 36 |
| 2016-02-09       |                  |            |                       |                                |    |
| 2016-02-09 10:15 | 2016-02-09 10:58 | 1000--1100 | PU                    | PSMD Block                     | 43 |
| 2016-02-09 12:13 | 2016-02-09 13:00 | 1200--1300 | PU                    | PSMD Block                     | 47 |
| 2016-02-09 13:00 | 2016-02-09 13:01 | 1300--1400 | PU                    | PSMD Block                     |  1 |
| 2016-02-09 14:24 | 2016-02-09 15:00 | 1400--1500 | PU                    | PSMD Block                     | 36 |
| 2016-02-09 15:00 | 2016-02-09 15:02 | 1500--1600 | PU                    | PSMD Block                     |  2 |
| 2016-02-09 16:17 | 2016-02-09 16:31 | 1600--1700 | PU                    | PSMD Block                     | 14 |
| 2016-02-11       |                  |            |                       |                                |    |
| 2016-02-11 14:27 | 2016-02-11 14:47 | 1400--1500 | PU                    | PSMD Block                     | 20 |
| 2016-02-17       |                  |            |                       |                                |    |
| 2016-02-17 10:03 | 2016-02-17 10:16 | 1000--1100 | PU                    | Update UAT                     | 13 |
| 2016-02-18       |                  |            |                       |                                |    |
| 2016-02-18 10:40 | 2016-02-18 11:00 | 1000--1100 | Greenwich Consultancy |                                | 20 |
| 2016-02-18 11:00 | 2016-02-18 11:10 | 1100--1200 | Greenwich Consultancy |                                | 10 |
| 2016-02-25       |                  |            |                       |                                |    |
| 2016-02-25 11:19 | 2016-02-25 11:33 | 1100--1200 | PU                    | UAT PSMD Roamers problem       | 14 |
| 2016-02-25 16:56 | 2016-02-25 16:59 | 1600--1700 | PU                    | Server tidy                    |  3 |
| 2016-02-25 16:59 | 2016-02-25 17:00 | 1600--1700 | PU                    | Server tidy                    |  1 |
| 2016-02-25 17:00 | 2016-02-25 18:00 | 1700--1800 | PU                    | Server tidy                    | 60 |
| 2016-02-25 18:00 | 2016-02-25 19:00 | 1800--1900 | PU                    | Server tidy                    | 60 |
| 2016-02-25 19:00 | 2016-02-25 20:00 | 1900--2000 | PU                    | Server tidy                    | 60 |
| 2016-02-25 20:00 | 2016-02-25 21:00 | 2000--2100 | PU                    | Server tidy                    | 60 |
| 2016-02-25 21:00 | 2016-02-25 22:00 | 2100--2200 | PU                    | Server tidy                    | 60 |
| 2016-02-25 22:00 | 2016-02-25 23:00 | 2200--2300 | PU                    | Server tidy                    | 60 |
| 2016-02-25 23:00 | 2016-02-26 00:00 | 2300--2400 | PU                    | Server tidy                    | 60 |
| 2016-02-26       |                  |            |                       |                                |    |
| 2016-02-26 00:00 | 2016-02-26 01:00 |  000-- 100 | PU                    | Server tidy                    | 60 |
| 2016-02-26 01:00 | 2016-02-26 02:00 |  100-- 200 | PU                    | Server tidy                    | 60 |
| 2016-02-26 02:00 | 2016-02-26 03:00 |  200-- 300 | PU                    | Server tidy                    | 60 |
| 2016-02-26 03:00 | 2016-02-26 04:00 |  300-- 400 | PU                    | Server tidy                    | 60 |
| 2016-02-26 04:00 | 2016-02-26 05:00 |  400-- 500 | PU                    | Server tidy                    | 60 |
| 2016-02-26 05:00 | 2016-02-26 06:00 |  500-- 600 | PU                    | Server tidy                    | 60 |
| 2016-02-26 06:00 | 2016-02-26 07:00 |  600-- 700 | PU                    | Server tidy                    | 60 |
| 2016-02-26 07:00 | 2016-02-26 08:00 |  700-- 800 | PU                    | Server tidy                    | 60 |
| 2016-02-26 08:00 | 2016-02-26 09:00 |  800-- 900 | PU                    | Server tidy                    | 60 |
| 2016-02-26 09:00 | 2016-02-26 09:22 |  900--1000 | PU                    | Server tidy                    | 22 |
| 2016-02-26 15:35 | 2016-02-26 16:00 | 1500--1600 | PU                    | Gradebook problem              | 25 |
| 2016-02-26 16:00 | 2016-02-26 16:26 | 1600--1700 | PU                    | Gradebook problem              | 26 |
| 2016-02-26 16:26 | 2016-02-26 16:53 | 1600--1700 | PU                    | PSMD Block                     | 27 |
  • Hmm, seem to have not closed "Server Tidy" properly. Anyway, this is great stuff. I'm surprised at the amount of code, but at least I don't feel like an idiot for not working it out myself. I'll give it a few test runs and see how I get one. Thanks very much. – Nagora Feb 28 '16 at 12:16
  • 1
    @Nagora I suggest to keep this question open as long as possible to see whether there is a more elegant solution than this one. I would be very glad if you would get an elegant three-liner that you could accept as solution and I could learn from even if my solution caused me some work. Nevertheless, if nobody comes up with something better you have at least this monstrum;-). – Tobias Feb 28 '16 at 15:01
  • Thanks, but I took the other answer as I found the code easier to work with. Thanks very much for the effort anyway. – Nagora Mar 3 '16 at 17:06

Your Answer

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

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