Excel Magic Trick 1469

Add Daily Rainfall 5 AM to 5 AM Next Day: Helper Column & PivotTable


Learn about how to take a rainfall data set with Time-Date Stamps and add rain totals for each day and have the ability to change the definition of the 24 hour period.
(00:11) Introduction to data set and the task at hand
(01:30) Look at Time Values and determine what Time Value should be subtracted from each Date-Time to get correct category.
(03:32) Add Data Validation List to Time Cell so we only subtract the correct whole hour.
(04:55) Helper Column Formula to subtract hour amount. See Relative Cell References and Absolute Cell Reference. See the INT Function.
(06:47) Create PivotTable
(08:05) What happens if we change Number of Hours to Subtract? PivotTable MUST be refreshed
(08:35) Summary


Download “Excel Magic Trick 1469-1481” Excel-Magic-Trick-1469-1481.zip – Downloaded 180 times – 4 MB

Be the first to comment

Leave a Reply