下面的公式将时间戳转换为天前。但我想把它转换成几周前,几个月前和几年前。如何修改这个公式
=IFERROR( ARRAYFORMULA(IF(LEN(A3:A),DATEDIF(VLOOKUP(A3:A,Backend!A2:U,18,FALSE),TODAY(),"D")&if(DATEDIF(VLOOKUP(A3:A,Backend!A2:U,18,FALSE),TODAY(),"D")=1," Day"," Days"),)),)
原始格式如下:后端中的7/27/2021 16:11:14
表R列
我建议您保持输出为数字,并添加显示格式为[=1]0" day"; 0" days"
的单位
:
=IFERROR( ARRAYFORMULA(IF(LEN(A3:A),DATEDIF(VLOOKUP(A3:A,Backend!A2:U,18,FALSE),TODAY(),"D"),)),)
:
=IFERROR( ARRAYFORMULA(IF(LEN(A3:A),DATEDIF(VLOOKUP(A3:A,Backend!A2:U,18,FALSE),TODAY(),"M"),)),)
:
=IFERROR( ARRAYFORMULA(IF(LEN(A3:A),DATEDIF(VLOOKUP(A3:A,Backend!A2:U,18,FALSE),TODAY(),"Y"),)),)
:(你可能想自己四舍五入)
=IFERROR( ARRAYFORMULA(IF(LEN(A3:A),DATEDIF(VLOOKUP(A3:A,Backend!A2:U,18,FALSE),TODAY(),"D")/7,)),)