Exporting - Minutes output or a Summery of hours when filtering

[stoberblog]

Is your feature request related to a problem? Please describe.
When using the export function, the way time is currently presented (##:## h) is very hard to use in excel (or similar).

Describe the solution you'd like
I'm proposing, another column is added (or can be set up) to just export minutes, and with a decimal point and not character suffix. This would mean the column could be selected and summed very easily.

Describe alternatives you've considered
An alternative, would be to have a summary of the hours/minutes performed once a filter has been added in the export (eg Project A, between date B and date C).

[kevinpapst]

I don't have Excel, just the Apple Calc called "Numbers" ... but there it took me 30 seconds to add a new column "C" with the Formula "B2-A2" and auto-completed that for the complete table + one more sum for this newly created column.
Et voila, the duration was there for all exported entries in less than a minute.
It shouldn't be much harder with Excel I guess.

[kevinpapst]

If someone wants to try it: you need to add a new line here:
https://github.com/kevinpapst/kimai2/blob/master/src/Export/Renderer/AbstractSpreadsheetRenderer.php#L174
the defined variable will be available in all export renderer (spreadsheets, not pdf).

[BeckeBauer]

A working solution would be:
Line 174: $sheet->setCellValueByColumnAndRow($entryHeaderColumn++, $entryHeaderRow, number_format($timesheet->getDuration() / 60));
Output will than be minutes
In Excel:
=A1/(24*60) (A1 must be substituted with the cell where the minutes are shown) then format the cell as hh:mm (or whatever time format you like)

[BeckeBauer]

@kevin: Is there a way to export the date of a time record without begin or end (instead of 11.07.2019 15:10 just 11.07.2019)?
I tried $sheet->setCellValueByColumnAndRow($entryHeaderColumn++, $entryHeaderRow, $timesheet->getDate()); and many other variables but nothing worked

[kevinpapst]

begin and end date are DateTime objects: https://www.php.net/manual/de/class.datetime.php
So you can format them as you want: $timesheet->getBegin()->format('Y-m-d')

[BeckeBauer]

Perfect, thank you!

[kevinpapst]

Please post all your additions later on, so I can add them to the core version.

[BeckeBauer]

I modified the AbstractSpreadsheetRenderer.php according to our internal needs. For the date field I added a new line 116
$sheet->setCellValueByColumnAndRow($recordsHeaderColumn++, $recordsHeaderRow, $this->translator->trans('label.date'));
and a new line 155
$sheet->setCellValueByColumnAndRow($entryHeaderColumn++, $entryHeaderRow, $timesheet->getBegin()->format('d.m.Y'));
Now, exports start with a row "Date"

[BeckeBauer]

By the way, I just noticed two missing ++ in row 175. If you use this line before the last row, the code should be $sheet->setCellValueByColumnAndRow($entryHeaderColumn++, $entryHeaderRow, $this->getFormattedMoney($timesheet->getRate(), $customerCurrency)); (two "+" after $entryHeaderColumn)

[kevinpapst]

They are not missing, but absent on purpose.
But yes, the line should not be appended without noticing that, thats why I linked line 174 😆

[kevinpapst]

Please have a look at #1046 - I added two columns to the export and changed the data format to native date (try Excel format and you should see what I mean).

[Stadly]

I just got a similar request from my co-workers. They would like to have the following columns in the exported Excel files:

1. Date shown without time.
2. Duration shown as number of hours (with decimals).

[Stadly]

They are not missing, but absent on purpose.
But yes, the line should not be appended without noticing that, thats why I linked line 174 😆

It wouldn't harm to include ++ on the last line as well, and it would make it less error-prone :) If you really want $entryHeaderColumn to equal the number of columns for some reason, I suggest starting with $entryHeaderColumn = 0 and using ++$entryHeaderColumn everywhere instead. The same goes for $recordsHeaderColumn.

[kevinpapst]

@Stadly please check #1046 both for the changed code and the updated Excel document on export

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. If you use Kimai on a daily basis, please consider donating to support further development of Kimai.