Creating nested IF(AND) statement to produce correct data

T

tps49

New member
Joined
Oct 7, 2014
Messages
3
Reaction score
0
Points
0
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,
 
Should it be?

=IF(AND($G3<=T$1,T$1<$K3)=TRUE,$O3,0)
 
Thanks, NBVC! That worked partially...the only downside is that I'm still getting zeros for anything past a certain date (in this case, March '15) even if the project starts or extends past that date. Could that be a formatting issue, or is something still off with the formula?
 
Are the dates in Row 1 (starting I guess at T1) all listed as the first of the month? If so, then maybe you need to adjust the dates in the G and K to 1st of the months, via the formula...

e.g.. trry

=IF(AND(EOMONTH($G3,-1)+1<=T$1,T$1<=EOMONTH($K3,-1)+1)=TRUE,$O3,0)
 
No need to overcomplicate. The correct numbers were given with a slightly-modified version of your original suggestion:

=IF(AND($G3<=T$1,T$1<=$K3)=TRUE,+$O3,0)
 
Well, I wasn't sure how exactly your dates were set up... so great that you got it.
 
You must log in or register to reply here.
Back
Top