unreasonable
Board Regular
- Joined
- Feb 10, 2011
- Messages
- 53
Hi guys. I have a spreadsheet setup that has 2 separate sets of data on the same sheet. Column A through H is formatted by half-hourly interval, so A4 (sheet has headers) is 12:00am, A5 is 12:30am, A6 is 1:00am, etc. Column B represents Sunday, Column C Monday, Column D Tuesday, etc. With this setup, Columns A through H and rows 4 through 51 represent every day of the week and every half hour interval of the day. Each cell within this set of columns is currently blank.
The other set of data starts at column L and goes through column Y. Columns L and M represent Sunday with column L being a start time and column M being an end time. Columns N and O represent Monday with N being start times and O being end times. Each cell within this set of columns (L through Y) is pulled by a formula from another worksheet which is, in turn, imported through a macro.
The desired result I am looking for is this:
For each cell in column A through H, I need to know how many people are scheduled to work based upon their starting and end times represented in columns L through Y. Due to the way that the data is imported, time values are represented thusly; midnight appears as 0, 12:30am appears as 30, 1am appears as 100, 1:30am appears as 130 and so on. 8pm would appear as 2000 rather than 20:00.
I am trying to conceive a formula (or a vba macro) that will look at a person's starting and ending time and count them into each corresponding cell in columns A through H. For instance, For cell B4 (Sunday, 12:00am), I need the cell to tell me how many people will be working on that day at that time. It is easy to make one which only looks at starting time, however I am having trouble when trying to also count people who start at say, 2am and work until 8am.
I know this sounds a lot more complex than it is, and I'm more than willing to attach an example spreadsheet if need be, however I'm not exactly sure how to do that. Thanks in advance for any help!
The other set of data starts at column L and goes through column Y. Columns L and M represent Sunday with column L being a start time and column M being an end time. Columns N and O represent Monday with N being start times and O being end times. Each cell within this set of columns (L through Y) is pulled by a formula from another worksheet which is, in turn, imported through a macro.
The desired result I am looking for is this:
For each cell in column A through H, I need to know how many people are scheduled to work based upon their starting and end times represented in columns L through Y. Due to the way that the data is imported, time values are represented thusly; midnight appears as 0, 12:30am appears as 30, 1am appears as 100, 1:30am appears as 130 and so on. 8pm would appear as 2000 rather than 20:00.
I am trying to conceive a formula (or a vba macro) that will look at a person's starting and ending time and count them into each corresponding cell in columns A through H. For instance, For cell B4 (Sunday, 12:00am), I need the cell to tell me how many people will be working on that day at that time. It is easy to make one which only looks at starting time, however I am having trouble when trying to also count people who start at say, 2am and work until 8am.
I know this sounds a lot more complex than it is, and I'm more than willing to attach an example spreadsheet if need be, however I'm not exactly sure how to do that. Thanks in advance for any help!