# Nested Ifs function with date and calculation

#### Rcaaa

##### New Member
=IFS(C14=YEAR(D8),(E8/H3),C14=YEAR(D9),(E9/H4),C14=YEAR(D10),(E10/H5))

Above is my formula that is not working.
I am trying to get a spreadsheet that automatically distributes the budget evenly based on each year by month. Does anyone have advice on how to correct my formula?

 C D E F G H 2 Project Name XYZ Year Month 3 Start Date 2/5/19 2019 1 4 Completion Date 12/5/20 2020 12 5 2021 12 6 7 8 FY Budget 2019 \$400,000 9 FY Budget 2020 \$200,000 10 FY Budget 2021 \$100,000 Month Planned Cost 12/5/19 This is where the formula would go 1/5/20

<tbody>
</tbody>

Last edited:

#### jondavis1987

##### Active Member
=IF(C14=YEAR(D8),(E8/H3),IF(C14=YEAR(D9),(E9/H4),IF(C14=YEAR(D10),(E10/H5),"")))

IFS is not a formula. If you want a nested if, in the "if false" section you need to put IF(logic test, if true, if false)

The formula I provided should give you a blank cell if it does not equal any of those cells

• Rcaaa

#### jondavis1987

##### Active Member
If it has to be one of those cells, this formula will also work for you

=IF(C14=YEAR(D8),(E8/H3),IF(C14=YEAR(D9),(E9/H4),E10/H5))

#### DanteAmor

##### Well-known Member
=IF(C14=YEAR(D8),(E8/H3),IF(C14=YEAR(D9),(E9/H4),IF(C14=YEAR(D10),(E10/H5),"")))

IFS is not a formula. If you want a nested if, in the "if false" section you need to put IF(logic test, if true, if false)

The formula I provided should give you a blank cell if it does not equal any of those cells
IFS function
Excel for Office 365 Excel for Office 365 for Mac Excel 2019 Excel 2016 More...

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45

• jondavis1987

#### jondavis1987

##### Active Member
Well, learn something new everyday. I've only got the 2013 version. A better nested if function would have been nice a year ago when i was setting stuff up

#### DanteAmor

##### Well-known Member
All the time we are learning, and especially in excel • mcmahon_66

#### jondavis1987

##### Active Member
Well, I don't know how to give you an ifs formula since i can't play with it, but the one i provided should work.

#### DanteAmor

##### Well-known Member
=IFS(C14=YEAR(D8),(E8/H3),C14=YEAR(D9),(E9/H4),C14=YEAR(D10),(E10/H5))

Above is my formula that is not working.
I am trying to get a spreadsheet that automatically distributes the budget evenly based on each year by month. Does anyone have advice on how to correct my formula?

 C D E F G H 2 Project Name XYZ Year Month 3 Start Date 2/5/19 2019 1 4 Completion Date 12/5/20 2020 12 5 2021 12 6 7 8 FY Budget 2019 \$400,000 9 FY Budget 2020 \$200,000 10 FY Budget 2021 \$100,000 Month Planned Cost 14 12/5/19 This is where the formula would go 1/5/20

<tbody>
</tbody>
But in C14 do you have a date?
In D8 you only have the year?

Then
This:
C14=YEAR(D8)

Should be:
YEAR(C14) = D8

Do you need to compare cell by cell or rather look for the year of C14 in a range of cells D8: D10 and get the value of column E?

• Rcaaa

#### jondavis1987

##### Active Member
Going off his table and the labels, D8 is a date. I didn't realize he's also got dates where C14 should go. If dates are in C14, this is the way to go

=IF(YEAR(C14)=YEAR(D8),(E8/H3),IF(YEAR(C14)=YEAR(D9),(E9/H4),IF(YEAR(C14)=YEAR(D10),(E10/H5),"")))

=IFS(YEAR(C14)=YEAR(D8),(E8/H3),YEAR(C14)=YEAR(D9),(E9/H4),YEAR(C14)=YEAR(D10),(E10/H5))

#### Rcaaa

##### New Member
Hello,

Thank you both for your help. It is working now!

Yes, I have a date in C14 (Now D14 after adding in a column to separate the year from the 12/5/19 date)
I also created a column with a monthly budget rate instead of dividing it in the formula(400,000/1 month - AKA E8/H3).

=IF(\$D14=(\$E\$8),(\$J\$3),IF(\$D14=(\$E\$9),(\$J\$4),IF(\$D14=(\$E\$10),(\$J\$5),"")))

Thanks again,

RCAAA