IF AND find Remaining Duration of a project within 2024


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

  • Allison Bishop
    Allison Bishop ✭✭✭✭
    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))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!