Hàm DATEDIF tự tạo (1 người xem)

[Hoàng Trọng Nghĩa]

Tối qua mò mẫm tự tạo cái hàm này, cũng tương tự hàm DATEDIF của Excel, nhưng vì WorksheetFunction không có nên làm hàm này. Mình muốn chia sẻ tí kiến thức. Mong được bổ sung góp ý.

Option Explicit
Function TinhDate(NgayNho As Date, NgayLon As Date, txt As String) As Currency
  Dim NgL As Currency, NgN As Currency
  On Error Resume Next
  If txt = "d" Or txt = "D" Then
    NgL = Format(NgayLon, "0")
    NgN = Format(NgayNho, "0")
    TinhDate = NgL - NgN
  ElseIf txt = "m" Or txt = "M" Then
    NgL = Day(NgayLon) / 30 + Month(NgayLon) + Year(NgayLon) * 12
    NgN = Day(NgayNho) / 30 + Month(NgayNho) + Year(NgayNho) * 12
    TinhDate = Format(NgL - NgN, "0.0")
  ElseIf txt = "y" Or txt = "Y" Then
    NgL = Day(NgayLon) / 360 + Month(NgayLon) / 12 + Year(NgayLon)
    NgN = Day(NgayNho) / 360 + Month(NgayNho) / 12 + Year(NgayNho)
    TinhDate = Format(NgL - NgN, "0.0")
  End If
End Function

 

[ndu96081631]

Tối qua mò mẫm tự tạo cái hàm này, cũng tương tự hàm DATEDIF của Excel, nhưng vì WorksheetFunction không có nên làm hàm này. Mình muốn chia sẻ tí kiến thức. Mong được bổ sung góp ý.

Option Explicit
Function TinhDate(NgayNho As Date, NgayLon As Date, txt As String) As Currency
  Dim NgL As Currency, NgN As Currency
  On Error Resume Next
  If txt = "d" Or txt = "D" Then
    NgL = Format(NgayLon, "0")
    NgN = Format(NgayNho, "0")
    TinhDate = NgL - NgN
  ElseIf txt = "m" Or txt = "M" Then
    NgL = Day(NgayLon) / 30 + Month(NgayLon) + Year(NgayLon) * 12
    NgN = Day(NgayNho) / 30 + Month(NgayNho) + Year(NgayNho) * 12
    TinhDate = Format(NgL - NgN, "0.0")
  ElseIf txt = "y" Or txt = "Y" Then
    NgL = Day(NgayLon) / 360 + Month(NgayLon) / 12 + Year(NgayLon)
    NgN = Day(NgayNho) / 360 + Month(NgayNho) / 12 + Year(NgayNho)
    TinhDate = Format(NgL - NgN, "0.0")
  End If
End Function

- Thứ nhất: Cả trong VBA lẩn bảng tính Excel đều có hàm này rồi ---> Vậy viết thêm làm gì?
- Thứ hai: Nếu viết thì vầy đi

Function TinhDate(NgayNho As Date, NgayLon As Date, txt As String) As Currency
  TinhDate = DateDiff(txt, NgayNho, NgayLon)
End Function

Chú ý: Hàm DateDiff trong VBA có phân biệt "y" và "yyyy" ---> 2 cái khác nhau về ý nghĩa nha ---> Xem Help

 

[Hoàng Trọng Nghĩa]

Ui, cám ơn Bác Ndu, hèn gì em tìm mãi mà không ra! Nhưng cũng nhờ vậy mình cũng mò mẫm làm hàm được! Nhưng cái hàm DATEDIF chưa đúng ở chỗ là 1 năm 6 tháng nó vẫn cho ra 1 năm thôi, có vẻ như chưa sát lắm!

 

[ndu96081631]

Ui, cám ơn Bác Ndu, hèn gì em tìm mãi mà không ra! Nhưng cũng nhờ vậy mình cũng mò mẫm làm hàm được! Nhưng cái hàm DATEDIF chưa đúng ở chỗ là 1 năm 6 tháng nó vẫn cho ra 1 năm thôi, có vẻ như chưa sát lắm!

1 năm 6 tháng ra 1 năm là đúng rồi còn gì ---> Chứ bạn muốn ra bao nhiêu?
Nó chỉ ra 2 khi phép trừ có kết quả >=24 tháng mà thôi
Hàm DATEDIF này nếu tự tính thì khá phức tạp à nha (năm nhuận hoặc không nhuận.. tùm lum cả) ---> May là Excel đã có sằn, nếu không là... MỆT

 

[Hoàng Trọng Nghĩa]

1 năm 6 tháng ra 1 năm là đúng rồi còn gì ---> Chứ bạn muốn ra bao nhiêu?
Nó chỉ ra 2 khi phép trừ có kết quả >=24 tháng mà thôi
Hàm DATEDIF này nếu tự tính thì khá phức tạp à nha (năm nhuận hoặc không nhuận.. tùm lum cả) ---> May là Excel đã có sằn, nếu không là... MỆT

Dạ, em nghĩ không phức tạp, bởi em đã thử hàm em làm rồi, 1 năm rưởi thì cho ra 1,5 năm là ok chứ sao chỉ là 1 thôi? Và theo hàm của thầy, em thử trong Sheet, cho ra lỗi #Value! kể cả 2 phương thức:
=TinhDate("d",A1,A2)
=TinhDate("d","29/06/2010","29/06/2011")

Rồi phát hiện chỗ sai rồi, muốn để txt trước thì phải đão lại như vầy:

Function TinhDate(txt As String, NgayNho As Date, NgayLon As Date) As Currency
  TinhDate = DateDiff(txt, NgayNho, NgayLon)
End Function

 

[ndu96081631]

Dạ, em nghĩ không phức tạp, bởi em đã thử hàm em làm rồi, 1 năm rưởi thì cho ra 1,5 năm là ok chứ sao chỉ là 1 thôi?

Hổng có vụ đó đâu! 1 năm mấy tháng nó cũng ra 1 thôi ---> Nó tính cái nào riêng cái đó, vì thế không bao giờ có số lẻ được ---> Muốn tính tháng riêng thì dùng tham số "YM" (đáng tiếc tham số này chỉ có với hàm trên bảng tính, trong VBA hổng có)

Và theo hàm của thầy, em thử trong Sheet, cho ra lỗi #Value! kể cả 2 phương thức:
=TinhDate("d",A1,A2)
=TinhDate("d","29/06/2010","29/06/2011")

Bạn viết ngược cha nó rồi còn gì ---> Phải vầy chứ =TinhDate(A1,A2,"d")
Nói thêm: Viết kiểu này "29/06/2010","29/06/2011" không được à nha ---> Phải vầy: "2010/1/31","2011/7/31" (quy tắc: Năm/Tháng/Ngày)

 

[Hoàng Trọng Nghĩa]

Đáng tiếc là 1/6/2010 và 31/7/2010 nó cho kết quả như 1/6/2010 và 1/7/2010 tức là 1 tháng! Vì thế mình cũng phải tự tạo cho phù hợp thôi!

Còn vấn đề tính ngày thì không bao giờ hàm tự tạo của em lại sai vì năm nhuần hay không nhuần. Hỏng biết thầy test thử chưa ạ?

Còn vấn đề cấu trúc yyyy/mm/dd hay dd/mm/yyyy thì do mình đặt trong Control panel thôi, nếu mình đặt như thế nào thì cấu trúc như thế đó ạ.

 

[ndu96081631]

Ah... có chiêu này bạn nghiên cứu thử xem!
Với hàm của bạn, nếu muốn dùng tham số "YM" hoặc "MD" thì bạn sẽ viết thế nào
Ẹc... Ẹc... Khoai à nha!

 

[Hoàng Trọng Nghĩa]

Ah... có chiêu này bạn nghiên cứu thử xem!
Với hàm của bạn, nếu muốn dùng tham số "YM" hoặc "MD" thì bạn sẽ viết thế nào
Ẹc... Ẹc... Khoai à nha!

Em đang chập chững tập viết hàm mà Thầy! Nhưng em không hiểu ý của thầy YM là gì MD là gì ạ? Nếu cho kết quả thì như thế nào?

 

[ndu96081631]

Em đang chập chững tập viết hàm mà Thầy! Nhưng em không hiểu ý của thầy YM là gì MD là gì ạ? Nếu cho kết quả thì như thế nào?

Thì điền YM hoặc MD vào cột B là biết liền chứ gì ---> DATEDIF của Excel có mấy tham số này mà, bạn chưa biết sao?

 

[Hoàng Trọng Nghĩa]

Thì điền YM hoặc MD vào cột B là biết liền chứ gì ---> DATEDIF của Excel có mấy tham số này mà, bạn chưa biết sao?

Trời ơi, nếu dễ như vậy thì tách ngày (day()) hoặc tháng (month()) ra mà trừ thôi chứ có gì đâu thầy? Thật tình mà nói thì tối hôm qua em mới biết được hàm DATEDIF thôi, mà trong HELP của máy em thì không có Giúp hàm này!

 

[ndu96081631]

Trời ơi, nếu dễ như vậy thì tách ngày (day()) hoặc tháng (month()) ra mà trừ thôi chứ có gì đâu thầy?

Cũng không chắc là dễ đâu nha
Bạn thử tạo hàm DATEDIF với tham số MD và tim kết quả với ngày nhỏ = 21/01/2010 và ngày lớn = 10/07/2011 xem nó ra bao nhiêu?
Hàm DATEDIF của Excel cho kết quả = 19 nhé

 

[ptm0412]

Ah... có chiêu này bạn nghiên cứu thử xem!
Với hàm của bạn, nếu muốn dùng tham số "YM" hoặc "MD" thì bạn sẽ viết thế nào
Ẹc... Ẹc... Khoai à nha!

Gọi A là ngày nhỏ, B là ngày lớn

1. Tham số "DM": tương đương tính số tháng lẻ sau khi tính năm:

Sothangle = DateDiff("m", A, B) Mod 12

2. Tham số "MD" tương đương tính số ngày lẻ sau khi tính tháng:

SoThang = DateDiff("m", A, B)
A1 = DateAdd("m", SoThang, A)
SongayLe = DateDiff("d", A1, B)

Sub tinh()
A = DateSerial(2010, 1, 21)
B = DateSerial(2011, 7, 10)
Sothang = DateDiff("m", A, B) - IIf(Day(A) >= Day(B), 1, 0)
Sothangle = Sothang Mod 12
MsgBox Sothang & Chr(10) & Sothangle

A1 = DateAdd("m", Sothang, A)
SongayLe = DateDiff("d", A1, B)
MsgBox A1 & Chr(10) & SongayLe
End Sub

Khoai củ gì nghiền ra cũng xơi tuốt.

Ghi chú: Hàm DateDiff (2 chữ f) có tính tròn lên có điều kiện. Mọi người nghiên cứu thêm.
Đồng thời ngâm kiú thêm hàm DateAdd

Chính vì vậy mới có vụ - IIf(Day(A) >= Day(B), 1, 0) trong code

Ghi chú: Có sửa > thành >= và 2 dòng trong code bài trên.

 

[ndu96081631]

Gọi A là ngày nhỏ, B là ngày lớn

1. Tham số "DM": tương đương tính số tháng lẻ sau khi tính năm:

Sothangle = DateDiff("m", A, B) Mod 12

2. Tham số "MD" tương đương tính số ngày lẻ sau khi tính tháng:

SoThang = DateDiff("m", A, B)
A1 = DateAdd("m", SoThang, A)
SongayLe = DateDiff("d", A1, B)

Sub tinh()
A = DateSerial(2010, 1, 21)
B = DateSerial(2011, 7, 10)
Sothang = DateDiff("m", A, B) - IIf(Day(A) >= Day(B), 1, 0)
Sothangle = Sothang Mod 12
MsgBox Sothang & Chr(10) & Sothangle

A1 = DateAdd("m", Sothang, A)
SongayLe = DateDiff("d", A1, B)
MsgBox A1 & Chr(10) & SongayLe
End Sub

Khoai củ gì nghiền ra cũng xơi tuốt.

Ghi chú: Hàm DateDiff (2 chữ f) có tính tròn lên có điều kiện. Mọi người nghiên cứu thêm.
Đồng thời ngâm kiú thêm hàm DateAdd

Chính vì vậy mới có vụ - IIf(Day(A) >= Day(B), 1, 0) trong code

Ghi chú: Có sửa > thành >= và 2 dòng trong code bài trên.

Ẹc.. Ẹc...
Em dùng Evaluate("DATEDIF(....)") code chỉ đúng 1 dòng

 

[ptm0412]

Ẹc.. Ẹc...
Em dùng Evaluate("DATEDIF(....)") code chỉ đúng 1 dòng

Rĩ nhiên rồi. Ai mà lại tạo ra 1 cái có sẵn thía. Tại ndu đố viết code tương tự DatedIf với tham số DM và MD mà?

Vả lại, mình thì nói về DateDiff và DateDif, nghĩa là lấy số nguyên, trong khi chiếu theo kết quả hàm UDF trong bài 1 của MinhThien, chủ topic muốn lấy số thập phân. (Lấy số thập phân thì sao mà gọi là tương đương Datedif được nhỉ?)

Mà cần số thập phân thì cần quái gì UDF nhỉ?

Gọi A là ngày nhỏ, B là ngày lớn, xài hàm Excel như sau:

1. Tham số D: = B - A

2. Tham số M: = (B - A)/30 hoặc muốn chính xác hơn thì = Datedif(A, B, "M") + Datedif(A, B, "MD")/30

3. Tham số Y: = (B - A)/ 365

 

[ndu96081631]

Rĩ nhiên rồi. Ai mà lại tạo ra 1 cái có sẵn thía. Tại ndu đố viết code tương tự DatedIf với tham số DM và MD mà?

Có chứ sư phụ! Lý do vì minhthien muốn 1 năm 6 tháng sẽ ra kết quả = 1.5 ---> Vì thế mới cần tạo UDF
Nói tóm lại: Theo em hiểu thì hàm DATEDIF trong Excel chưa đáp ứng đựoc nhu cầu của minhthien, vì thế bạn ấy phải viết UDF ---> Và cách của em là sửa lại những cái đã có sẳn đồng thời mở rộng thêm cho các tham số khác
Chỉ vậy thôi

 

[ptm0412]

Lý do vì minhthien muốn 1 năm 6 tháng sẽ ra kết quả = 1.5 ---> Vì thế mới cần tạo UDF
Nói tóm lại: Theo em hiểu thì hàm DATEDIF trong Excel chưa đáp ứng đựoc nhu cầu của minhthien, vì thế bạn ấy phải viết UDF[/B][/U]
Chỉ vậy thôi

Dùng datedif dĩ nhiên là không đáp ứng. Một đằng hàm lấy số nguyên, 1 đằng muốn lấy số thập phân, lấy râu ông nọ cắm cằm bà kia. Ngay cả tiêu đề topic cũng nói lên việc hiểu sai 1 vấn đề mang tính bản chất của hàm.

Sao không dùng cách tính toán thông thường như bài trên mình gợi ý?

Gọi A là ngày nhỏ, B là ngày lớn, xài hàm Excel như sau:
1. Tham số D: = B - A
2. Tham số M: = (B - A)/30 hoặc muốn chính xác hơn thì = Datedif(A, B, "M") + Datedif(A, B, "MD")/30
3. Tham số Y: = (B - A)/ 365

Hãy suy nghĩ đơn giản 1 chút theo cấp lớp tương ứng thôi (trường hợp này là cấp lớp 5), suy nghĩ hàn lâm làm gì.

 

[Hoàng Trọng Nghĩa]

Dùng datedif dĩ nhiên là không đáp ứng. Một đằng hàm lấy số nguyên, 1 đằng muốn lấy số thập phân, lấy râu ông nọ cắm cằm bà kia. Ngay cả tiêu đề topic cũng nói lên việc hiểu sai 1 vấn đề mang tính bản chất của hàm.

Sao không dùng cách tính toán thông thường như bài trên mình gợi ý?


Hãy suy nghĩ đơn giản 1 chút theo cấp lớp tương ứng thôi (trường hợp này là cấp lớp 5), suy nghĩ hàn lâm làm gì.

Nếu muốn như datedif trong Excel đơn giản chỉ khai báo CURRENCY thành LONG hay DOUBLE là OK.

 

[ptm0412]

Long là gì và Double là gì? MinhThien thử chưa?
Long là số nguyên thì đúng rồi, Double là số thực mà?

 

[Hoàng Trọng Nghĩa]

Long là gì và Double là gì? MinhThien thử chưa?
Long là số nguyên thì đúng rồi, Double là số thực mà?

Xin thưa tại em nói không rõ nghĩa thôi!

Em làm như vầy thì ổn hơn:
Function TINHDATE(NgayNho As Date, NgayLon As Date, txt As String) As Long
Dim NgL As Double, NgN As Double

Nhưng nếu hoàn toàn giống DATEDIF, thì tất cả là LONG hết!

Function TINHDATE(NgayNho As Date, NgayLon As Date, txt As String) As Long
Dim NgL As Long, NgN As Long

Nói vậy thôi, chứ "trứng sao khôn hơn vịt" được, em đang tập viết hàm, biết đâu vài năm nữa em học được vài trang của các Thầy làm sao! hihihii

 

[ptm0412]

Để học và thực hành thì không chọt nữa!
Có điều NgL và NgN dù khai báo kiểu gì nó cũng là số nguyên, vì

Function TinhDate(NgayNho As Date, NgayLon As Date, txt As String)

Ngày tháng trong Excel hay trong VBA khi tính toán đều bị coi là số cả. Mà biến Date thì sẽ luôn là số nguyên.

Quay lại code bài #1: nếu khai báo lại biến Currrency thành Long, sau khi tính toán ra số thập phân (vì bản chất các phép tính chia 30, chia 12, chia 360, sẽ ra kết quả thập phân), VBA sẽ làm tròn thành số nguyên tương tự dùng Round.
Kể cả khi dùng hàm format, format(5.26, "0.0") sẽ bằng 5.3 tương tự làm tròn 1 số thập phân bằng Round.

Trong khi DateDif không làm tròn mà cắt bỏ khúc đuôi, tương tự Roundown.

Thử thì biết, chứ nói suông có khi không tin.

 

[Trần Văn Bình]

Nhờ các bác xem hàm dateif

Hiện tôi làm 1 file đếm năm có file kèm theo nhờ các bác chỉ giúp

 

[Hoàng Trọng Nghĩa]

Hiện tôi làm 1 file đếm năm có file kèm theo nhờ các bác chỉ giúp

1) Tại cột A; cột B phải nhập đủ ngày, tháng năm là tính đúng còn chỉ nhập năm là tính sai?

- Nếu gõ 1/1/2005 và 2005 là hoàn toàn khác, Excel tính 1 là 1 ngày và 2005 có nghĩa là 2005 ngày chứ không phải là năm như cái chúng ta nhìn thấy nó hiển thị. Thực sự 1/1/2005 chính là 38353 là giá trị thực sự của nó, vì vậy nó tính không như ta thấy, đương nhiên kết quả sẽ không đúng. Nếu muốn tính được, bạn cứ gõ 1/1/2005 rồi định dạng chúng kiểu yyyy trong format cells.

2) Hàm today() + thêm 1 năm ( Năm hiện tại + 1 năm) dùng hàm gì?

- Tôi nghĩ có thể bạn nên dùng công thức này:

=DATE(YEAR(TODAY())+1;MONTH(TODAY());DAY(TODAY()))

3) Trong khi tôi dùng = DATEDIF(MAX(A9:B9);TODAY()+year(1) ;"Y") thì ra kết quả khác

Vì công thức của bạn chưa đúng nên không cho ra kết quả đúng (như câu 2).

Công thức sẽ là:

=DATEDIF(MAX(A9:B9);DATE(YEAR(TODAY())+1;MONTH(TODAY());DAY(TODAY()));"Y")

 

[Trần Văn Bình]

Vâng cảm ơn bản đã giúp