I have several columns to work off of. The first two are dates (start date
and a finish date). I also have an hourly grosse dollar profit (GDP) column.
What I want to do is add the hourly gross dollar column if the dates fall
within a certain guideline. For Example:
A B C D (GDP) E (date started)
(Estimated Date end)
1 - John Doe $12/hr $20/hr $8/hr 12/1/2005 2/1/2006
I want to show the grosse dollar amount for
John in the month of December,
but only for the month of December. I'll then want to show the grosse dollar
amount for John in the month of January, and only for the month of January
(this, of course, would be in a different cell).
To get really tricky, I want to create a rolling commission
on the start and end dates. The calculation would assume 8 hour work days,
but the work week would need to be based solely on Monday through Friday (and
exclude certain holidays). Basically, if I were to open the spreadsheet in
February, it would forecast an estimated amount of commission to be paid out
at the end of that month of February. In another cell, it would also show
what the estimated commission should be for the following month (March). If I
were to open it in March, then the commision calculator would show what we
have for March (for the current) and April (for the next).
So far I can calculate the estimated commission, but I
don't have a way to
base it on a calendar. I can only say that if every person were to start
working today, and go exactly one month, then here is what that commission
would be. As you can see, it's not all that practical.