How do I bring a cell amount considering the start and end date correspond to that particular month?

PaulaJacques

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!

Itai Perez

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?

PaulaJacques

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?

Itai Perez

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!

Paul Newcome

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.