Completion at Year End Formula

[shornbuckle]

Hi - I need a formula that will show me a projection's percentage complete at year end. Every formula I find only shows completion through today.

 

[felixstraube]

Could you elaborate? It is not very clear what you need exactly

 

[Joe4]

Welcome to the Board!

In these situations, it is usually helpful if you could show us some sample data and expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[shornbuckle]

I list a set of duration dates for a project, i.e. 05/06/24 start 05/16/25 end. I want to know the completion percentage of this project by 12/31/2024. Every formula I can find only will show me the completion percentage through today.

 

[Joe4]

Formula in cell D2:

=(C2-A2)/(B2-A2)

 

[shornbuckle]

That works. I was hoping there was a formula where I didn't have to make an additional column but I can just hide it. Thanks!

 

[felixstraube]

You mean something like this?
(Taking the current year for the calculation)

Book6.xlsx
	A	B	C
1	Start Date	End Date	Year End Completion %
2	06/05/2024	16/05/2025	63.73%
3
Sheet5

Cell Formulas
Range	Formula
C2	C2	=(DATE(YEAR(TODAY()),12,31)-A2)/(B2-A2)

 

[Joe4]

That works. I was hoping there was a formula where I didn't have to make an additional column but I can just hide it. Thanks!

You did not mention that requirement (the question was a bit vague), but it is easy enough to do, as Felixstraube demonstrated.

 

[shornbuckle]

That formula is perfect. How should I wrap it with a MIN/MAX formula so that it stops at 100%? Lastly, I need to add in that if the start date is after the current year, i.e. 02/01/2025, the result should be zero.

 

[Joe4]

So, it appears your question is actually a bit more complicated than you first let on. That is why it is good to consider all cases, and show some examples of your data and expected results.
(The key to a good answer that works for you is asking a good, thorough complete question).

Formula in cell C2:

=IF(YEAR(A2)>YEAR(TODAY()),0,MIN((DATE(YEAR(TODAY()),12,31)-A2)/(B2-A2),1))