Count & Multiply Months for Hours Budgeting
 

What I need:
I need to automatically calculate hours for a particular work group if a
user answers a question "YES" in a cell on another sheet. Our fiscal
calendar is July thru June.
I have 5 cells, one for each of the next 5 fiscal years (or portion thereof
for the current year), for which I need to calculate these hours.
The calculation would be: Number months remaining in fiscal year x 4 hours
+12 hours for the first month in the first year only.

What I have:
CELL A1 - A user enters the starting month and year a project will
start(from a drop down list - format is Date Oct-06) .
CELL A2 - He/she then enters in the next cell the duration (format - whole
numeber) representing months that the project will run (projects can run from
1 month to 60 months).
CELL A3 - calculates the ending month/year using A1 & A2 (format is Date
Oct-06).

Count & Multiply Months for Hours Budgeting
 

If the first year's date was in A8:

=((12-(MONTH(A8)))*4)+12

If July was the month,
(12-7)*4+12

Is that what you want?

Kathy


PSU35 wrote:
What I need:
I need to automatically calculate hours for a particular work group if a
user answers a question "YES" in a cell on another sheet. Our fiscal
calendar is July thru June.
I have 5 cells, one for each of the next 5 fiscal years (or portion thereof
for the current year), for which I need to calculate these hours.
The calculation would be: Number months remaining in fiscal year x 4 hours
+12 hours for the first month in the first year only.

What I have:
CELL A1 - A user enters the starting month and year a project will
start(from a drop down list - format is Date Oct-06) .
CELL A2 - He/she then enters in the next cell the duration (format - whole
numeber) representing months that the project will run (projects can run from
1 month to 60 months).
CELL A3 - calculates the ending month/year using A1 & A2 (format is Date
Oct-06).

Count & Multiply Months for Hours Budgeting
 

Kathy

Not sure why you mention cell A8?

I see what your doing but it doesn't address the possibilty of multiple years.
A1 gives the start month/year but A3 gives the ending month/year. I will
need each of the 5 cells (that calculate the hours) to decern what is
ablicable to each of them -ie. how many months of a given fiscal year will
hours be required.

I have been trying to use YEAR and MONTH in my formulas along with IF, AND,
& a few other commands, but i just can't seem to get it to do what I need.

"KathyC" wrote:

If the first year's date was in A8:

=((12-(MONTH(A8)))*4)+12

If July was the month,
(12-7)*4+12

Is that what you want?

Kathy


PSU35 wrote:
What I need:
I need to automatically calculate hours for a particular work group if a
user answers a question "YES" in a cell on another sheet. Our fiscal
calendar is July thru June.
I have 5 cells, one for each of the next 5 fiscal years (or portion thereof
for the current year), for which I need to calculate these hours.
The calculation would be: Number months remaining in fiscal year x 4 hours
+12 hours for the first month in the first year only.

What I have:
CELL A1 - A user enters the starting month and year a project will
start(from a drop down list - format is Date Oct-06) .
CELL A2 - He/she then enters in the next cell the duration (format - whole
numeber) representing months that the project will run (projects can run from
1 month to 60 months).
CELL A3 - calculates the ending month/year using A1 & A2 (format is Date
Oct-06).