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
-
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 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
-
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.
-
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.
-
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)
-
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.
-
@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.
-
@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!
-
Yes, the start date should be compared to the earliest date in the month. Glad we could help you see a result.
-
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
-
@Paul Newcome Great minds think alike. And at close to the same speeds.
-
@Mike Wilday I have noticed this. I was just sitting here laughing out loud at that other thread about pulling the most recent updated.
Help Article Resources
Categories
Check out the Formula Handbook template!
