Creating nested IF(AND) statement to produce correct data

[tps49]

Hi there,

I am working with a spreadsheet that deals with construction costs that is giving me trouble. In this spreadsheet, I am given the total cost of construction as well as the start and end date for the project. My goal is to approximate (on a monthly basis) what the total spend per project is. For instance, if the project costs $10mm and lasts from Jan-May 2015, I want Excel to show $2mm in each cell from Jan-15 to May-15 and shows zeros in all other months and years.

The current formula that I'm using is:

=IF(AND($G3>T$1,T$1<$K3)=TRUE,+$O3,0)

Where column G is the project start date (formatted as mm/dd/yyyy), column T is the month and year (formatted as mmm-yy, or Jan-15), column K is the project end date, and column O is the approximated monthly spend. My logic, then, is trying to say:

- If the project start date is greater than or equal to the month and year AND

- If the project end date is less than or equal to the month and year, place the monthly spend approximation in this cell.

Currently, my formula is taking projects greater than or equal to 2014 and placing the monthly approximation in all twelve months. For the life of me, I cannot figure out why this is...

Don't hesitate to let me know if I can help with further information. Any insight is much appreciated!

Thanks,

 

[tps49]

Apologies if any of this wording is unclear. Let me know if I can clarify something.

 

[ChrisBM]

Not 100% clear as to why it is not working.

As long as there is a date in Column T, it should be working. My advice is check that it is a date and not text.


n.b. Just for tidyness, you do not need =TRUE in the IF Formula.

 

[tps49]

Thanks, ChrisBM. I checked to make sure that column T was a date, and the formula is still not displaying the correct numbers (I'm getting values in only 2014 - regardless of if the project starts in '15, '16, etc. - and getting that value until the month of the proposed project. For instance, if the project was to start in October 2014, I have the monthly spend value recording in Jan-14 through Oct-14.)

Other thoughts?

 

[tps49]

An update to this: if I modify the formula to:

=IF(AND($G3<=T$1,T$1<$K3)=TRUE,$O3,0)

Then it works for every date up until March '15: I'm still getting zeros if the project starts or continues through that date.