EXCEL中如何用函数计算指定的日期再过几个月后的对应日期和整年整月整日怎样计算?
台江县公积金管理部 姜瑞
按照民法通则中对日期的要求,按月计算期限的,按到期月的对应日计算,无对应日的,以每月的最后一日为到期日。如2013-11-30,再过3个月应为2014-2-28,以及2013-11-30至2014-3-2有几个月零几天,如何利用函数计算?如下图:
假设
A4为2013-11-30,B4为3,C4=DATE(YEAR(A4),MONTH(A4)+B4,DAY(A1))
D4=DATE(YEAR(A4),MONTH(A4)+B4+1,0)
E4=IF(ABS(DAY(C4)-DAY(A4))>27,D4,IF((DAY(C4)-DAY(A4))=0,C4,IF(((YEAR(C4)-YEAR(A4))*12+MONTH(C4)-MONTH(A4))>3, D4,C4)))
C4
=DATEDIF(A4,B4,"m")D4=DATE(YEAR(A4),MONTH(A4)+C4,DAY(A4))
E4=DATE(YEAR(A4),MONTH(A4)+C4+1,0)
F4=IF(ABS(DAY(D4)-DAY(A4))>27,E4,IF((DAY(D4)-DAY(A4))=0,D4,IF(((YEAR(D4)-YEAR(A4))*12+MONTH(D4)-MONTH(A4))>3, E4,D4)))
G4=IF(AND(MONTH(B4)=2,0<(DAY(A4)-DAY(B4)),(DAY(A4)-DAY(B4))<4),30,DATEDIF(F4,B4,"md"))DATEDIF(F4,B4,"md")
联系客服