 # Percentage Calculation Based On Months & Dates

• Hi all,

Here's what I want to do:

I want to know how to base a formula upon a specific recurring month, and another formula on all other months. One formula for every June, or every January. A second formula for every other month.

For example, I want to recognize 50% of yearly revenue in January, but (50%/11) of yearly revenue in all other months. But I want this to repeat depending on how long the contract is. So EACH january it would use formula 1, and every other month, formula 2.

Thanks so much!!

- Jeff

• Re: Formula Based On Specific Recurring Month

Quote

For example, I want to recognize 50% of yearly revenue in January, but (50%/11) of yearly revenue in all other months. But I want this to repeat depending on how long the contract is. So EACH january it would use formula 1, and every other month, formula 2.

Since you didn't post a sample workbook or provide much detail, here is a possible solution based on assumptions:

Cell A1 = yearly total
Cell B1 = Date
Cell C1 = IF(MONTH(B1)<2,A1*.5,(A1*.5)/11)

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Percentage Calculation Based On Months &amp; Dates

Ok, I've uploaded an excel spreadsheet to give you an idea of what I'd like to be done.

Where it says, "Revenue recognized at these dates" I would like a formula in the grayed boxes which places the correct revenue under those months according to the starting date of the contract, the contract length, and the amount of revenue per year.

Thanks very much,

- Jeff

## Files

• Re: Percentage Calculation Based On Months &amp; Dates

Try this in your H3 and copy across to the right:

=IF(MONTH(\$B\$2)=MONTH(H\$1),\$A\$2*0.5,(\$A\$2*0.5)/11)

-------------
[FONT="Comic Sans MS"]Averil Pretty[/FONT]

• Re: Percentage Calculation Based On Months &amp; Dates

ah yes, i didn't know about that function. thanks.

• Re: Percentage Calculation Based On Months &amp; Dates

Quote

ah yes, i didn't know about that function. thanks.

Which function do you mean? I supplied both of them to you in my initial post, you only had to adapt them to your needs.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Percentage Calculation Based On Months &amp; Dates

you're right. sorry I misunderstood what the MONTH function was doing at first.

• Re: Percentage Calculation Based On Months &amp; Dates

Believe it or not, the Excel Help file is quite decent. :wink:
I use it often, as should everyone, as the best first source of information.

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!