☰

根据范围将日期转换为数字

tag-icon • tag-icon worksheet-function google-spreadsheets
根据范围将日期转换为数字

我正在尝试编写一个公式，根据日期所属的范围将其转换为数字，但我一直收到解析错误消息。有人能帮忙吗？这是公式。我希望它能够正常工作，例如，如果 $F2 中的日期是 28/09/2006，它将返回值 6。

TIA 提供任何帮助

=IF(AND($F2>="2016/09/01"+0,$F2<="2015/08/31"+0),"19-20",IF(AND($F2>="2015/09/01"+0,$F2<="2016/08/31"+0),"18-19",IF(AND($F2>="2014/09/01"+0,$F2<="31/ 08/2015"+0),"FS1",如果(AND($F2>="01/09/2013"+0,$F2<="31/08/2014"+0),"FS2",如果(AND($F2>="01/09/2012"+0,$F2<="31/08/2013"+0),"R",如果(AND($F2>="01/09/2011"+0 ,$F2<="31/08/2012"+0),"1",如果(AND($F2>="01/09/2010"+0,$F2<="31/08/2011"+0),"),"2",如果(AND($F2>="01/09/2009"+0,$F2<="31/08/2010"+0),"3",如果(AND($F2>="01/0 9/2008"+0,$F2<="31/08/2009"+0),"4",如果(AND($F2>="01/09/2007"+0,$F2<="31/08/2008"+0),"5",如果(AND($F2>="01/09/2006"+0,$F2<="31/08/2007"+0),"6"))))))))))))

答案1

我认为这会起作用：

=IF(AND($F2>="01/09/2016"+0,$F2<="08/31/2015"+0),"19-20",IF(AND($F2>="01/09/2015"+0,$F2<="08/31/2016"+0),"18-19",IF(AND($F2>="01/09/2014"+0,$F2<="08/31/2015"+0),"FS1",IF(AND($F2>="01/09/2013"+0,$F2<="08/31/2014"+0),"FS2",IF(AND($F2>="01/09/2012"+0,$F2<="08/31/2013"+0),"R",IF(AND($F2>="01/09/2011"+0,$F2<="08/31/2012"+0),"1",IF(AND($F2>="01/09/2010"+0,$F2<="08/31/2011"+0),"2",IF(AND($F2>="01/09/2009"+0,$F2<="08/31/2010"+0),"3",IF(AND($F2>="01/09/2008"+0,$F2<="08/31/2009"+0),"4",IF(AND($F2>="01/09/2007"+0,$F2<="08/31/2008"+0),"5",IF(AND($F2>="01/09/2006"+0,$F2<="08/31/2007"+0),"6")))))))))))

我将日期从 31/08/2015 改为 08/31/2015，并删除了,”）在公式中间，毫无理由地出现，并在末尾删除了一个多余的括号。

相关内容