Page 1 of 1
calculate number of month
Posted: Fri Jul 26, 2024 4:29 pm
by sna
Hi there,
Calculate the number of completed months per year per project.
for example project A starts in 2022 (for 10 months),2023(12 months),ends 2024(6 months)
I attach a template
thanks
Re: calculate number of month
Posted: Fri Jul 26, 2024 9:17 pm
by norkaz
...
D3
=IF(AND(D$2>YEAR($B3),D$2=YEAR($C3)),MONTH($C3),IF(AND(D$2>YEAR($B3),D$2<=YEAR($C3)),12,IF(D$2=YEAR($B3),13-MONTH($B3),"")))
Then copy to the related cells right and down.
Bigcat9
BKK Thailand
Re: calculate number of month
Posted: Fri Jul 26, 2024 11:10 pm
by sna
How about with Excel 365 dynamic array?
Re: calculate number of month
Posted: Sat Jul 27, 2024 7:26 am
by norkaz
sna wrote: Fri Jul 26, 2024 11:10 pm
How about with Excel 365 dynamic array?
D3
=LET(b,B3:B6,c,C3:C6,d,D2:H2,IF(YEAR(b)=d,13-MONTH(b),IF(YEAR(c)=d,MONTH(c),(d>YEAR(b))*(d<YEAR(c))*12)))
Bigcat9Excel
Bangkok Thailand
...
Re: calculate number of month
Posted: Sat Jul 27, 2024 3:51 pm
by sna
Thanks so much
Re: calculate number of month
Posted: Tue Jul 30, 2024 9:11 am
by linzhengli
ขอความอนุเคราะห์สูตรนี้เป็น excel2033 ได้หรือไม่ครับ
Re: calculate number of month
Posted: Tue Jul 30, 2024 10:21 am
by norkaz
linzhengli wrote: Tue Jul 30, 2024 9:11 am
ขอความอนุเคราะห์สูตรนี้เป็น excel2033 ได้หรือไม่ครับ
ใช้สูตรตาม กระทู้ #2 ที่โพสต์ไปได้เลยครับ
D3
=IF(AND(D$2>YEAR($B3),D$2=YEAR($C3)),MONTH($C3),IF(AND(D$2>YEAR($B3),D$2<=YEAR($C3)),12,IF(D$2=YEAR($B3),13-MONTH($B3),"")))
Copy ไปขวา และลงล่าง
** สนับสนุนฟอรั่ม กรุณา คลิกโฆษณาที่หน้าเพจ**
แมวใหญ่-Bigcat9
RCA พระราม9 กรุงเทพฯ
...