Count hours per period from a large list

English
#1

Hi,

I would like to track and count hours in a year or a custom period, like from 01 February 2023 until 31 January 2024. Then there is a contingent for that period of free hours, and I like to move the rest per period to the next period. I’m going to call it “year” for convenience…

First approach was: I have a tab per year, let’s say 2022, 2023, 2024. I’m going to duplicate that tab and clear the entries for every next year like 2024. I would need to make a dynamic reference to the previous tab like (logically spoken) [thisTab-1].C3 to get the rest from the previous year. I failed with that approach as I’m not sure if I can get a reference to the previous years tab…

I learned, it’s a better way to separate data and calculation. So I started having a big first tab “data list” getting a plain list of hours, 1 entry per line, increasing, no year boundaries. Then I’m going to create tabs for 2022, 2023, 2024, showing the sorted and cut part of the data list by reference.

I tried doing that by Pivot-Table but it doesn’t seem to be the right feature for that.

Now I tried Filters.
As Libreoffice doesn’t know a function like =FILTER(Datenliste.A:E; YEAR(Datenliste.A:A) = 2024), I tried Advanced filter, having the option to specify the start and end of the period in every tab. Is that the right approach?
But that would mean, in a reference tab called “2023” I would need to first do a big “referencing all” area and then doing the filtering on that, right? But what if the datalist is increasing?

Compatibility issues with Microsoft Excel 2019
#2

pivot_106846.ods (101.9 KB) (the pivot has a filter, and I made a mistake setting the filter values)

#3

Thanks. I see. I guess the filter is wrong and must be DATE >= AND DATE <=.

#4

Yes, that was my mistake. And you see that the filter works. The source table contains only numbers under a row of column labels.
The numbers were imported from a database in random sort order. You can filter and sort the source data any way you want without affecting the pivot table.
The pivot table aggregates the sums of durations (data field) for each day (row field) in ascending sort order of days. The filter button at cell A1 filters the incoming source data. Any filter buttons within a pivot table filter the resulting row or column fields of a pivot table.

#5

I understand.
But I’m trying hard to get the formatting from my original table to that Pivottable. Just copy/paste for format only doesn’t work like that. Just redoing the conditional formatting in order to have alternating row colors is always lost when re-sorting or refreshing the pivot-table.

#6

pivot_240702-1435.ods (121.5 KB)

Please try to get the format from the original table to the pivot table.
When applying any formatting it’s lost after update. I also tried changing the “Pivot Table …” styles in Manage Styles (F11), but I can’t remove the grid or add some alternating row coloring by Conditional Rules with formula ISEVEN(ROW()).

What is broken on my Date column?

#7

Yes, the entire table is rebuilt from scratch. There is nothing to do about it, except using this formatting for printing/pdf export.

#8

That’s a blocker issue against using Pivot tables. We definitely need to format the filtered result in the pivot-tables.
At least I need to get rid of the grid lines. This does not seem to be part of the Pivot table styles?
I managed to apply a background color to the content by changing style “Pivot Table Category”. But I can’t get some background color set for the column headers.

#9

In the Pivot table filter, how can I use fields for the start and enddate?

#10

pivot_240702-1655.ods (134.4 KB)

#11

By the way: The pivot table in your document is an exact copy of the source table, because the combination of date, start time and end time is unique. The sum of durations always refers to one value in a unique record.
You could simply filter the source table, but that would distort your even/odd conditional formatting because spreadsheets just hide filtered rows instead of actually filter them out.

#12

I tried your new version pivot_240702-1655.ods. Formatting is lost after a refresh, but kept when pressing the Refresh-Button with Macro. Is that the idea?

I know, I actually don’t use all the filtering and mapping of Pivot-Tables. I actually only need a filtering per date range. I could do that via an Advanced filter as well, and also reading filter criteria from table. But that means that I have “per year” tabs that need to reference all from the Data tab. And because the Data tab is always increasing, I don’t know how much to reference to each tab? Ok, I just tried:
I made a copy of the Data tab, replaced all values by references to the Data tab. Formatting is easy there. Then I created the Filter criteria in $‘2023’.$F$2:$G$3 with:

Date Date
>=2023-05-01 <=2024-04-01

But creating an Advanced filter fails with “This range does not contain a valid query”.

#13

Have a look at your other topic.

#14

I forgot to upload my new approach, that I described above…
As I almost use the same columns like the Data tab, Pivot table is maybe not the right tool.
Now I got it working with Advanced filter, but there are some drawbacks:
pivot_240704-1146.ods (292.6 KB)

  • Changing the filter criteria doesn’t refresh the filter. I need to reset the filter and reconfigure it.
  • The filtering only happens by hiding rows which means that the color alternation is broken. Unpleasant.
  • As I don’t know how long the Data list will be, I need to reference a very long list for each new tab.
#15

pivot_240704-1153.ods (279.8 KB)

Adding new tabs for new time ranges… I compared doing with with Advanced filters and also Pivot Tables.

Finally I still would prefer Pivot-Tables, as they better refresh without recreating a filter.

Pivot-Table drawbacks: No or only very basic formatting, no filter criteria read from table
Advanced filter drawbacks: Nice formatting but hiding rows breaks color alternation, no update on changing time range

I could go for Pivot tables and accept the very basic formatting. But how can I set the time range somewhere in the table for users that don’t know how to adapt the Pivot table filter?

#16

I had working approaches using Pivot Tables and Macros.
One other requirement I dropped so far: It should also work on Excel 2019. I know compatibility reduces the feature set a lot…
Like Pivot Tables and Macros are not compatible.

I have a complete new approach:
Hours-Testproject_02.ods (61.7 KB)

Some things are still broken in Excel. I could either save as .xslx or using the .ods in Excel. Results look equally broken to me:

Opening .ods takes a minute in Excel.
Opening .xlsx is fast.

Incompatible is:
A3 text field showing the tab name
A1 text field showing the file name
C3 referencing the residue of the previous tab: =INDIRECT(TEXT(A3 - 1; “0”) & “.$E$4”)

Is there any more compatible approach?

#17

https://wiki.documentfoundation.org/ReleaseNotes/24.8

Support importing and exporting OOXML pivot table (cell) format definitions, which allow direct formatting of the pivot table cells (which survive pivot table update). (Tomaž Vajngerl, Collabora)

#18

Ok, this is still Version: 24.2.4.2 (X86_64) in my Linux distribution.

#19

Next version, due next month, will support formattings within pivots.

#20

Pivot was just one thing that wasn’t compatible.
I like my last approach, any way to get that working for both, also Excel 2019?
Should I start the same approach in Excel style and see if Libreoffice can deal with it? Is .xlsx the best shared medium between both?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.