How do I bring a cell amount considering the start and end date correspond to that particular month?
HI everyone,
Could someone help me solve this formula? I need to have the "revenue per month amount" cell (in gray) added to the corresponding months, considering the project will be happening in that month by reading from the estimated start and end date columns.
I am currently using the following formula which is using helper columns/cells:
=IF(AND($[Estimated Projects Start Date]@row >= aug@row, $[Estimated Project End Date]@row >= aug$1), $[Revenue per month (weighted)]@row, "")
The helper columns have the start of the month (eg: augs@row = 01/08) and the aug$1 would have the last day of the month.
But It's not working for all dates. I tried changing the but I feel there's a bit of logic still missing.
Appreciate any help!
Answers

Hey Paula,
Did you try using the MONTH formula in your formula to extract the month's number out of the date?
Which column are you using to determine where the amount would go? Estimated start or End date column?
Itai Perez
Project Manager  Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

Hi Itai, thank you for your reply.
I have tried using the month, but I'm definitely doing something wrong. I am trying to have the amount go under the specific month in the columns, where it shows Jul23  Aug 23  Sep 23. I tried using the month number on the row under the column but didnt work.
I'm happy to retry anything as I believe I might be missing a small detail. What would you recommend?

Hey Paula,
I think because you have dates that are overlapping and are not just inside a month the formula will not work.
Also, is it possible that the End date operator is meant to be <=? or am I missing someting?
=IF(AND($[Estimated Projects Start Date]@row >= aug@row, $[Estimated Project End Date]@row >= aug$1), $[Revenue per month (weighted)]@row, "")
This is a formula I built that does what your formula does but with no helper columns. I think you should decide, if a project is over a month, do you want to revenue to go to the end date month? I can help you build it once I know that.
Hopefully I understood what you meant and this is helpful!
Itai Perez
Project Manager  Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

July would look like this:
=IF(AND(MONTH([Estimated Projects Start Date]@row) <= 7, MONTH([Estimated Project End Date]@row >= 7), $[Revenue per month (weighted)]@row, "")
Just change the 7s to the appropriate month numbers.
Help Article Resources
Categories
Check out the Formula Handbook template!