Đếm số ngày cuối tháng giữa 2 thời điểm (1 người xem)

anhtuan1066 · 27/12/07

Các công thức về thời gian ta đã bàn rất nhiều... đếm các kiểu ta cũng đã từng gặp qua, chẳng hạn:
-Đếm số ngày chủ nhật giữa 2 thời điểm
-Đếm số ngày lẽ giữa 2 thời điểm
vân... vân...
Hôm nay tôi chợt vướng ở 1 điểm: Làm sao đếm số ngày cuối tháng giữa 2 thời điểm nhỉ? Như từ 5/1/2007 đến 9/5/2007 có 4 ngày cuối tháng là 31/1/2007, 28/2/2007, 31/3/2007, và 30/4/2007
Vì ngày cuối tháng của các tháng có khác nhau ko giống ngày đầu tháng luôn luôn = 1, ví dụ ngày cuối tháng của tháng 1 là 31, ngày cuối tháng của tháng 2 là 28 hoặc 29 tùy từng năm.. vân vân...
Vậy công thức gì đạt dc yêu cầu này đây?
ANH TUẤN

salam · 27/12/07

Cái này em thấy chỉ cần tính số tháng trừ đi 1 là tháng cuối là OK phải không Bác. Nhưng Bác đưa lên thì chắc là không phải rồi. có uẩn khúc gì đây.

kongcom · 27/12/07

Cái này tôi nghĩ giống toán trồng cây .
Có n cây thì có n-1 khoảng cách nếu hai đầu cùng có cây
Có n cây thì có n khoảng cách nếu 1 đầu có cây 1 đầu không có cây
Có n cây thì có n+1 khoảng cách nếu hai đầu cùng không có cây

Ta coi ngày 1 hàng tháng là cây vì nó đếm đựợc
coi ngày cuối tháng là khoảng cách vì nó nằm giữa .
làm công thức ra sao đây nhỉ ?

ThuNghi · 27/12/07

Các Bác thử hộ ct này, cty tôi lãnh lương 5,20.
=SUMPRODUCT((DAY(ROW(INDIRECT("A"&A1+1&":A"&B1+1)))=1)*1)

Mr Okebab · 27/12/07

Công thức hơi dài, nhưng đúng với mọi ngày:

PHP:

=(year(b1)-year(a1))*12+(month(b1)-month(a1))+(b1=eomonth(b1;0))

Thân!

phamduylong · 27/12/07

minhlev đã viết:
Mã:

=DATEDIF(A1,B1,"M")+IF(MONTH(A1+1)>MONTH(A1),1,0)+IF(MONTH(B1+1)>MONTH(B1),1,0)

minhlev thử lại với: A1=31/01/2007 và B1=31/01/2008, công thức cho kết quả 14. Dư 1 ngày.

minhlev · 27/12/07

Thưa thầy đó là do hàm Datedif gây ra.

Mr Okebab · 27/12/07

ThuNghi đã viết:
Các Bác thử hộ ct này, cty tôi lãnh lương 5,20.
=SUMPRODUCT((DAY(ROW(INDIRECT("A"&A1+1&":A"&B1+1)))=1)*1)

Tôi đã test lại công thức của bác Thunghi thì thấy ngắn nhất và cho ra kết quả đúng.

Rất hay, tuy nhiên sẽ chỉ đúng đến ngày 05/06/2079 (Với excel 2003). Và thời gian truy xuất sẽ chậm nếu 2 ngày cách xa nhau (Dĩ nhiên phải là nhiều công thức).

Thân!

anhtuan1066 · 27/12/07

Nếu gọi là ngắn nhất thì phải SUM chứ... SUMPRODUCT làm sao ngắn hơn SUM dc... he.. he... Cách viết giống nhau... nhưng.. SUMPRODUCT thì đương nhiên thừa chứ PRODUCT nên cũng đương nhiên là dài hơn SUM rồi...
Ngắn nhất đây

Mã:

{=SUM((DAY(A2-1+ROW(INDIRECT("1:"&B2-A2+1))+1)=1)*1)}

Nói chung thuật toán là 1, nhưng nếu gọn là "ngắn" theo nghĩa đen thì phải là cái.. này... Và cái món của tôi cũng đương nhiên là có độ mở rộng nhiều hơn cái của ThuNghi... Giới hạn đến 6/6/2179
ha...ha...ha...
ANH TUẤN

anhtuan1066 · 27/12/07

Sẳn đây chơi luôn vài món tiếp tục nhỉ?
1> Làm sao biết dc 1 năm nào đó là năm nhuận (cho trước chỉ số năm) và đương nhiên là ko phải tính nhẩm nhé
2> Làm sao biết dc 1 năm nào đó có bao nhiêu tuần nếu ko dùng hàm WEEKNUM? Cho trước tuần dc bắt đầu bằng ngày thứ 2
Giãi pháp thì đương nhiên là có... nhưng hãy thử xem thuật toán nào là ngắn nhất?
ANH TUẤN

phamduylong · 27/12/07

Mã:

Rất hay, tuy nhiên sẽ chỉ đúng đến ngày 05/06/2079 (Với excel 2003).

Làm Excel chứ có để ý đến ngày nào không sử dụng hàm được đâu ! Thấy anhtuan nói mới chú ý đến nó.

Công thức dùng hàm DATEDIF này thử cho ngày cuối đến năm 9999 vẫn không báo lỗi.

Mã:

DATEDIF(DATE(YEAR(A1);MONTH(A1);1);B1;"M")+IF(EOMONTH(B1;0)=B1;1;0)

kongcom · 27/12/07

anhtuan1066 đã viết:
Sẳn đây chơi luôn vài món tiếp tục nhỉ?
1> Làm sao biết dc 1 năm nào đó là năm nhuận (cho trước chỉ số năm) và đương nhiên là ko phải tính nhẩm nhé

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"năm nhuận ", "")

Số tuần giữa hai khoảng thời gian A1 và A2

=ODD((A2)-(A1)/7)

Không biết có cách nào ngắn hơn nữa không nhỉ ?

anhtuan1066 · 28/12/07

kongcom đã viết:
=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"năm nhuận ", "")
Số tuần giữa hai khoảng thời gian A1 và A2
=ODD((A2)-(A1)/7)
Không biết có cách nào ngắn hơn nữa không nhỉ ?

Câu 1 đúng nhưng hơi dài.. Nếu bạn dựa vào hàm MOD tại sao ko là
=IF(MOD(A1,4)=0,"năm nhuận ", "") (tức là 4 năm có 1 năm nhuận, đúng ko?)
???
Còn nhiều cách khác nữa đấy!
Câu 2 thì.. sai... Có lẽ bạn muốn viết =ODD((A2-A1)/7) chăng? Nhưng dù có sửa lại cũng vẫn.. sai... Vì chú ý có năm có 54 tuần nhé.. Và còn đặc biệt hơn nữa là cái số 54 này lại phụ thuộc vào việc tôi nói tuần bắt đầu vào thứ mấy!
Lấy ví dụ: nếu tuần bắt đầu bằng chủ nhật thì năm 2000 sẽ có 54 tuần.. Nhưng nếu tuần bắt đầu bằng thứ 2 thì 2012 sẽ có 54 tuần... Rắc rối thế đấy! hi... hi...
ANH TUẤN

kongcom · 28/12/07

AT không loại trừ năm 100, 200,300,500,600,700,900,1100,1300,1400,1500,1700....không phải năm nhuận mặc dù số năm vẫn chia hết cho 4 à ?

anhtuan1066 · 28/12/07

Tôi cũng chẳng biết nữa... nhưng nói chung là nếu phải tính đến những năm xưa lắc như thế thì.. vẫn có cách khác nhanh hơn...
Chẳng hạn như có thể dựa vào nhưng thuật toán sau đây:
-Năm nhuận là năm có 366 ngày... nên đếm ngày trong năm --> Năm nhuận (=DATE(A1,12,31)-DATE(A1,1,1)+1)
-Năm nhuận là năm mà tháng 2 có 29 ngày.. nên đếm ngày trong tháng 2 --> Năm nhuận (=IF(DAY(DATE(A1,3,0))=29..... )
vân vân và vân vân
Các bạn có ý kiến gì về số tuần trong năm ko nhỉ?
ANH TUẤN

phamduylong · 28/12/07

anhtuan1066 đã viết:
Câu 1 đúng nhưng hơi dài.. Nếu bạn dựa vào hàm MOD tại sao ko là
=IF(MOD(A1,4)=0,"năm nhuận ", "") (tức là 4 năm có 1 năm nhuận, đúng ko?)

Các bạn thường nghĩ năm nhuận là năm chia hết cho 4. Đúng nhưng Sai !. Vì sao:
Năm nhuận phải thỏa mãn 2 điều kiện:
- Năm chia hết cho 4.
- Năm tròn trăm phải chia hết cho 400.
Do đó: năm 1900, 1996, 2000, 2400 là năm nhuận. Nhưng 2100, 2200, 2300 không phải là năm nhuận.
Có nhiều tài liệu nói năm 1900 không phải là năm nhuận. Nhưng kiểm tra bằng Excel năm 1900 là năm nhuận. Có lẻ đây là mốc để các nhà làm lịch tính năm nhuận chăng?

nanh · 6/1/08

anhtuan1066 đã viết:
Sẳn đây chơi luôn vài món tiếp tục nhỉ?
1> Làm sao biết dc 1 năm nào đó là năm nhuận (cho trước chỉ số năm) và đương nhiên là ko phải tính nhẩm nhé

Giải pháp của mình thế này nhé: năm bình thường có 365 ngày, năm nhuận có 366 ngày. Điều này có đúng không? Nếu đúng thì ta tính ra số ngày của năm cần biết có là năm nhuận không, nếu năm đó có 366 ngày thì sẽ là năm nhuận. ok?
Ví dụ: A1=2008
công thức: f(x)=if(date(A1+1,1,1)-date(A1,1,1)+1=366,"Năm Nhuận","Không phải năm nhuận").
Kết quả: "Năm Nhuận"
Các bác xem cách này có đủ ngắn chưa nhé.

anhtuan1066 · 6/1/08

nanh đã viết:
Giải pháp của mình thế này nhé: năm bình thường có 365 ngày, năm nhuận có 366 ngày. Điều này có đúng không? Nếu đúng thì ta tính ra số ngày của năm cần biết có là năm nhuận không, nếu năm đó có 366 ngày thì sẽ là năm nhuận. ok?
Ví dụ: A1=2008
công thức: f(x)=if(date(A1+1,1,1)-date(A1,1,1)=366,"Năm Nhuận","Không phải năm nhuận").
Kết quả: "Năm Nhuận"
Các bác xem cách này có đủ ngắn chưa nhé.

Cũng đúng... hi.. hi... nhưng sao ko là:

Mã:

=IF(day(DATE(A1,3,0))-28,"Năm nhuận","")

Vầy gọn hơn ko?
Còn số tuần trong năm thì sao nhỉ?
ANH TUẤN

kongcom · 6/1/08

Để tính số tuần khi biêt ngày tháng năm , bạn phải cho phép Analysis ToolPak trong Excel .
Nếu A1 có nội dung là ngày tháng năm , để tính số tuần bạn theo công thức

=WEEKNUM(A1,1)

1 ở đây là tuần bắt đầu tính từ Chủ nhật
Nếu bạn muốn tính tuần bắt đầu Thứ hai thì là số 2 - Ví dụ : =WEEKNUM(A1,2)

Không biết tính số tuần trong năm thì thế nào cho chuẩn nhỉ ???

À ra rồi .
A1 là ngày 31/tháng12 năm nào đó
Ô kết quả ghi =WEEKNUM(A1,WEEKDAY(DATE(YEAR(A1),1,1)))

anhtuan1066 · 6/1/08

Như từ đầu tôi đã nói là ko dùng WEEKNUM rồi... thế mới là bài toán chứ... hi... hi...
Còn A1 là tôi ghi chỉ số năm... giã sử A1 = 2008 chẵng hạn... và quy định tuần bắt đầu bằng thứ 2
Giờ thì tính xem năm 2008 (ghi ở A1) có tổng số tuần là bao nhiêu?
Vậy đấy!
------------------------
Về câu hỏi đếm số tuần này, lúc đầu tôi nghĩ đơn giãn chỉ là:

Mã:

=IF(MOD(A1,28)=24,54,53)

Nhưng sau đó thấy ko ỗn lắm... tính tới năm 2100 trở đi sẽ sai khi so sánh với WEEKNUM... ha... ha...
Căng nhỉ?
ANH TUẤN

ptm0412 · 6/1/08

anhtuan1066 đã viết:
=IF(day(DATE(A1,3,0))-28,"Năm nhuận","")

không hiểu anhtuan ơi!

tigertiger · 6/1/08

PHP:

=IF(DAY(DATE(A1,3,0))-28,"Năm nhuận","")

Ở đây a tuan dùng thủ thuật một chút đó là
+ dùng hàm DATE(A1,3,0) nhưng ở đây atuan sử dụng ngày 0 tháng 3 năm lấy giá trị A1-> trả về giá trị ngày trước đó (ngày đầu tiên tháng 3 vì ko có ngày 0) -> là ngày 28/2/... (đối với năm ko nhuận) và 29/2/... (đối với năm nhuận)
+ Do đó -> DAY(DATE(A1,3,0))-28 là =0 (đối với năm ko nhuận) và =1 (đối với năm nhuận)
Đó chính là mấu chốt vấn đề, rất hay,
mặc dù atuan nói hạn chế là 2100 -> nhưng dùng vô tư đến khi ....... hết đời ... hic
Tuy vậy có thuật toán chuẩn tìm năm nhuận mà mọi ng đã đề nghị trên rùi,

ptm0412 · 6/1/08

Hỏi thêm:
Như vậy DAY(DATE(A1,3,0))-28 đã phải là Logical Expression chưa?

tigertiger · 7/1/08

ptm0412 đã viết:
Hỏi thêm:
Như vậy DAY(DATE(A1,3,0))-28 đã phải là Logical Expression chưa?

ở đây DAY(DATE(A1,3,0))-28 trả về 0 hoặc 1; Mà excel lun xem 0 là tường ứng vơi False, còn 1 (hay các số khác 0) là True -> nó là Logical Expression rùi

* Vì thế nhiều trường hợp ta viết tắt không viết là =1 hay =0 (hay <>0) nữa

nanh · 7/1/08

anhtuan1066 đã viết:
Còn số tuần trong năm thì sao nhỉ?

Tôi có một thắc mắc chưa rõ lắm về cách tính tuần, thế này nhé: nếu tuần bắt đầu từ thứ hai thì có phải nếu trong một tháng có 4 ngày thứ hai thì tháng đó có 4 tuần phải không, và nếu tháng đó có 5 thứ hai (mặc dù thứ 2 là ngày cuối tháng) thì tháng đó có được tính là 5 tuần không?? :cc_confused:

Nếu quả thực như vậy thì có nghĩa là số tuần trong một năm bằng số ngày thứ 2 trong một năm đó?
Tôi có thắc mắc như vậy, mong các thầy, các anh và các bạn chỉ giáo.
Thanks. %#^#$

nanh · 7/1/08

anhtuan1066 đã viết:
Còn số tuần trong năm thì sao nhỉ?

Công thức để tính số tuần trong năm:
A1= số năm
f(x)=IF(WEEKDAY(DATE(A1,1,1))*(DATE(A1,12,31)-DATE(A1,1,1)+1)=366,54,53)
tôi đã kiểm tra lại bằng cách so sánh kết quả với hàm WEEKNUM thì đúng đến năm 6799, hơn nữa thì tôi chưa thử tiếp.
Các anh, các bạn xem có đúng không nhé. Nếu đúng xin vote cho tôi nhé. THanks

anhtuan1066 · 7/1/08

Kiễm tra sơ bộ thấy chính xác lắm, cãm ơn bạn!
Còn tôi thì xài cái này:

Mã:

=INT((DATE(A1,12,31)-DATE(A1,1,1)-WEEKDAY(DATE(A1,12,31),2)+WEEKDAY(DATE(A1,1,1),2)+8)/7)

Mến
ANH TUẤN

nanh · 7/1/08

anhtuan1066 đã viết:
Kiễm tra sơ bộ thấy chính xác lắm, cãm ơn bạn!
Còn tôi thì xài cái này:

Mã:

=INT((DATE(A1,12,31)-DATE(A1,1,1)-WEEKDAY(DATE(A1,12,31),2)+WEEKDAY(DATE(A1,1,1),2)+8)/7)

Mến
ANH TUẤN

Công thức này rất hay, hôm qua mình cũng đi theo hướng này nhưng mãi không ra, giờ đọc bài của bạn mình đã hiểu ra vấn đề.
Mình xin được rút gọn công thức một chút xíu nhé:

Mã:

=(DATE(A1,12,31)-WEEKDAY(DATE(A1,12,31),2)-DATE(A1,1,1)+WEEKDAY(DATE(A1,1,1),2))/7+1

Thanks.
Mr.Nanh

ThuNghi · 7/1/08

Tiện đây xin hỏi các bạn 1 vấn đề về ngày tháng như sau:
- Giả sử hôm nay là 01/01/2008 sau 6 tháng (kỳ hạn) là 01/07/2008
- Tương tự:
02/01/2008 sau 6 tháng (kỳ hạn) là 02/07/2008
31/01/2008 sau 6 tháng (kỳ hạn) là 31/07/2008
....
28/02/2008 sau 6 tháng (kỳ hạn) là 28/08/2008
29/02/2008 sau 6 tháng (kỳ hạn) là 29/08/2008
01/03/2008 sau 6 tháng (kỳ hạn) là 01/09/2008
...
30/03/2008....30/09/2008
31/03/2008....30/09/2008
---
31/10/2008....30/04/2008 (không thể 31/04...)
Nghĩa là kỳ hạn thanh toán là ngày như ngày đầu sau 6 tháng nhưng nếu ngày 31/03/2008 - thì không thể 31/09/2008 mà phải 30/09/2008
Nhờ các bạn giúp tôi công thức trên, nếu có thể dùng VBA.
Xin cám ơn!

tigertiger · 7/1/08

ThuNghi đã viết:
Tiện đây xin hỏi các bạn 1 vấn đề về ngày tháng như sau:
...
30/03/2008....30/09/2008
31/03/2008....30/09/2008
---
31/10/2008....30/04/2008 (không thể 31/04...)
Nghĩa là kỳ hạn thanh toán là ngày như ngày đầu sau 6 tháng nhưng nếu ngày 31/03/2008 - thì không thể 31/09/2008 mà phải 30/09/2008
Nhờ các bạn giúp tôi công thức trên, nếu có thể dùng VBA.
Xin cám ơn!

chắc là thế này chứ
31/10/2008....30/04/2009 (không thể 31/04...)

thế này chắc phải dùng tính được tổng cộng ngày của 6 tháng tiếp theo, VBA chắc ra ngay, hic

ptm0412 · 7/1/08

Không cần VBA đầu, dùng hàm edate() là được.
Cấu trúc: EDATE(date,number of month)
--> B1=EDATE(A1,6)
Vì là hàm ngày tháng của Excel nên loại bỏ được vụ ngày 30, 31, 28, và 29 /2 của cả năm nhuận. Nếu number of month < 0 là tính lùi 1 số tháng.

tigertiger · 7/1/08

Dùng hàm có sẵn trong Excel

ptm0412 đã viết:
Không cần VBA đầu, dùng hàm edate() là được.
Cấu trúc: EDATE(date,number of month)
--> B1=EDATE(A1,6)
Vì là hàm ngày tháng của Excel nên loại bỏ được vụ ngày 30, 31, 28, và 29 /2 của cả năm nhuận. Cái này mình giới thiệu rồi mà!

* Hàm EDATE thì phải cộng thêm addIN

* Nếu ko, thì sử dùng công thức sau cũng ngon, với A1 chứa ngày cần cộng thì ô kết quả dùng công thức như thế này:

PHP:

=DATE(YEAR(A1)+IF(MONTH(A1)+6>12,1,0),MONTH(A1)+IF(MONTH(A1)+6>12,-6,6),MIN(DAY(A1),DAY(DATE(YEAR(A1)+IF(MONTH(A1)+6>12,1,0),1+MONTH(A1)+IF(MONTH(A1)+6>12,-6,6),0))))

.

phamduylong · 7/1/08

ThuNghi đã viết:
Tiện đây xin hỏi các bạn 1 vấn đề về ngày tháng như sau:
- Giả sử hôm nay là 01/01/2008 sau 6 tháng (kỳ hạn) là 01/07/2008
- Tương tự:
02/01/2008 sau 6 tháng (kỳ hạn) là 02/07/2008
31/01/2008 sau 6 tháng (kỳ hạn) là 31/07/2008
....
28/02/2008 sau 6 tháng (kỳ hạn) là 28/08/2008
29/02/2008 sau 6 tháng (kỳ hạn) là 29/08/2008
01/03/2008 sau 6 tháng (kỳ hạn) là 01/09/2008
...
30/03/2008....30/09/2008
31/03/2008....30/09/2008
---
31/10/2008....30/04/2008 (không thể 31/04...)
Nghĩa là kỳ hạn thanh toán là ngày như ngày đầu sau 6 tháng nhưng nếu ngày 31/03/2008 - thì không thể 31/09/2008 mà phải 30/09/2008
Nhờ các bạn giúp tôi công thức trên, nếu có thể dùng VBA.
Xin cám ơn!

ThuNghi dùng công thức này xem (với A1 là ngày đầu)
=IF(DAY(DATE(YEAR(A1);MONTH(A1)+6;DAY(A1)))<>DAY(A1);DATE(YEAR(A1);MONTH(A1)+6;DAY(A1)-1);DATE(YEAR(A1);MONTH(A1)+6;DAY(A1)))

ThuNghi · 8/1/08

Cám ơn các Bác nhiều

phamduylong đã viết:
ThuNghi dùng công thức này xem (với A1 là ngày đầu)
=IF(DAY(DATE(YEAR(A1);MONTH(A1)+6;DAY(A1)))<>DAY(A1);DATE(YEAR(A1);MONTH(A1)+6;DAY(A1)-1);DATE(YEAR(A1);MONTH(A1)+6;DAY(A1)))

Nhưng trường hợp này chưa OK. Nếu:
A1 là 31/08/2007 thì B1 phải là 29/02/2008
A1 là 28/08/2008 thì B1 phải là 28/02/2009 --- cái này OK
A1 là 29/08/2008 thì B1 phải là 28/02/2009
A1 là 30/08/2008 thì B1 phải là 28/02/2009
A1 là 31/08/2008 thì B1 phải là 28/02/2009

anhtuan1066 · 8/1/08

Thử cái này xem có chính xác ko? (tôi mới thử sơ sơ)

Mã:

=DATE(YEAR(A1),MONTH(A1)+A2,MIN(DAY(DATE(YEAR(A1),MONTH(A1)+A2+1,0)),DAY(A1)))

Với A1 là ngày tháng năm, A2 là kỳ hạn
ANH TUẤN

ThuNghi · 8/1/08

anhtuan1066 đã viết:
Thử cái này xem có chính xác ko? (tôi mới thử sơ sơ)

Mã:

=DATE(YEAR(A1),MONTH(A1)+A2,MIN(DAY(DATE(YEAR(A1),MONTH(A1)+A2+1,0)),DAY(A1)))

Với A1 là ngày tháng năm, A2 là kỳ hạn
ANH TUẤN

Tôi mới chỉ test 2008 thấy OK, trước mắt là vậy. Test sau. Cám ơn AT1066 và các bạn nhiều.

Đếm số ngày cuối tháng giữa 2 thời điểm (1 người xem)

Người dùng đang xem chủ đề này

Thành viên gạo cội

Thành viên gắn bó

Thành viên hoạt động

Hãy cho rồi sẽ nhận!

Ngon Ngất Ngây

-

A - Bờ - Cờ

Ngon Ngất Ngây

Thành viên gạo cội

Thành viên gạo cội

-

Thành viên hoạt động

Thành viên gạo cội

Thành viên hoạt động

Thành viên gạo cội

-

Thành viên mới

Thành viên gạo cội

Thành viên hoạt động

Thành viên gạo cội

Bad Excel Member

Coming back ...

Bad Excel Member

Coming back ...

Thành viên mới

Thành viên mới

Thành viên gạo cội

Thành viên mới

Hãy cho rồi sẽ nhận!

Coming back ...

Bad Excel Member

Coming back ...

-

Hãy cho rồi sẽ nhận!

Thành viên gạo cội

Hãy cho rồi sẽ nhận!

Thời gian đếm ngược.