Formula to calculate between dates

[Jyggalag]

Hi all,

I have an overview that looks like this:

Days since beginning / ended?	Start date	End date
	01-10-2019	01-11-2023
	01-10-2017	01-11-2025
	01-10-2018	01-11-2028
	01-10-2016	01-11-2021
	01-10-2015	01-11-2018

And in Excel:

I want to create a formula in column A that does the following:
1) If the end date IS after our current date (as seen in C2 AND C5), the formula should respond with the amount of months that have passed since the project began
2) If the end date IS NOT after our current date, i want the formula to return with the value "COMPLETED".

Additionally, please see the following for column B:

If anybody can make a 2nd formula for column B above showing the process based on amount of days/months passed that would be amazing. So if a project has start date 01-06-2024 and end date 01-08-2024, it should be at around 50% as of today (01-07-2024) in the format "DD-MM-YYYY".

This is a complex task, but if anybody can help me with it, it would be greatly appreciated!

 

[awoohaw]

This, I think does what you ask, but I think there may be something overlooked. The last record shows as completed, when it isn't. And you say C6 and C10 should have a number of months only, but C9 also does.

the column B calculation just uses straight days, and not months. But the DateDif function can work with that as well.

Please take a look:

Book1
	A	B	C	D
1	Current Date	01-Jul-2024
2
3
4
5			Start date	End date
6	49		01-Oct-2019	01-Nov-2023
7	Completed		01-Oct-2017	01-Nov-2025
8	Completed		01-Oct-2018	01-Nov-2028
9	61		01-Oct-2016	01-Nov-2021
10	37		01-Oct-2015	01-Nov-2018
11	Completed	49.18%	01-Jun-2024	01-Aug-2024
Sheet1

Cell Formulas
Range	Formula
A6:A11	A6	=IF(D6<=$B$1,DATEDIF(C6,D6,"m"),"Completed")
B11	B11	=($B$1-C11)/(D11-C11)

 

[Peter_SSs]

I read it a little differently & think it may be this?

24 07 02.xlsm
	A	B	C	D
1	Current Date	02-Jul-24
2
3
4
5			Start date	End date
6	Completed		01-Oct-19	01-Nov-23
7	81	83.51%	01-Oct-17	01-Nov-25
8	69	57.03%	01-Oct-18	01-Nov-28
9	Completed		01-Oct-16	01-Nov-21
10	Completed		01-Oct-15	01-Nov-18
11	1	50.82%	01-Jun-24	01-Aug-24
Completion

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A6:A11	A6	=IF(B$1<D6,DATEDIF(C6,B$1,"m"),"Completed")
B6:B11	B6	=IF(A6="Completed","",(B$1-C6)/(D6-C6))

 

[Jyggalag]

I read it a little differently & think it may be this?

24 07 02.xlsm
	A	B	C	D
1	Current Date	02-Jul-24
2
3
4
5			Start date	End date
6	Completed		01-Oct-19	01-Nov-23
7	81	83.51%	01-Oct-17	01-Nov-25
8	69	57.03%	01-Oct-18	01-Nov-28
9	Completed		01-Oct-16	01-Nov-21
10	Completed		01-Oct-15	01-Nov-18
11	1	50.82%	01-Jun-24	01-Aug-24
Completion

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A6:A11	A6	=IF(B$1<D6,DATEDIF(C6,B$1,"m"),"Completed")
B6:B11	B6	=IF(A6="Completed","",(B$1-C6)/(D6-C6))

Hi both of you!

Sorry for my late reply!

This formula worked, thank you so much for your input, truly appreciated!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.