Multiple Days Hourly staff count

I posted one before and had no luck. So i am posting again. I have racked my brain for about 5 days now and have gotten no where. I am able to get it to work for 1 day but i need it to work for multiple days (31) to exact. I have it so it will lookup the start date but having trouble lookuping up end date or the between times.

I want to be able to put in the person, position, date and time start in one cell and date and time end in another cell. The main tab then looks up all the data i enter and color codes it out as well as counts the number of staff at the bottom (i alread have done this)

Please help. I am using excel 2007

It is a big file but that is because of the code i already have in there to show what i want it to do.

You were almost there. I have updated your sheet for Person 3 (row 6). I have changed the date headers to include date and time calculated off the Start Date in cell A2 (rather than have multiple headers separating out date and time) It also makes it easier to update the dates and times off a single cell reference (if you are planning to use the sheet month to month). I have also changed your VLOOKUP formulae. Let me know if you need me to explain any changes I have made.

A slight afterthought. The changes I've made will still only calculate between two dates and times at any given time. I take it that you want the data to remain for previous days once you have entered it?

Yes i would like them to remain the same, so when i put in all the dates and times for the month it will do all of them. I kind of get what you did but not really. Could you explain more? Also could you create one for the whole month? I could pay as well as i would like to get this problem solved.

I've attached a solution. The VBA references formulae (MATCH) on the Data Schedule sheet to achieve what you want. It's a starting point i.e. there is no error handling (you could potentially overwrite previously entered data but you could probably get around this using Intersect in the VBA for example) and I haven't completely populated the spreadsheet with dates etc. so that you can expand on it. It works off a button "Update Chart Schedule" on the Data Schedule worksheet.

Awesome!!! Thank you so much.

One more question. I want to count all the weekdays for for the specified time. Example All the mondays at 12:00am and all the mondays at 12:30am, etc, etc.

Could you attach an example of what you want to achieve?
e.g. Do you want this between two dates?

Here is what i have so far. I guess the two dates would be the start and end April 1 to April 30 or cell A2 and B2

if it can be done by each month that would be even better..

an example would be like it looks at all the data in he charted tab and says
12:00am on all the thursdays we have x amount of people working

Thursdays in April
12:00am 12:30am 1:00am
25 29 35

Then i am able to divided by the number of "Thrusdays" in April to get how many on average we have working during that time period.

I beleive you are on the right track with your last post. THANKS AGAIN!!

That clears things up. I will update the workbook so that it has 1 worksheet per month. If a shift overlaps months i.e. 04/30/2010 to 05/01/2010 then the data will be split between the relevant worksheets.

You are awesome. Highly recommended. Thank you so much

You're welcome .

There are further improvements that could be made. For example, if you have already entered data then you would not want to keep on re-populating the monthly sheets for that range. You could populate a column on your Data Schedule input sheet with a message depending on the success of a data transfer and then use that as a starting point for a subsequent data transfer. Also, if a shift range overlaps a previous entry you will want to flag this before continuing.

I have built in limited error handling as a starting point i.e. you cannot enter a shift start date greater than the end date. Additionally you cannot have a shift start and end date greater than one month (you will probably want to reduce this based on maximum working hours).

It will be good to see the finished article. Keep me posted and let me know if you have any questions or need some assistance.