Any Excel geniuses out there?

Question

Imagine a given calendar month, lined up by weeks, starting monday and ending sunday. At the beginning is the last few days of the previous month, and at the end is the first days of the next month. Now imagine you have a sales goal of X. How do you write a formula that will count the days in the week, ignoring the previous and next month, check the number of days in the month (28,29,30,31?) and pop out a sales goal of W=X*D, where W is the goal for only that week and D is the decimal that corresponds to how much weight the week is given toward the monthly total.

For instance, if February started on a monday and ended on a sunday, each week would be given a weight of .25.

I finally got this crappy spreadsheet automated to the point where all you have to do is type in the names of each employee once and it populates the whole sheet, the hours they work in a day, the monthly goal, and the date of the first monday before the month starts, which assigns the rest of the dates. With all that automation, it seems silly to quit there and recalculate a week's fraction every time.

10 points to the answer that helps me the most.

Answer 1

I am not worthy

you are the geniuses

sounds like you have it all done

I will be interested to see the other answers

sorry I couldn't help

Answer 2

All I can give you is what I figured as the value of each day in a full or partial week.  Since I see it as ‘Mon –Fri’ @ 01.25 per day…. your figure of 25 for the month of February that starts and ends with a full week.

The same figure of @01.25 per day is true for each work day before or after a whole week within any month.

I am assuming that you are only working with a five-day workweek (Monday -Friday)

You will have to deduct down-time, which is holidays, vacations and half-days accordingly, absenteeism does not count in this case.

You will now have to take whatever figure or goal Weekly, Monthly or yearly… be it Sales dollars, time and material, or man-hours for each employee.

Divide  the whole or sale goal by the number of days to be worked within a fiscal years  @01.25 ….you now have a per day figure….if you need to go beyond that ….you can divide the new day figure by the hour or minute. Normal workday per fiscal year is somewhere around 244 minus the downtime (see Above)

If that helps you …. Fine… just think of me as an old vocational high school drop-out

Answer 3

Write a VBA function that receives the date cell as an input parameter and returns the fraction you want...

And set your profile to allow emails.  It doesn't give anybody your address, it just allows them to contact you through Yahoo.