Binning by Hour of Day

Here's what my data looks like

Status | DOW | HOD | Seconds Spent

AD | 4 | 5 | 1200
TS | 4 | 5 | 10560
AD | 4 | 6 | 2600

What I need to do is create a matrix, day of week in the columns, hour of day in the rows, total seconds spent on each status as values (1 matrix per status code). Easy enough with a pivot but the problem is when total seconds exceeds an hour (3600 seconds). For instance, if a status starts at 10:30am and lasts 2 hours, it falls under 3 HOD bins. How can I solve this?

Well, if your example data is representative of your real data, you only have hour of the day, so you don't know if a status starts at 10:00, 10:30 or 10:59.

Or is your data a bit more complicated than your example?

It would be better, anyway, to post a sample workbook - the FAQ describes how to.

Pete

Thanks, Pete_UK. You're right, I did a horrid job explaining the data.

Here is a sample. Thanks for letting me know I can do that. (new here!)

Example.xlsx

This is just one approach to split the time spans into multiple HOD bins.

time_bins.xlsx

cyiangou- thank you very much, this approach will work quite well.

newdoverman- thank you, i think i see where you were going with this and will try it with more data. p.s. if you were wondering why some of my seconds calculations were doubled, it's because some "statuses" apply twice. I removed the multiplier column from the sample data without realizing this would make it confusing.

Thanks, good tip about combining the date and time. Hadn't done that and it will simplify the calculations that straddle midnight. About 50,000 rows so you saved me some upcoming googling!

I'm extracting date and time with =DATE(MID(.....),...) but the sample was a paste values job.

Thanks, ndm, that's quite helpful.

I thought this was solved but still having issues! What I need to do is create a heatmap of this data by DOW and HOD, sum of seconds (sometimes rate of statues compared to total) as the values.

The problem with the current solution is that when a status goes over midnight, it loops around and the remainder is added to 1am, 2am etc. but on the same DOW. For instance, if a 3 hour status begins at 11pm on thursday, i need 1 hour to bin on thursday and 2 on friday.

Any ideas?

Thanks, ndm. So they are hour of day (HOD) and day of week (DOW), calculating them with =WEEKDAY and =HOUR formulas.

I think there's been some confusion! The statuses are not shifts, they are statuses of people who are on a shift. I've uploaded an example that may better illustrate what I'm after.

Appreciate the help nonetheless! Only thing message #4 is lacking is the ability to put after-midnight time into the next day's bin (for midnight-straddling statuses).

Glad you find that method useful. I've tweaked it a bit, and should now handle midnight rollover.

time_bins_2.xlsx

To test, change B1 to 2014/01/02.

FrankTO, what is the maximum span of time that will be contained in one set of source data at a time. ie. Is it a one week view, a one month view, year?

Cyiangou, mostly quarterly, one year maximum.

I don't see an easy way to handle such a large span simultaneously using my method (without having as many columns as there are hours in a quarter). However you could loop this, outputting one day stats at a time, and then using a pivot table on the resulting data.

Excellent! Will take a closer look this afternoon, but at first glance this seems to be doing everything I need.

http://www.excelforum.com/showthread...43#post4148243

Excellent. Is is fully working now? Glad to have helped. If you liked this answer, please consider clicking the 'Add reputation' star to the left.

Fully working now. Thanks for putting it together. Appreciate it! Reputation added.

Ta very much!