Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
count number of months year to date | Excel Worksheet Functions | |||
Count number to reach a cumulative value | Excel Worksheet Functions | |||
Difference between two dates in months with decimals | Excel Worksheet Functions |