Days Count Sheet Help

[Dax Arroway]

I'm hoping someone can help. I'm working on a worksheet (Rollup!) that gives
me a count of days that a person is present in a given month from information
pulled from another sheet (Data!).

Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource (L).

Rollup! needs to include columns: Name, BeginDate, EndDate, FSource, and
DaysCount. But it's funky.

For starters, there will be two dropdowns on Data!, one for year (A1), one
for month (B1) used to filter the records pulled from Data! for the specified
year and month.

For BeginDate I need it to be either the first day of the month or the
StartDate depending on when the person started. For example if my focus
month is December, 2009, and one person's BeginDate is 11/20/09, then the
BeginDate next to their name needs to be 12/01/09. I'm trying to count the
days the person is present in only that month.

For EndDate, same thing. If the person ended within the month
(EndDate=12/24/09), then I need that date, otherwise, if the person is still
there through the last day of the month, I need the last day of the month
inserted. So if this person was still there at the end of December, EndDate
would be 12/31/09. Also, if there is no EndDate on Data!, then the last day
of the selected month should be inserted.

For DaysCount, I need it to simply count the days between BeginDate and
EndDate with one exception. If the EndDate occurs within the month of focus
(ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day (because
the last day doesn't count).

For FSource I simply need to pull over the cooresponding FSource from Data!.

Can anyone help with a formula for this?

Thanks SO MUCH in advance. I know it's complicated but I'm sure it's
possible. I unfortunately lack the Excel coding skill to get it done!
--Dax

 

[Ashish Mathur]

Hi,

So what do you need help with - determining start date or end date

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Dax Arroway]

So if the Data sheet is:

Name StartDate EndDate FSource
John Smith 11/25/09 12/20/09 Blue
Fred Jones 11/26/09 01/15/10 Green
Bill Blast 12/15/09 01/10/10 Blue

And I selected a range of 12/2009 on the RollUp sheet.
The result on the RollUp sheet would be:

Name BeginDate EndDate FSource DaysCount
John Smith 12/01/09 12/20/09 Blue 19
Fred Jones 12/01/09 12/31/09 Green 31
Bill Blast 12/15/09 12/31/09 Blue 16

Please let me know if there's other questions.
--Dax

 

[Ashish Mathur]

Hi,

Assume that the order of the names on both sheets is the same (assumed from
your illustration below). Also, enter 12/2009 and 12/1/2009 I.e. 1 December
2009 in cell F2 of the Rollup sheet

You may use this to determine the BeginDate. Copy down till where required

=if(Data!$B2<F2,F2,Data!$B2)

To determine the end date, use the following:

=if(and(Data!$B2>F2,Data!$B2<eomonth(F2,0)),Data!$B2,eomonth(F2,0))

Since the order of names remains the same, the FSource can simply be copied
and pasted

I am not clear about the days count - please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

So if the Data sheet is:

Name StartDate EndDate FSource
John Smith 11/25/09 12/20/09 Blue
Fred Jones 11/26/09 01/15/10 Green
Bill Blast 12/15/09 01/10/10 Blue

And I selected a range of 12/2009 on the RollUp sheet.
The result on the RollUp sheet would be:

Name BeginDate EndDate FSource DaysCount
John Smith 12/01/09 12/20/09 Blue 19
Fred Jones 12/01/09 12/31/09 Green 31
Bill Blast 12/15/09 12/31/09 Blue 16

Please let me know if there's other questions.
--Dax

 

[Dax Arroway]

There's actually over 1500 entries which span over 3 years.
RollUp!A1 contains a dropdown of years: 2006 through 2020.
RollUp!B1 contains a dropdown of months: 1 through 12.

The list I gave was only partial. There are many and the names don't
necessarily stay in the same order. There of course are those who would not
show up. For example if I filtered for December, folks who have an EndDate
of 11/29/09 or earlier would be filtered out.

The DaysCount is simply the count of days between the BeginDate and the
EndDate that results in the sheet. But something that's extra is that if the
EndDate happens within the month of focus, the formula needs to subtract one
day from the total.

OK, so in Enlish the formula for Cell A2:however many names there are would
be:
Look at cell A1 and get the year, then look at cell B1 and get the month.
Now look through the names of the Data sheet and select all of them whose
begin dates are before the last day of the selected month. If they have an
end date before the first day of the selected month, disregard. Insert the
names of those listed down the column.

In cell B2: For each name, if the begin date is within the selected month,
insert that date, if the begin date is before the selected month, insert the
first day of the selected month into cell.

In cell C2: For each name, if the end date is not there, or is after the
selected month, but the last day of the selected month. If the end date is
within the selected month, insert that date.

In Cell D2: For each name, enter their FSource.

In Cell E2: Count the days between Cell B2 through C2 but if the date of C2
is withing the selected month, subtract one day.

So, there are people coming and going all the time. Some are present for
months. What we need to do is have a way of being able to take a snapshot of
specific months, know who is present during that month, and how many days
those specific people were present during that month. Sometimes it could be
the whole month but sometimes it would only be a part of that month. They
are however consecutive, obviously.

Does that help explain it a bit better?
--Dax

 

[Dax Arroway]

Any other takers on this??? PLEASE HELP! I'm so stuck!