如何计算天数？

lzr122

已知道开始日期和结算日期，计算1月份的天数？

求大神帮忙

高个子

1. =MIN(--SUBSTITUTE(B2,".","/"),--"2023/1/31")-MAX(--SUBSTITUTE(A2,".","/"),--"2023/1/1")+1

复制代码

tchh666

1. =COUNT(0/(MONTH(VDB(,,B2,A2,A2+ROW($1:$365)-1)+A2+ROW($1:$365)-1)=1))

复制代码

tchh666

小数点，替换一下。。

jivy1212

1. =COUNT(VDB(,1,44957,44927,ROW(INDIRECT(SUBSTITUTE(A2,".","-")*1&":"&SUBSTITUTE(B2,".","-")*1))))

复制代码

山寨日期真是麻烦

rockzhou

=DATEDIF(MAX(--SUBSTITUTE(A2,".","/"),"2023/1/1"),MIN(--SUBSTITUTE(B2,".","/"),"2023/1/31"),"d")+1

博爱风雨同舟

=SUM(IF(TEXT(ROW(INDIRECT(--SUBSTITUTE(A2,".","-")&":"&--SUBSTITUTE(B2,".","-"))),"YYYY年M月")=MID($C$1,1,7),1,""))

lzr122

jivy1212 发表于 2023-1-13 16:57
山寨日期真是麻烦

要是2月份的天数呢？

一杯绿茶来了

=IF(SUBSTITUTE(B2,".","/")*1>EOMONTH("2023-1",0),EOMONTH("2023-1",0)-MAX("2023/1/1",SUBSTITUTE(A2,".","/")*1)+1,SUBSTITUTE(B2,".","/")*1-MAX("2023/1/1",SUBSTITUTE(A2,".","/")*1)+1)

w4275

C1控制月份

1. =MIN(--SUBSTITUTE(B2,".","/"),EOMONTH(LEFT(C$1,FIND("月",C$1)),0))-MAX(--SUBSTITUTE(A2,".","/"),EOMONTH(LEFT(C$1,FIND("月",C$1)),-1)+1)+1

复制代码