Excel 2007 : Headcount Based on Date & Start/Stop Times

**ibspayroll** · 05-10-2011, 12:47 PM

I am trying to create a headcount by half hour list based on scheduled start dates and start/stop times. See attached spreadsheet with Data worksheet and Report worksheet. How can I automate the report build?

**NBVC** · 05-10-2011, 02:19 PM

See attached to see if I understood correctly.

You would be best to change the time columns to actual time inputs and the dates in Report changed to actual dates... you can format as mm/dd if you want....

Then formula in B5 would be:

=COUNTIFS(Data!$D:$D,">="&$A5,Data!$D:$D,"<="&$A6+TIME(0,30,0),Data!$A:$A,B$4)

copied down and across.

You can format the results as custom: 0;-0;;@ to hide the 0 results...

**ibspayroll** · 05-10-2011, 03:45 PM

NVBC - thanks for the response. I didn't see an attachment though.

**NBVC** · 05-10-2011, 03:49 PM

Sorry 'bout that...

**ibspayroll** · 05-10-2011, 04:14 PM

Sorry, but I don't think I described what I am after well enough. The report should list the total number of employees working at each half hour interval. For example, on 5/9 the fourth employee starts at 10:00 a.m. At 10:00 a.m., there would be 4 employees working. The example report on my sample spreadsheet lists what the expected results would be. Than you very much for your help.

**NBVC** · 05-10-2011, 04:38 PM

Ok, with same conditions as before... i.e. actual times in column D and E of Data... and actual dates in row 4 of Report, use formula:

=COUNTIFS(Data!$A:$A,B$4,Data!$D:$D,"<="&$A5)-COUNTIFS(Data!$A:$A,B$4,Data!$E:$E,"<="&$A5)

copied down and across the columns.

Again, hide the 0's by formatting as custom: 0;-0;;@

**ibspayroll** · 05-10-2011, 04:58 PM

Looks great! Last question: how would I automate the population of the dates in cells B4..F4?

**NBVC** · 05-10-2011, 05:01 PM

Not sure what you mean?

Do you mean how I converted to real date?

If yes, I typed May 5, 2011 in F9 and dragged across it incremented by one day.

If you mean using a formula, then after date is entered in B4, then in C4, =B4+1 copied across....

not sure I interpreted the question correctly though...

**ibspayroll** · 05-10-2011, 05:28 PM

The intent is to extract the work schedule data from a database, paste it into the cells on the Data worksheet, and have the report automatically generated (i.e., the headcounts by half hour and the dates would automatically be generated - there would be no need to key in dates). I am trying to automate this process as much as possible for the end user. Thanks again for your help.