Indicating Each Month A Project Is Active?

Community,

I'm struggling to indicate which months a project is active given a project start and end date. If a project starts on 09 January 2020 and ends on 09 March 2020, it would be active January, February, March. I would like to represent this as follows:

Showing that the project was active in its Start Month and End Month is easy (as in Projects A and B above). For example, I can use...

=IF(OR(MONTH([Project Start Date]1) = 1, MONTH([Project End Date]1) = 1), 1, 0)

...to do this for January.

Where i'm stuck is how to determine the intervening months (as in Projects C and D above). Any ideas?

Thanks in advance.

Best Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 01/09/20 Answer ✓

    @Paul Newcome Thanks for noticing the missing parenthesis. I edited my original post to include it.

    And you're right projects that extend over multiple years would be much more complicated, but I think that it could still be done by comparing the year and month. If you actually used the last date of the month you were checking against you could just compare the dates altogether.

    =IF(AND(Date(2019,02,1) >= [Project Start Date]@row, Date(2019,02,27) <= [Project End Date]@row, 1)

    @RK Are you looking at a year's work at one time or would this project extend over multiple years? IF my above solution works for you then please mark it as a correct answer.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!