Count frequency of set hour ranges from large list of times
I have a large list of date/time stamps (over 8000). I am just concerned with the time portions. I need to summarize how many records appeared within a certain hour block.
Example:
Hour / Frequency
04:00-04:59 / 200
05:00-05:59 / 150
06:00-06:59 ...
19:00-19:59
08:00-08:59
09:00-09:59
10:00-10:59
11:00-11:59
12:00-12:59
Attached is a sample of the data (just 100 records) I am working with. This is part of a much larger spreadsheet so I mainly need the correct formula(s). My range of date/times I need to work from is listed in tab "times" column B. The hour ranges I need to count can be found in tab "frequency".
Last edited by samchargers09; 02-26-2010 at 07:14 PM. Reason: Solved.
You forgot to actually attach your workbook but in a nutshell, Excel codes date/time as a real number of days. Therefore the part after the decimal point is fraction of a day.
If you have a date/time in A1 and need just the time part you can use
=A1-INT(A1)
Does that get you started?
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
Please see the attached. Hopefully it goes through this time.
time_tbl.xls
See attached for a solution. Note a couple of things:
When putting a range of numbers or times in Excel, it is best to put the start time in one cell and the end time in another. When you put them both together in a string, it is much harder to work with.
Your range of times looks like there may be an error, which I preserved in my solution. The time 19:00 appears between 06:00 and 08:00. Should it really be 07:00?
Your data doesn't provide comprehensive test coverage, and I didn't take the time to test with a larger set of data. In fact, only one time on your sheet falls into any of the ranges, and it happens to be 7 PM which falls into the range that I just identified as a likely error.
So you will probably want to run more extensive tests.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
I see there may be some confusion created by my spreadsheet. I do not have a start and end time.
- Column A is my raw data collected in MM/DD/YY HH:MM format.
- Column B is my cleansed data whereby certain records needed to be offset by 5 hours given a time zone change in some of our record sets. =A1-"05:00" for certain records, simply =A2 for records that did not require a change.
- Column C is not needed for this specific challenge but is being used for other reporting. =INT(A1)
- Column D is where I attempted to split off the time from the date but this may be the cause of my problem. I do not know. I used =MOD(A1,1)
Note: For example purposes only I referred to row 1
Use a Pivot Table.
It's not clear if you intend to use Col A or Col B as source ?
Using whichever is appropriate Insert a Pivot Table and set the DateTime field as both Row Label and Data Field (set to COUNT)
Then to get the frequency by hour (rather than by record):
Right click on any date time stamp in the Row Label and select Group -> choose Hours only.
in your dataset of course there's only one hour - 00 so you will get 00 -> 99 result
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I am talking about sheet "frequency" where you are tabulating the counts. Did you see the change I made?Originally Posted by samchargers09
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
When I create the pivot table, right click on a date time stamp record in the Row Label and go to group it keeps saying "Cannot group that selection." I researched and tried following the instructions here http://contextures.com/xlPivot07.html also to no avail. I am using Excel 2008 for Mac. Could it be how my date time stamps are formatted? I have tried columns B & C and cannot get it to work.
Just to check - are you able to create a Pivot from your earlier sample file ?
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Yes. I can create a Pivot from the earlier sample file.
Attached is an updated file with two pivot tables - 1) using column B 2) using column C. Either way my ultimate goal is to simply count the occurrence of records within the defined hour ranges on the "frequency" tab.
time_tbl_pivot.xls
Last edited by samchargers09; 02-26-2010 at 05:37 PM. Reason: attached new file
OK, so are you saying you can not group the Pivots in your uploaded file ?
(FWIW - I am able to group both by Month)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I got the pivot table working. Thank you for your assistance. This is SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks