Excel , Pivot Table, Group Dates How To Create Customized

These can vary from 10K (hours, mins, secs) to 1500m, 800m distance (mins, secs) and 200m, 100m sprints (sec, 100s sec).

We will use Pivot Tables, and the FLOOR, TRUNC, and VLOOKUP functions for the different solutions.

Excel , Pivot Table, Group Dates How To Create Customized

This is great if you want to analyze trends throughout the hours of a day to see what time you are busiest, and what time you can take a nap We will also see how to create customized and uneven time blocks.

He also wants a report that has groups for every two hours, and another report that combines the overnight hours into one group.

All cells in the data column of the data set must contain date values.).

The values in the Values area of the pivot table will be summed up to include all transaction that occurred within each hour.

The image below shows that there were 4 transaction between 12:00 AM and 12:59 AM.

I explain how to group dates in the 3rd video of that series.

We will have to use a different solution to group the transactions in time blocks of 2 hours or more.

The nice part is that FLOOR starts at midnight and rounds down in whatever increment you specify.

So subtracting the date from the truncated date will return only the decimal number, which is the time.

If your data set spans multiple days then this is really going to show you what times of the day you do the most volume.

You could analyze it further to see the days of the week, weeks in the month, etc.

This is just text that I created with a formula using the TEXT function.

Its a great technique to learn for things like calculating commissions or tax rates.

These group names are text, not numbers, so Excel will not be able to sort them as times.

The quickest and easiest method is probably to use the Group feature in a Pivot Table (solution 1).

If you want to group the times in increments of multiple hours or fractions of an hour, then the FLOOR and VLOOKUP functions can help group the times.

I have not used FLOOR before and will now use it in the future.

I think that I would use WEEKDAY function in another column to see the days of the week, but DAY(Date) yields the numerical value of the weekday, not the name of the day.

I can solve this with a lookup table of the weekday names or by adding nested IF statements to get the weekday names, but do you have an easier method that would yield the names of the days.

The following formulas will return the day names of the week.

Excel For Mac, Pivot Table, Group Dates