基于单元格值显示特定的文本(日期)

I'm working on displaying a specific text in a cell based on a date value in another cell.

我正在根据另一个单元格中的日期值在单元格中显示特定的文本。

Scenario:

场景:

On Cell A2 = this is where the date in inputed

在单元格A2 =这是输入的日期

On cell B2 = this counts the date difference and display it on year and month and days

在单元格B2 =上计算日期差异，并在年、月和日显示它

On cell C2 = this should display a specific text/value based on Cell B2.

在cell C2 =上，应该显示基于cell B2的特定文本/值。

I have this written on cell B2 :

我写在细胞B2上

=IF(DATEDIF(A2,today(),"y")=0, "", DATEDIF(A2,today(),"y") & " years ") & IF(DATEDIF(A2,today(),"ym")=0,"", DATEDIF(A2,today(),"ym") & " months ") & IF(DATEDIF(A2, today(), "md")=0, "", DATEDIF(A2, today(), "md") & " days")

Its output is formatted to display (e.g 1year 2months 3days). Now what I want to do is to display specific value on cell C2 based on the output from B2. This is the value and ranges:

它的输出被格式化为显示(e)。g 1年2个月3天)。现在我要做的是根据B2的输出在C2上显示特定的值。这是价值和范围:

If cell B2 = 1day - 3months this should display "Tadpole"

如果细胞B2 = 1天- 3个月这应该显示“蝌蚪”

If cell B2 = 3months and 1 day - 6months this should display "young frog"

如果细胞B2 = 3个月1天- 6个月那么就会出现"小青蛙"

If cell B2 = 6months and 1day - 1year this should display "mature frog"

如果细胞B2 = 6个月1天- 1年，则显示为“成熟蛙”

If cell B2 = 1year and 1 day - onwards this should display "Old Frog"

如果单元格B2 = 1年1天，则显示“老青蛙”

I have this code to display specific text, but it doesn't work well in this scenario:

我有这个代码来显示特定的文本，但是它在这个场景中不能很好地工作:

=IF(B2="","",IF(AND(B2>=0,B2<=3),"Tadpole"

Sample Output:

样例输出:

1 个解决方案

#1

Here is the formula you want =IF(OR(AND(DATEDIF(A2,TODAY(),"y")=0,DATEDIF(A2,TODAY(),"ym")<=2),AND(DATEDIF(A2,TODAY(),"y")=0,DATEDIF(A2,TODAY(),"ym")=3,DATEDIF(A2,TODAY(),"md")=0)),"Tadpole", IF(OR(AND(DATEDIF(A2,TODAY(),"y")=0,DATEDIF(A2,TODAY(),"ym")<=5), AND(DATEDIF(A2,TODAY(),"y")=0,DATEDIF(A2,TODAY(),"ym")=6,DATEDIF(A2,TODAY(),"md")=0)),"Young Frog", IF(OR(DATEDIF(A2,TODAY(),"y")=0, AND(DATEDIF(A2,TODAY(),"y")=1, DATEDIF(A2,TODAY(),"ym")=0, DATEDIF(A2,TODAY(),"md")=0)), "Mature Frog", "Old Frog")))

这是你想要的公式=如果(或者(和(DATEDIF(A2,今天(),“y”)= 0,DATEDIF(A2,今天(),“ym”)< = 2),(DATEDIF(A2,今天(),“y”)= 0,DATEDIF(A2,今天(),“ym”)= 3,DATEDIF(A2,今天(),“医学博士”)= 0)),“蝌蚪”,如果(或(和(DATEDIF(A2,今天(),“y”)= 0,DATEDIF(A2,今天(),“ym”)< = 5),(DATEDIF(A2,今天(),“y”)= 0,DATEDIF(A2,今天(),“ym”)= 6,DATEDIF(A2,今天(),“医学博士”)= 0)),“小青蛙”,如果(或(DATEDIF(A2,今天(),“y”)= 0,和(DATEDIF(A2,今天(),“y”)= 1,DATEDIF(A2,今天(),“ym”)= 0,DATEDIF(A2,今天(),“医学博士”)= 0)),“成熟的青蛙”,”老青蛙”)))