本帖最后由 蓝粆 于 2014-4-18 16:27 编辑
交作业~有肉吃~!
1、- =TRUNC((DAYS360(DATE(MID(D7,7,2+(LEN(D7)=18)*2),MID(D7,9+(LEN(D7)=18)*2,2),MID(D7,11+(LEN(D7)=18)*2,2)),DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),FALSE))/360,0)
复制代码- =TRUNC((DAYS360(TEXT(MID(D8,7,6+(LEN(D8)=18)*2),"#-00-00"),TODAY(),0))/360)
复制代码- =DATEDIF(TEXT("19"&MID(D7,7+(LEN(D7)=18)*2,6),"#-00-00"),NOW(),"Y")
复制代码- =DATEDIF(TEXT(MID(D7,7,6+(LEN(D7)=18)*2),"00-00-00"),TODAY(),"Y")
复制代码- =DATEDIF(TEXT(MID(D7,7,11)-500,"00-00-00,"),TODAY(),"y")
复制代码- =TRUNC((1140418-MID(D7,7+(LEN(D7)=18)*2,6))%%)
复制代码- =INT((1140418-MID(D7,7,6+(LEN(D7)=18)*2))%%)
复制代码- =-LEFT(MID(D7,7+(LEN(D7)=18)*2,6)-1140418,3)
复制代码 阐述:利用MID+LEN判断该从身份证中哪里取出日期,然后用DAYS360算出出生日期到今天多少天,再除以360,得出年份,再利用TRUNC取整。
2、- =DATE(YEAR(D19),MONTH(D19)+E19,DAY(D19))
复制代码- =EOMONTH(D19,E19-1)+DAY(D19)
复制代码 阐述:利用EDATE函数特性直接求出几个月之后的日期,得出保质期过期日
3、- =DATE(YEAR(C28),MONTH(C28)+1,0)
复制代码 阐述:利用EOMONTH函数第二参数为0特性求出该月最后一天
4、- ="第"&LOOKUP(MONTH(C45),{1,4,7,10;"一","二","三","四"})&"季度"
复制代码- ="第"&TRIM(MID(" 一 二 三 四",MONTH(C45),3))&"季度"
复制代码- =TEXT(ROUNDUP(MONTH(C45)/3,0),"第0[DBNUM1]季度")
复制代码- =TEXT(CEILING(MONTH(C45)/3,1),"第0[DBNUM1]季度")
复制代码- =TEXT(INT((MONTH(C45)+2)/3),"第[DBNUM1]0季度")
复制代码- ="第"&NUMBERSTRING(LEN(2^MONTH(B2)),3)&"季度"
复制代码- =TEXT(MONTH(MONTH(C45)&0),"第[dbnum1]0季度")
复制代码- =TEXT(LEN(2^MONTH(C45)),"第0[DBNUM1]季度")
复制代码- =TEXT(MONTH(C45)/3+0.7,"第[dbnum1]D季度")
复制代码- =TEXT(MONTH(C45)*10,"第[DBNUM1]M季度")
复制代码- =TEXT(MONTH(C45)&0,"第[DBNUM1]M季度")
复制代码- =TEXT((MONTH(C45)+5)/3,"第aaa季度")
复制代码 阐述:利用CEILING函数向上取整得出月份所在季度,然后利用TEXT函数转换大写
5、- =NETWORKDAYS(C57,D57,{41760;41792;41890;41913;41914;41915})
复制代码 |