Monthly Allocation Variance Formula Help!

JenMC

Hi, I am struggling with my formula to calculate my monthly percentages and show the variance across each month as the projects approaches the end. For instance, we have a project that may have started 10/1/2024 and is going to 10/12026, It shows 0% not acknowledging the months or years in between. Or if the project starts in Jan 2025 and ends in May 2026 or later, it will not calculate further than May 2025. It isn't acknowledging years outside of 2025. It calculates perfectly for the current year so it seems, but not outside of current year.

The formula takes the start and end date and pulls the month number then calculates time worked into a monthly percentage then it has a variance to reduce with each passing month. Not sure if it is right but I have been working on this for 2 days and am stuck!

Here is the formula I am using:

=IF(AND(MONTH(DATE(YEAR(Start@row), 1, 1)) <= MONTH(End@row), MONTH(DATE(YEAR(Start@row), 1, 1)) >= MONTH(Start@row)), ([Hours Worked Weekly]@row / 8 / (MONTH(End@row) / MONTH(Start@row) * 0.1 * 4.33 + 1)), 0)

Here is a screenshot of my table.

prime_nathaniel

@JenMC

Could you please manually put a sheet together with 1 or 2 entries showing what it should calculate out to. Your formula and goal is a bit hard to follow. What I can say is normally, you do NOT want to named months for columns if you can avoid it. It is better to do 1 month out, 2 months out, etc etc as this becomes variable and can scale past 1 year much easier.

That said, if you do need to have named month columns you need more columns for more years and the formula will change when you cross a year in timing.

As far as reverse engineering your formula, it formats as

=IF(

AND(
MONTH(DATE(YEAR(Start@row), 1, 1)) <= MONTH(End@row),
MONTH(DATE(YEAR(Start@row), 1, 1)) >= MONTH(Start@row)
),

([Hours Worked Weekly]@row / 8 / (MONTH(End@row) / MONTH(Start@row) * 0.1 * 4.33 + 1)),

0

)

What I can't figure out is why do you do Month(date(year(Start@row), 1, 1)), you are always setting the month component to 1 as date(year, month, day) is the formula being used and it is hard coded to 1, so you are hard coding the value you are month()'ing out of the date. So why not just replace that entire thing with and(1 < = month(end@row), 1 >= month(start@row)) where 1 represents January.

Okay so let's assume you then have the following logical "if" criteria with that change made,

"If January is between the start month and the end month" THEN "([Hours Worked Weekly]@row / 8 / (MONTH(End@row) / MONTH(Start@row) * 0.1 * 4.33 + 1))" ELSE 0

Let's look at your hour scalar calculation,

Hours Worked Weekly = 1 in your Oct example
1 / 8 / 10 / 10 * .1 * 4.33 + 1 = 1.00054 for your January column

A lot of these values I am not sure what the purpose is either.
- Can you explain why if working 1 hour a week do we divide by 8?
- Also what is the purpose of dividing month by month?
- Then why do you have .1 and .433 as two separate scalars rather than just "* .433"? I think maybe the .1 should really be the variable of weekly allocation, but this is not what is represented in the formula at present
- And then finally what does the + 1 represent

What I think your goal here is:
Given X working hours per week and Y % allocation of those X hours towards row. What is the amount of work completed per month?

But I could also see it being:
Given X working hours per week and Y % allocation of those X hours towards row. What is the relative utilization in January given all other rows?

Are either of those what you are looking to do?
Let me know the exact business case and then should be able to clean that up for you

JenMC

Thank you for your comment, I actually took over this sheet and was wondering the same thing, Basically, my manager wants to take the allocation of a project and see the progression throughout the time of the project for each employee and their project. He wants to see the monthly percentage based off of the weekly hours they are working throughout the project. He wanted to be able to see the format with the months out to the right and the employee and project to the left. I hope that makes sense.