Help with if(and) formula please

[Jmoz092]

I'm in need of a formula that compares a couple things and gives an answer, but I'm not having any luck. Any help would be appreciated.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Begin Date[/TD]
[TD]End Date[/TD]
[TD]true/false[/TD]
[TD]today[/TD]
[TD]active/expired[/TD]
[/TR]
[TR]
[TD]11-1-17[/TD]
[TD]11-30-17[/TD]
[TD]true[/TD]
[TD]=today()[/TD]
[TD]active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]false[/TD]
[TD]=today()[/TD]
[TD]n/a
[/TD]
[/TR]
[TR]
[TD]11-1-17[/TD]
[TD]11-2-17[/TD]
[TD]true[/TD]
[TD]=today()[/TD]
[TD]expired[/TD]
[/TR]
</tbody>[/TABLE]


I have 2 columns with a begin and end date.
I have a static cell that contains =today().
I have a column that contains true or false values

I need (in the active/expired/na column) to compare those in the active/expired column.

If the end date is less than today, = active
if the true/false column is false, = n/a
if the end date is in the past (compared to today), = expired

Thank you

 

[estevaoba]

Assuming your data are in range A1:E4, try this:

[TABLE="width: 87"]
<tbody>[TR]
  [TD="width: 87"]=IF(C2="False","n/a",IF(B2>=D2,"active","expired"))[/TD]
[/TR]
</tbody>[/TABLE]

 

[NiMip]

Hi there, I think your description is a little confused, because "If the end date is less than today" and "if the end date is in the past (compared to today)" are the same thing? But I think I know what you're after. Try the following:

[TABLE="class: grid, width: 486"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Begin Date[/TD]
[TD="align: center"]End Date[/TD]
[TD="align: center"]true/false[/TD]
[TD="align: center"]today[/TD]
[TD="align: center"]active/expired[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]11/01/2017[/TD]
[TD="align: center"]11/30/17[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]28/11/2017[/TD]
[TD="align: center"]=IF(C2,IF(B2>D2,"Active","Expired"),NA())[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]28/11/2017[/TD]
[TD="align: center"]Fill from above[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]11/01/2017[/TD]
[TD="align: center"]11/02/2017[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]28/11/2017[/TD]
[TD="align: center"]Fill from above[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jmoz092]

Thank you for the responses. @NiMip, that works. Thank you for seeing past my poor explanation