Formula to extract Employee work schedule report from "date of hiring list"

Hi guys,

I have a small requirement that I need to complete. I attached the sample data file.

I have a data sheet with employee hiring schedule. There are 8 employees and dates when they are hired by the company. Each employee is hired till the date it says that service stopped (thats when all the employees hired till that date are withdrawn from service).

I need to prepare a report as to how many employees are hired on all Mondays from 30th of March till 30th of July and the list of names. (I inserted the layout of the report in the attachment).

I tried doing a vlookup (TRUE) for the dates. But that didn't work out well. I think we can achieve this using VBA programming.

Please help me with this report.

Thank in advance,
Ravi.

First off - please remove "Need Help with" from your title - it adds no value and detracts from search facility

You can do this with formulae if you want if you're prepared to use some lightweight helper calculations.

First off I should point out initially there are some discrepancies in your sample:

-- B8:B11 should be 09 rather than 10 I think

-- I don't understand why in week 1 you show only one employee rather than two (#1 & #2) - if someone starts on a Monday surely they count in that week (ie #2)

Attached is a basic example of a formula driven approach which reflects the following:

to Data sheet we add the following calculations:

Please Login or Register  to view this content.

With the above in place we can generate the results matrix

On the Report sheet first we insert a new Row 1 (ie blank row above dates) then:

Please Login or Register  to view this content.

The above is all possible given the Data sheet info. is sorted by Date Started column.

I don't follow - the example doesn't make a great deal of sense (or is incomplete)

Originally Posted by ravikiran

if the there is a "Service Stopped", then we should start a fresh count.

Using my earlier sample file:

Please Login or Register  to view this content.

edit: above is ok - remainder needs to be altered - will post back.

Having thought some more about this... attached is a further example in which by altering Report!T1 from 0 to 1 you can get either/or
(ie 0 will be as before, 1 will be per 2nd requirement)