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.

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

You would need to use an IF/AND statement to determine if

Start Date is less than or equal to the last day of the month

AND

End Date is greater than or equal to the first day of the month.

So for January you would use something along the lines of

=IF(AND([Project Start Date]@row <= DATE(2020, 1, 31), [Project End Date]@row >= DATE(2020, 1, 1)), 1)

Mike Wilday

@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.

All comments

Mike Wilday

Are you concerned about indicating the specific months? or are you more interested in getting a count of total months that a specific project was active? I could imagine that you could check to see if the current month number was greater than the start month number and less than the end date month number.

For February you could try something like

=IF(AND(2 >= Month([Project Start Date]@row), 2 <= Month([Project End Date]@row), 1)

If that works, then you could essentially do that for all the rest of the columns.

Mike Wilday

Yep, just tested out my theory. That will work for you. Just change the 2 to whichever month number that your are looking to add the 1 flag too.

Paul Newcome

You would need to use an IF/AND statement to determine if

Start Date is less than or equal to the last day of the month

AND

End Date is greater than or equal to the first day of the month.

So for January you would use something along the lines of

=IF(AND([Project Start Date]@row <= DATE(2020, 1, 31), [Project End Date]@row >= DATE(2020, 1, 1)), 1)

Paul Newcome

I just realized my solution is pretty much the same as @Mike Wilday's. The only thing I would change for his would be fixing the second MONTH function to include the appropriate parenthesis.

=IF(AND(2 >= Month([Project Start Date]@row), 2 <= Month([Project End Date]@row)), 1)

Additionally this will only work if they project start and end are in the same year. If your dates have different years such as Dec. 2019 - March 2020, this will not work.

Mike Wilday

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

=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.

@Mike Wilday and @Paul Newcome , thank you for your quick and elegant responses. I was over-complicating things in my head. Your outside view simplified things greatly.

@Mike Wilday Projects will wrap across years, but I think your revised approach solves that for me (I can suffer the once-a-year administrative effort to create new columns).

Thanks to you both!

Mike Wilday

Yes, the start date should be compared to the earliest date in the month. Glad we could help you see a result.

Paul Newcome

@Mike Wilday

No worries. Parenthesis can be a pain sometimes.

You also pulled a "Paul" and replicated my original formula with the only difference being the order you compared the dates in. Hahaha

Mike Wilday

@Paul Newcome Great minds think alike. And at close to the same speeds.

Paul Newcome

@Mike Wilday I have noticed this. I was just sitting here laughing out loud at that other thread about pulling the most recent updated.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.