IF AND find Remaining Duration of a project within 2024
Hi,
I am looking for some help with a formula that will calculate the number of days a project will be active in the year 2024. Below are the formulas I currently have plugged. I know that I need to embed something into the Duration Remaining column that will not count days past 12/31/2024.Yet, I am stuck.
Total Project Duration: =NETDAYS([Start Date]@row, [Finish Date]@row)
Total Remaining Duration: =IF([Start Date]@row <= [Today's Date]@row, [Finish Date]@row - [Today's Date]@row, [Total Project Duration Days]@row)
Duration Remaining - 2024: =IF([Total Remaining Duration]@row >= [Days Remaining In 2024]@row, [Days Remaining In 2024]@row, [Total Remaining Duration]@row)
Days Remaining in 2024: =Date@row - TODAY()
I do think I may have figured out a formula that works (see below).....but I know that there is something more simple than what I have come up with. I would like to have fewer columns.
Duration Remaining - 2024 r1 =IF([Start Date]@row >= Date@row, 0, [Duration Remaining - 2024]@row)
If someone can help me out that would be great!
Thank you
Best Answer
-
@JamesB , thank you for your help. the formula above doesn't give me the information I needed, because I need the NETDAYS that fall within the year 2024 only. . However, I did figure it out.....Finally! Below is the formula. :)
=MAX(0, NETDAYS(MAX([Start Date]@row, [Today's Date]@row), MIN([Finish Date]@row, DATE(2025, 1, 1))))
Answers
-
@Allison Bishop Try this...
=NETDAYS([Start Date]@row, DATE(2024, 12, 31))
-
@JamesB , thank you for your help. the formula above doesn't give me the information I needed, because I need the NETDAYS that fall within the year 2024 only. . However, I did figure it out.....Finally! Below is the formula. :)
=MAX(0, NETDAYS(MAX([Start Date]@row, [Today's Date]@row), MIN([Finish Date]@row, DATE(2025, 1, 1))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!