Help with reporting headcount and cost

[Guest]

Excel 2003

I have following information in columns: Name, arrival date, departure date,
cost.
I would like to create a pivot table showing total headcount and cost on a
monthly level for the year in question and also on a yearly level for the
duration of the project. Presently I have an own row per person per month,
but I would like come up with something more sensible.
What would be an efficient way of handling this?

 

[Guest]

Hi Hans,

I don’t know if the following method is the best and will be interested if
anyone submits any other options.

Create additional columns for the Months and Years:-

Assuming:-
Col A = Name
Col B = Arrival Date
Col C = Departure Date
Col D = Cost

Additional columns:-
Col E = Month
Col F = Year

In cell E2 (months column) use the following formula:-

=Month(B2) (Returns month as a numeral)

In cell F2 (year column):-

=year(B2)

You can now use the pivot table to extract the required reports. The Months
and Years columns can be hidden if required but something else to consider is
the use of Autofilter combined with SUBTOTAL function which also works well
with the Months and Years.

You should realize that if arrival and departure dates are different months
then you need to handle how you apportion the costs. You might want to apply
the months to the departure dates instead of the arrival dates.

Regards,

OssieMac