Formula Required

[Johnboy28]

Hi - Would appreciate an formula to calculate the "Days on Feed" - I have calculated the 1st answer. 368 Days

Processed Date minus sale date if Processed equal "Yes" AND if Processed equal "No" Sales date minus today's day AND if Processed equals "MORT" Processed Date minus Sale Date
Thank you

Processed	Processed Date	Sale Date	Days on Feed
Yes	4/10/2023	6/10/2024	368
Yes	4/10/2023	8/10/2024
No	4/10/2023
No	4/10/2023
MORT	4/10/2023

 

[dermie_72]

Assuming Processed is Column A and Row 1. This formula should work.

=SWITCH(TRUE,A2="Yes",C2-B2,A2="No",TODAY()-B2,A2="MORT",B2-C2,"")

 

[Johnboy28]

=SWITCH(TRUE,A2="Yes",C2-B2,A2="No",TODAY()-B2,A2="MORT",B2-C2,"")

Perfect - Thank you very much.

 

[Peter_SSs]

Perfect - Thank you very much.

If your data is not very large then you probably will not notice the difference but I would recommend against using SWITCH if possible. The reason is that SWITCH takes the time and resources to evaluate every test and every calculation in the formula even if the first one turns out to be the one needed.
For example for the formula above for row 2 of your sample data, Excel performs the following steps

1. Tests to see if A2="Yes"
2. Calculates C2-B2
3. Tests to see if A2="No"
4. Calculates TODA()-B2
5. Tests to see if A2="MORT"
6. Calculates B2-C2
7. Stores "" in case none of the previous tests are True
8. Now goes back and looks at the first test and finds that it is True so returns the value calculated at step 2

That is, steps 3 to 7 did not need to be done.

The following formula should return the same results

=IF(A2="Yes",C2-B2,IF(A2="No",TODAY()-B2,IF(A2="MORT",B2-C2,"")))

For this formula Excel performs the following steps

1. Tests to see if A2="Yes", It is so
2. Calculates and returns C2-B2 and stops

None of the other tests/calculations are performed. This is a more efficient approach & seems sensible to me.

 

[Johnboy28]

If your data is not very large then you probably will not notice the difference but I would recommend against using SWITCH if possible. The reason is that SWITCH takes the time and resources to evaluate every test and every calculation in the formula even if the first one turns out to be the one needed.
For example for the formula above for row 2 of your sample data, Excel performs the following steps

1. Tests to see if A2="Yes"
2. Calculates C2-B2
3. Tests to see if A2="No"
4. Calculates TODA()-B2
5. Tests to see if A2="MORT"
6. Calculates B2-C2
7. Stores "" in case none of the previous tests are True
8. Now goes back and looks at the first test and finds that it is True so returns the value calculated at step 2

That is, steps 3 to 7 did not need to be done.

The following formula should return the same results

=IF(A2="Yes",C2-B2,IF(A2="No",TODAY()-B2,IF(A2="MORT",B2-C2,"")))

For this formula Excel performs the following steps

1. Tests to see if A2="Yes", It is so
2. Calculates and returns C2-B2 and stops

None of the other tests/calculations are performed. This is a more efficient approach & seems sensible to me.

Thank you. Can also try this.

 

[Peter_SSs]

You're welcome.