excel database

[Megan]

The following is what I have for data:

Name Daily Rate Dates of Service Days Total
John Doe 20 6/1/09 – 5/31/10 365 7,300
John Doe 18 1/1/10 – 1/31/10 31 558
John Doe 22 3/1/10 – 3/31/10 31 682
John Doe 7/1/09 – 8/31/10 62 absent nonbillable

My first inclination was to do a pivot table to come up with the total
current dollars. I don’t know how to do this now as there could be several
instances for an individual. The total days need to add up to 365. I need a
total dollar amount for the full year. I could have multiple changes
throughout the year. Is there a way for me to set this up in Excel to track
and at any given moment know what the total dollar amount is for the year?

 

[smartin]

The following is what I have for data:

Name Daily Rate Dates of Service Days Total
John Doe 20 6/1/09 – 5/31/10 365 7,300
John Doe 18 1/1/10 – 1/31/10 31 558
John Doe 22 3/1/10 – 3/31/10 31 682
John Doe 7/1/09 – 8/31/10 62 absent nonbillable

My first inclination was to do a pivot table to come up with the total
current dollars. I don’t know how to do this now as there could be several
instances for an individual. The total days need to add up to 365. I need a
total dollar amount for the full year. I could have multiple changes
throughout the year. Is there a way for me to set this up in Excel to track
and at any given moment know what the total dollar amount is for the year?

A few questions...

What "year"? Calendar year? Some other year?

Are the totals spurious (since different rates appear in overlapping
time spans)? Which rate takes precedence?

How should "absent" be handled with respect to rate?

I'm not actually not clear on your problem statement. Can you give a
couple dates and expected results (and how you got them)?