活字格常用公式及函数

税号验证

验证字符串是否只由字母和数字组成

^[a-zA-Z0-9]+$

邮箱验证正则表达式

可验证是否为一个或多个邮箱

^([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})(;[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})*[;]?$

生成 13 位时间戳

=TEXT((NOW()-DATE(1970,1,1))*86400,"0")

生成 GUID

=CONCATENATE(LOWER(DEC2HEX(RANDBETWEEN(0,4294967295),8)),"-",LOWER(DEC2HEX(RANDBETWEEN(0,65535),4)),"-",LOWER(DEC2HEX(RANDBETWEEN(0,65535),4)),"-",LOWER(DEC2HEX(RANDBETWEEN(0,65535),4)),"-",LOWER(DEC2HEX(RANDBETWEEN(0,4294967295),8)),LOWER(DEC2HEX(RANDBETWEEN(0,65535),4)))&"_"

日期计算公式

//本月开始日期
=EOMONTH(TODAY(),-1)+1

//本月结束日期
=EOMONTH(TODAY(),0)

//上月开始日期
=EOMONTH(TODAY(),-2)+1

//上月结束日期
=EOMONTH(TODAY(),-1)

//近三天日期开始日期
=TODAY()-2

//近三天日期结束日期
=TODAY()

//上周开始日期
=TODAY()-WEEKDAY(TODAY(),3)-7

//上周结束日期
=TODAY()-WEEKDAY(TODAY(),3)-1

//本周开始日期
=TODAY()-WEEKDAY(TODAY(),3)

//本周结束日期
=TODAY()-WEEKDAY(TODAY(),3)+6

//下月开始日期
=EOMONTH(TODAY(),0)+1

//下月结束日期
=EOMONTH(TODAY(),1)

//本年开始日期
=DATE(YEAR(TODAY()),1,1)

//本年结束日期
=DATE(YEAR(TODAY()),12,31)

//去年开始日期
=DATE(YEAR(TODAY())-1,1,1)

//去年结束日期
=DATE(YEAR(TODAY())-1,12,31)

//1年后的当天
=DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY()))

//季度开始日期
=DATE(YEAR(TODAY()),ROUNDDOWN((MONTH(TODAY())-1)/3,0)*3+1,1)

//季度结束日期
=EOMONTH(DATE(YEAR(TODAY()),ROUNDDOWN((MONTH(TODAY())-1)/3,0)*3+1,1),2)

//上半年开始日期
=DATE(YEAR(TODAY()),1,1)
//上半年结束日期
=DATE(YEAR(TODAY()),6,30)

//下半年开始日期
=DATE(YEAR(TODAY()),7,1)
//下半年结束日期
=DATE(YEAR(TODAY()),12,31)

版本号递增公式

用于将"1.0.0"版本号递增为 "1.0.1"

=IFERROR(LEFT(版本号,LOOKUP(2,1/(MID(版本号,ROW(INDIRECT("1:"&LEN(版本号))),1)="."),ROW(INDIRECT("1:"&LEN(版本号))))-1)&"."&(RIGHT(版本号,LEN(版本号)-LOOKUP(2,1/(MID(版本号,ROW(INDIRECT("1:"&LEN(版本号))),1)="."),ROW(INDIRECT("1:"&LEN(版本号)))))+1),版本号&".1")