Creating Histograms with Time Ranges

[AZ_Joe]

I have 16,790 records with military time stamps (00:00 - 23:59) that I need to sort into the following ranges:

04:00 - 10:59
11:00 - 13:59
14:00 - 16:59
17:00 - 03:59

Any insight would be appreciated.

- Joe

[djapigo]

Hi Joe,

Here's my take... note that I'm using Excel 2010, so the attachment might not show up...

1. I used a helper column with this formula...
=IF(A2>=TIME(17,0,0),"17:00 - 03:59",IF(A2>=TIME(14,0,0),"14:00 - 16:59",IF(A2>=TIME(11,0,0),"11:00 - 13:59",IF(A2>=TIME(4,0,0),"04:00 - 10:59","17:00 - 03:59"))))

2. Then used a COUNTIF function...

3. Then inserted a bar chart (highlighted E3:F7, then insert chart)

Hope this helps... let me know if you get stuck...

Dennis

[Ace_XL]

Good job Dennis.

Without the helper columns, you could determine count by using the following

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

See attached

[djapigo]

Thanks, Ace... great formula on your end too... nice, clean and elegant...

[AZ_Joe]

Hi Joe,

Here's my take... note that I'm using Excel 2010, so the attachment might not show up...

1. I used a helper column with this formula...
=IF(A2>=TIME(17,0,0),"17:00 - 03:59",IF(A2>=TIME(14,0,0),"14:00 - 16:59",IF(A2>=TIME(11,0,0),"11:00 - 13:59",IF(A2>=TIME(4,0,0),"04:00 - 10:59","17:00 - 03:59"))))

2. Then used a COUNTIF function...

3. Then inserted a bar chart (highlighted E3:F7, then insert chart)

Hope this helps... let me know if you get stuck...

Dennis

Dennis -

Thnaks so much for your help. Have a wonderful weekend.

- Joe

[djapigo]

No problem, Joe... we're here to help... did you see Ace's formula... it works without the helper column...

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save