Nested IF / AND Statements

auream00

I'm trying to put together formulas that will allow me to report forecasted labor across the months a project is active. I have all the formulas working individually but can't figure out how to group them into a singular formula. For example:

• Project Start: 6/17/24
• Project End: 8/21/24
• Forecast Cost: $10,000

The way I think this should work is that four date formulas need to be accounted for:

1. Work starts and ends in the same month = full forecasted amount
   1. =IF(AND(MONTH(Start@row) = 6, MONTH(END@row) = 6), [Forecast Cost]@row, "")
2. Work starts for the specified month but does not finish in the same month = calculate the number of days in the starting month and multiply by the daily cost
   1. =IF(AND(MONTH(Start@row) = 6, MONTH(End@row) > 6), StartMoDays@row * DailyCost@row, "")
3. Work doesn't start or end in the specified month = monthly forecast cost
   1. =IF(AND(MONTH(Start@row) < 6, MONTH(End@row) > 6), DailyCost@row * 30, "")
4. Work ends in that month = number of days in the end month multiplied by the daily cost
   1. =IF(AND(MONTH(Start@row) < 6, MONTH(End@row) = 6), EndMoDays@row * DailyCost@row, "")

This is how I've tried to assemble the relevant formulas:

=IF(AND(MONTH(Start@row) = 6, MONTH(END@row) = 6), [Forecast Cost]@row, ""), IF(AND(MONTH(Start@row) = 6, MONTH(End@row) > 6), StartMoDays@row * DailyCost@row, ""), IF(AND(MONTH(Start@row) < 6, MONTH(End@row) > 6), DailyCost@row * 30, ""), IF(AND(MONTH(Start@row) < 6, MONTH(End@row) = 6), EndMoDays@row * DailyCost@row, "")

NOTE: I do adjust the formula months based on the month they are entered in.

Nic Larsen

It looks like you're closing your arguments so it can't work through the list. Try it like this:

=IF(AND(MONTH(Start@row) = 6, MONTH(END@row) = 6), [Forecast Cost]@row, IF(AND(MONTH(Start@row) = 6, MONTH(End@row) > 6), StartMoDays@row * DailyCost@row, IF(AND(MONTH(Start@row) < 6, MONTH(End@row) > 6), DailyCost@row * 30, IF(AND(MONTH(Start@row) < 6, MONTH(End@row) = 6), EndMoDays@row * DailyCost@row, "")

auream00

Thank you so much that worked!