Grouping different values by hour

Steenkool wrote:

I can't find a way to group time with values in a different table using formulas and pivot tables.

Pivot Tables, as you have discovered, can easily aggregate (group) data by quarter, month, day of week, etc. Very convenient! Alas, there is not a built-in option for hour.

But you can add a column that uses the HOUR function to retrieve the hour and base the pivot table on that column:

In C2 of the example filled down:

=HOUR(A2)

If you want to get fancier (perhaps for consistency of formatting with other charts) and have date-time in a column rather than a decimal hour then you can do this in D2, filled down:

=TODAY()+DURATION(,,C2)

By adding the hour to TODAY you ensure that all the time components in the date-time value in the cell are on the same day.

And in the Pivot Table choose Group By: Value

Replace , in the formulas with ; if your region uses , as the decimal separator.

And note that those strange-looking leading , (or ;) in the DURATION function are needed here.

More on functions used:

HOUR - Apple Support

DURATION - Apple Support

SG

Glad to hear it helped!

I was puzzled by your question about ASC until I saw the file name of my Numbers document appears in my screenshot. That simply stands for Apple Support Community. I don't know enough to be a consultant. 😀

I hope you noticed that I only included a Gain column in the simple example. No Loss column. For good luck!

SG

Nice looking charts!

Not sure how to interpret 14:00. (At first glance, one gets the impression results were positive in one chart but negative in the other.)

Remember you also have the option of a 2-Axis Chart. If you haven't seen it already, have a look at the working example in the 'Charting Basics' template at File > New in the menu.

SG