Cách đếm số tháng theo điều kiện (1 người xem)

[Gia_Khue]

Cám ơn bác ThuNghi nhiều lắm

Cho GK hỏi thêm tí nhé.

Tại sao phải dùng hàm Today() thì mới không bị báo lỗi hả bác. Hàm Today và Now khác nhau ở chỗ nào?

Cám ơn sự giúp đỡ của bác.

Anh BNTT ơi, công thức của anh cũng ra kết quả đúng nữa, dài loằng ngoằng, nhưng GK đọc vô hiểu công thức. Công thức của bác ThuNghi cũng ra kết quả đúng, rất ngắn gọn, nhưng do Gk hỏng rành cái hàm Indirect tí teo nào nên chỉ copy và xài, chứ hỏng hiểu gì ráo trọi hết á.

Cám ơn anh BNTT. (hỏi nhỏ, GK gọi là "anh" đúng không vậy? hay phải gọi là "chị"?)

GK.

 

[BNTT]

Hàm TODAY() chỉ hiện ra mỗi ngày tháng năm thôi, Còn hàm NOW() thì có kèm theo cả giờ phút giây nữa.

Bạn cứ hiểu theo tiếng Anh đi, NOW = hiện nay, ngay lúc này (tính được cả giờ, phút...),
trong khi TODAY = hôm nay (7 giờ sáng cũng là hôm nay mà 9 giờ tối cũng là hôm nay)

Để kiểm chứng, bạn mở một Sheet mới, đừng định dạng gì cả,
một ô bạn gõ = NOW(), một ô bạn gõ = TODAY()
bạn sẽ thấy nó khác ở chỗ nào liền.


P/S: Bạn gọi anh là... đúng gòy.

 

[ptm0412]

Quả nhiên sự đời không đơn giản. Mình đã làm lại hết mọi trường hợp (khoảng 1000 trường hợp), công thức BNTTnew cũng còn sai (445 trường hợp). Mình phải làm lại, công thức dài như xe lửa xuyên Việt:
= IF(B3=EOMONTH(B3;0);
DATEDIF(IF(DAY(A3)=1;A3;EOMONTH(A3;0));B3+1;"M");
DATEDIF(IF(DAY(A3)=1;A3;EOMONTH(A3;0)+1);B3;"M"))

Xem file kèm theo

 

[anhtuan1066]

Mấy món tính ngày tháng năm theo 1 yêu cầu nào đó phải tinh ý mới dc.. ko dễ ăn đâu... Ngay từ đầu tôi đã gợi ý là nên bám vào vụ đếm số ngày đầu tháng (ngày 1 tây)
Nói chung sẽ có hàng đóng công thức tùy thích.. xin gới thiệu 2 em:
-Em đầu tiên (công thức mãng)

=SUM(IF(DAY((A2-1+ROW(INDIRECT("1:"&(B2-A2)+1))))=1,1,0))-1+(DATE(YEAR(B2),MONTH(B2)+1,0)=B2)
Nhớ Ctrl + Shift + Enter đễ kết thúc

-Em thứ 2 dùng DATEDIF

=DATEDIF(DATE(YEAR(A2-1),MONTH(A2-1)+1,1),DATE(YEAR(B2+1),MONTH(B2+1),1),"M")

File đính kèm cho các bạn theo dỏi!
Công thức của đồng chí ThuNghi về nguyên tắc là gần chính xác, nhưng vẫn còn thiếu đoạn xét ngày cuối tháng... Nếu ngày cuối đúng bằng cuối tháng thì cộng thêm 1 vào công thức trên
ANH TUẤN

 

[ThuNghi]

Bạn thử dùng ct sau: (Copy anhtuan1066)
=SUMPRODUCT((DAY(ROW(INDIRECT("1:"&(C4-B4)+1)))=1)*1)-1
Lưu ý: nên dùng today() hơn là dùng now()

Công thức trên bị sai khi A4 là 02/10/07 và B4 là 26/12/07, đúng ra phải cho KQ 1
Vậy nên sửa thành ct sau cho đúng
=SUMPRODUCT(((DAY(ROW(INDIRECT("A"&$A4&":A"&$B4+1)))=1)*1))-1
Sorry nhé, cám ơn BNTT nhiều!

 

[ptm0412]

Nếu đừng cố bám vào Datedif()!

Ngay từ đầu do yêu cầu của GK chưa chính xác, mình dùng Datedif(), sau đó khi yêu cầu thay đổi và đã quá khuya, mình chỉ cải tiến nó, rồi cứ thế bám theo nó mãi đến khi nó thành xe lửa Xuyên Việt thì mất trắng 2 đêm.

Nhưng nay nghĩ lại, ngay từ đầu mà biết cái yêu cầu như thế mình dùng cái này, đơn giản hơn, dễ hiểu hơn, dù có dài nhưng không phải xe lửa mà chỉ là xe đò:
- lấy month trừ month
- Nếu tháng của ngày trước đủ: +1
- Nếu tháng của ngày sau đủ: +1
- Nếu tháng của cả 2 ngày đều thiếu: -1
- Dự phòng trường hợp month() 2 ngày bằng nhau --> bị âm

Dễ làm như đọc đề bài làm xuống, nếu đề bài ra đúng và chính xác câu cú ngay từ đầu:

 

[anhtuan1066]

Uh.. phát biểu thì dễ, nhưng diển giãi cho Excel hiểu thì.. nội cái:
-Nếu tháng của ngày trước đủ
-Nếu tháng của ngày sau đủ
-Nếu tháng của cả 2 ngày đều thiếu
Nội 3 cái IF đó thôi cũng đủ làm cho công thức dài mệt nghĩ... Dựa vào tiêu chí nào đễ biết tháng là đũ? Thứ nhất ngày phải là ngày 1 tây.. nhưng điều này vẫn chưa đũ, ít nhất ngày kết thúc phải >= ngày cuối tháng nữa? Hic...
IF 3 nhát đói bụng luôn
ANH TUẤN

 

[ptm0412]

Chưa xong đâu các bạn.

Mình test thử công thức của vài bạn, và cả của mình, test cái nào sai cái nấy, trường hợp sai là nếu ngày trước và ngày sau cùng 1 tháng.

Mình định test hết nhưng lâu quá, vì mình đang xài dấu ";" gõ lại thì lâu mà copy xuống cũng phải sửa.

Xin cảm phiền, của ai nấy test cả trong 4 trường hợp đủ đủ, đủ thiếu, thiếu đủ, thiêu thiếu trong trường hợp chung là 2 ngày cùng tháng.
Vì đúng trong case này thì sai case kia.
Của mình cũng thế.

 

[anhtuan1066]

He... he... Vậy thì chúng ta cùng cố gắng hoàn thiện hơn vậy!
Tôi đề xuất 1 cái

=MAX(SUM(IF(DAY((A2-1+ROW(INDIRECT("1:"&(B2-A2)+1))))=1,1,0))-1+(DATE(YEAR(B2),MONTH(B2)+1,0)=B2),0)

Tôi nghĩ cái của ThuNghi cũng phải thêm MAX giống vậy

MAX(SUMPRODUCT...... ,0)

vì thực chất 2 công thức trên là cùng 1 thuật toán
ANH TUẤN

 

[ptm0412]

Mình test thử luôn rồi, Atuan cũ 2 cái, mới nhất 1 cái là 3, ThuNghi 1 cái mới nhất, BNTT 1 cái mới nhất, của mình cái xe lửa xuyên Việt.
Chỗ nào sai bị font chữ đỏ.
Chỗ tô màu là cùng trường hợp "thiếu thiếu" mà lại đúng. Thua Bác Bill chỗ này.
Xem file kèm theo

 

[anhtuan1066]

Đại ca xem lại đi... Công thức sau cùng của tôi hoàn toàn đúng.. chỉ là đại ca đã nhập sai... hi... hi... Tôi ghi A2 và B2 thì trong file cụ thể này, đại ca phải đỗi nó thành B2 và C2 chứ
Đây nè.. đại ca.. Tôi sửa của ThuNghi luôn rồi đấy...
Còn công thức của các đại ca vì IF nhiều quá tôi chẳng biết phải sửa như thế nào nữa..
ANH TUẤN

 

[BNTT]

Tôi đã test lại các công thức được nêu lên, và xin đưa ra kết luận sau:

Công thức của bác ThuNghi

MAX(SUMPRODUCT(((DAY(ROW(INDIRECT("A"&$A2&":A"&$B2+1) ))=1)*1))-1,0)
​

và công thức của bác ANHTUAN1066
​

MAX(SUM(IF(DAY((A1-1+ROW(INDIRECT("1:"&(B1-A1)+1))))=1,1,0))-1+(DATE(YEAR(B1),MONTH(B1)+1,0)=B1),0)
​

là chính xác 100%
​

Với A1 là ngày của tháng trước, B1 là ngày của tháng sau

Trong đó,

Công thức của bác ThuNghi là công thức bình thường
Công thức của bác ANHTUAN là công thức mảng
​

Nếu có ai chứng minh được hai công thức trên không đúng, tôi sẽ hậu tạ 1 thùng bia.​

 

[ptm0412]

Còn công thức của các đại ca vì IF nhiều quá tôi chẳng biết phải sửa như thế nào nữa..
ANH TUẤN

Thanks bạn, mình cũng chẳng muốn sửa cái xe lửa dài ngoằng đó nữa, vì thêm 1 if là thêm 1 toa, lại càng dài.
Của bạn là đúng rôì, lại ngắn nữa.

Nhân đây mình cũng rút ra 1 số bài học, chung cho mọi người:

1. Phải test kỹ mọi trường hợp
2. Nếu càng sửa càng thấy xuất hiện lỗi thì :
a. Trở lại sửa từ đầu F1, F2, đừng cố sửa cái phiên bản F5, F7
b. Đổi hướng tư duy, tìm thuật toán khác, hàm khác
3. Xong phải trở lại điều 1.

Mình thấy bài học này có thể xài cho cả trong việc viết code VBA.

Cám ơn mọi người đã chia sẻ.

To BNTT:
Bạn mới có vốn 1/2 thùng mà dám treo giải 1 thùng ư? Hì hì!

 

[ThuNghi]

Tôi đã test lại các công thức được nêu lên, và xin đưa ra kết luận sau:

Công thức mảng của bác ANHTUAN1066 là chính xác 100%
​

MAX(SUM(IF(DAY((A1-1+ROW(INDIRECT("1:"&(B1-A1)+1))))=1,1,0))-1+(DATE(YEAR(B1),MONTH(B1)+1,0)=B1),0)
​

Với A1 là ngày của tháng trước, B1 là ngày của tháng sau

Nếu có ai chứng minh được công thức trên không đúng, tôi sẽ hậu tạ 1 thùng bia.​

Vậy ct này có sai không, test thử luôn, nếu OK thì ngắn hơn
=MAX(SUMPRODUCT(((DAY(ROW(INDIRECT("A"&$B2&":A"&$C2+1) ))=1)*1))-1,0)
Xin 5 lon thôi!

 

[anhtuan1066]

Uh... thì làm phần mềm cũng vậy mà... lúc đầu cũng ko thể nào lường trước dc mọi việc... phải nhờ bà con test thử, phản hồi ý kiến thì mới rút ra kết luận phải sửa hay thêm bớt cái gì cho hoàn thiện...
Mà tôi nhận xét công thức của ThuNghi nếu thêm MAX vào cũng chính xác cơ mà..

He... he... và thêm cái DATEDIF của tôi mới sửa nữa.. cũng chính xác luôn... Là 3 em chính xác nhé!
ANH TUẤN

 

[BNTT]

Xin lỗi bác ThuNghi nhé, trong khi bác post bài trên thì em đang sửa lại bài của mình, là thêm cái của bác nữa đó.

Và cũng xin nói thêm chút, công thức của ai ngắn hơn thì có điểm cao hơn.
Bác AnhTuan1066 có đồng ý không ?

Bác Ptm0412 à, em test gần 2 tiếng đồng hồ, quên ăn trưa luôn, nên mới dám mạnh dạn tuyên bố vậy chứ...

 

[Gia_Khue]

GK lại chào cả nhà.

Hôm nay GK đang test số liệu phát hiện ra trường hợp này bị sai khi áp dụng công thức của bác Thu Nghi. Các bác xem giúp GK lỗi do đâu nhé.

Các bác xem file đính kèm ạ, GK đã ghi rất chi tiết trong file rồi ạ.

Cám ơn các bác.

 

[SA_DQ]

Cho ké một cái nha, bằng VBA

Option Explicit
 Function TronThang(Dat1 As Date, Dat2 As Date) As Integer
 If Dat2 < Dat1 Then
    Dim tDate As Date:              tDate = Dat2
    Dat2 = Dat1:                    Dat1 = tDate
 End If
 If Day(Dat1) > 1 Then     Dat1 = DauThang(Dat1)
 If Day(Dat2) < Day(DauThang(Dat2) - 1) Then _
    Dat2 = DauThang(Dat2, False) - 1
 TronThang = DateDiff("M", Dat1, Dat2) + 1
 End Function

(hú í: Ngày theo dạng Mẽo

 Function DauThang(Dat0 As Date, Optional Sau As Boolean = True) As Date
 
  If Month(Dat0) <> 12 Then
    DauThang = DateSerial(Year(Dat0), Month(Dat0) - Sau, 1)
  Else
    DauThang = DateSerial(Year(Dat0) + 1, 1, 1)
  End If
 
 End Function

 

[anhtuan1066]

GK lại chào cả nhà.
Hôm nay GK đang test số liệu phát hiện ra trường hợp này bị sai khi áp dụng công thức của bác Thu Nghi. Các bác xem giúp GK lỗi do đâu nhé.
Các bác xem file đính kèm ạ, GK đã ghi rất chi tiết trong file rồi ạ.
Cám ơn các bác.

Như vầy nè chị hai ơi!
ANH TUẤN

 

[ThuNghi]

GK lại chào cả nhà.

Hôm nay GK đang test số liệu phát hiện ra trường hợp này bị sai khi áp dụng công thức của bác Thu Nghi. Các bác xem giúp GK lỗi do đâu nhé.

Các bác xem file đính kèm ạ, GK đã ghi rất chi tiết trong file rồi ạ.

Cám ơn các bác.

Tôi đã sửa ct rồi mà bạn o update
D5=MAX(SUMPRODUCT(((DAY(ROW(INDIRECT("A"&$B5&":A"&$C5 +1) ))=1)*1))-1,0)