How to display an amount from a cell if the date of another cell is between 30 and 60 days

[Angeleyez]

I'm actually trying to create a helper cell in excel for a pivot table. I need help with creating a formula to display a value from another cell if the date is between 30 and 60 days from a designated cell with a date.

Here is an example:

So for the table below, I would want the "Copay amount" to show up under the correlating "over 'N'" column.

Invoice date Copay amount Over 30 Over 60 Over 90
2/1/2019 30
4/5/2019 120
1/22/2019 27
3/12/2019 82
5/12/2019 25


Let me know if I need to clarify further. Thank you

[AliGW]

Welcome to the forum!

In C2:

=IF(AND(B2>30,B2<=60),B2,"")

In D2:

=IF(AND(B2>60,B2<=90),B2,"")

InE2:

=IF(B2>90,B2,"")

[Angeleyez]

Thank you so much! I was adding an extra "AND(" function to mine.

This worked great, but now I am running into an issue that if a cell in the "Invoice Date" column is blank, then it calculates the days wrong. Is there a way to code it to treat a cell differently if it is blank?

(thanks again! I'm so glad to join the forum here!)

[AliGW]

Maybe this?

=IF(A2="","",IF(AND(B2>30,B2<=60),B2,""))
=IF(A2="","",IF(AND(B2>60,B2<=90),B2,""))
=IF(A2="","",IF(B2>90,B2,""))

[Angeleyez]

I'll try this, thanks!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.