Monday, July 5, 2010

Tip for displaying the HOUR only in Excel

Situation:  you have pulled some date/time data from another source (in my case SharePoint) but don't know how to group/aggregate the data so that you can determine which hour of the day had the most entries.  Grouping itself tends to be a little messy in Excel and requires a decent amount of work to separate all the pieces out by you.  Here's a nifty little formula that uses just nested IF statements to determine what hour of the day (in military time) the event occurred so you can then run functions like the average number of entries or the total of them - and H2 happens to be the cell where my date/time value was located:
=IF(AND(VALUE(H2)>=0.33,VALUE(H2)<0.375),"08",IF(AND(VALUE(H2)>=0.375,VALUE(H2)<0.4167),"09",IF(AND(VALUE(H2)>=0.4167,VALUE(H2)<0.4583),"10",IF(AND(VALUE(H2)>=0.4583,VALUE(H2)<0.5),"11",IF(AND(VALUE(H2)>=0.5,VALUE(H2)<0.5417),"12",IF(AND(VALUE(H2)>=0.5417,VALUE(H2)<0.5833),"13",IF(AND(VALUE(H2)>=0.5833,VALUE(H2)<0.625),"14",IF(AND(VALUE(H2)>=0.625,VALUE(H2)<0.6667),"15",IF(AND(VALUE(H2)>=0.6667,VALUE(H2)<0.7083),"16")))))))))

This is based on these calculations (they ARE rounded so you may have to tweak them ever so slightly):
8am = .33
9am = .375
10am = .4167
11am = .4583
12pm = .5
1pm = .5417
2pm = .5833
3pm = .625
4pm = .6667
5pm = .7083