# Nested IF / AND Statements

Options

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.

Tags:

• ✭✭✭✭✭✭
Options

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, "")

• Options

Thank you so much that worked!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!