1

I am very new to excel and cannot figure out how to take a group of times throughout a single day, which correspond to events in that day, and graph how many events occur during each hour of the day. In other words, I want a bar graph with hrs 0 through 24 on the x axis, and events on the y axis, but my original data set is presented as the actual event time, not events per hour. I need excel to calculate the number of events between 9 and 10, 10 and 11, etc., and then graph that.
My other problem is that my data arrives in the format "8/7/2012 9:12:01 AM". Is there any way I can get excel to ignore the date and just focus on the time without me having to manually cut out the date in every cell?

Please help, and feel free to take it down to a very basic level, as using formulas in excel is not yet intuitive. Thank you.

5
  • If you can make sure that Excel recognizes the data as a date & time, the HOUR function will give you the hour from the time. Go from there.
    – Iszi
    Aug 8, 2012 at 13:38
  • Iszi,Thanks, that solved half of my problem. Now how do I get excel to count all of the events between 9:00 and 10:00, for instance, and display that as a number so I can graph the number of events during each hr of the day? In essence, I now have 11 "9:00s", 7 "10:00s", etc., and I need excel to automatically sum these and present them in a separate column so I don't have to manually do it.
    – GLF
    Aug 8, 2012 at 14:44
  • Check out COUNTIF.
    – Iszi
    Aug 8, 2012 at 14:51
  • Worked like a charm. Thanks a lot for your help Iszi.
    – GLF
    Aug 8, 2012 at 15:18
  • Glad it helped. I posted an answer summing up the comments, so you can up-vote and/or accept. Let me know if there's any part of your question that is not addressed.
    – Iszi
    Aug 8, 2012 at 16:02

1 Answer 1

0

If your values are actually stored in cells formatted for Date/Time in Excel, the HOUR function will pull out the hour value from the time. Combine that with COUNTIF to get the number of events within each hour. You may want to add the date value to the COUNTIF statement, if your spreadsheet crosses multiple days. Once you've worked all that out, you'll have what you need to put the counts into another column and build your charts based off that column.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .