? Tìm Min và Max của range ngày tháng (1 người xem)

[vumian]

Dear all,

Giả sừ mình có 1 range chứa toàn bộ là ngày tháng năm không liên tục nhau, bao gồm cả những ô trống, Vậy làm sao dùng Fx và VBA để lấy được ngày nào là cũ nhất , ngày nào là gần hiện tại nhất, cũng có nghĩa là min và max của ngày

Cám ơn nhiều

 

[salam]

Câu trả lời bạn có rồi thôi, dùng hàm min và max là được.

 

[ThuNghi]

Option Explicit
Function MaxDate(rng As Range) As Date
MaxDate = WorksheetFunction.Max(rng)
MaxDate = DateSerial(Year(MaxDate), Month(MaxDate), Day(MaxDate))
End Function

Chả biết nó có thừa cái gì không hay thiếu cái gì, thấy vẫn chạy tốt.

 

[anhtuan1066]

E rằng bạn có gì đó chưa nói rõ... Chứ nếu chỉ đơn giản thế thì ra rồi còn gì:
=MAX(Range) ra ngày mới nhất
=MIN(Range) ra ngày củ nhất
Sao đây?

 

[vumian]

hi, Chưa có ra bác ơi, hic

 

[ThuNghi]

hi, Chưa có ra bác ơi, hic

Do cột ngày của bạn là text, bạn thừ type(d2) =???
B2=DATEVALUE(D2)
Hay là
B2=DATE(YEAR(D2),MONTH(D2),DAY(D2))
sau đó dùng min, max theo B.

 

[ptm0412]

Cột ngày tháng của bạn bị định dạng text rồi, định dạng lại không được ngay. Chắc bạn lấy dữ liệu từ nguồn khác không phải Excel chăng?

 

[vumian]

HIc, hèn chi, vậy bây giờ giải quyết làm sao đây bác, hông lẽ cứ date(left,mid,right), mà nếu vậy thì làm sao lấy duoc max min ?

Éo le thiệt, dữ liệu là text

 

[FunnyBoy]

hi, Chưa có ra bác ơi, hic

Do dữ liệu bạn nhập vào dạng ngày/tháng/năm nhưng trong Control Panel lại là tháng/ngày/năm nên Excel hiểu là Text và không tự nhận dạng được (có thể bạn lấy từ nguồn dữ liệu khác vào Excel).

Bạn đóng Excel và vào Control Panel chỉnh Date lại thành dd/mm/yyyy là OK.

FB.

 

[vumian]

To FunnyBoy,
Giải quyết bằng Fx hay VBA thôi, vì máy mình chỉnh hôg có được

 

[ptm0412]

Bạn đóng Excel và vào Control Panel chỉnh Date lại thành dd/mm/yyyy là OK.

FB.

không phải, máy mình dd/mm/yy sẵn rồi mà cũng không đọc được. Format lại cũng hông đọc được; phải F2, không làm gì cả rồi enter mới ra ngày. Chịu khó làm từng cell vậy đi:
- Fomat ngày nguyên cột D
- hai tay 2 phím F2 và enter, gõ nhịp như nghe nhạc vậy đó.

 

[vumian]

không phải, máy mình dd/mm/yy sẵn rồi mà cũng không đọc được. Format lại cũng hông đọc được; phải F2, không làm gì cả rồi enter mới ra ngày. Chịu khó làm từng cell vậy đi:
- Fomat ngày nguyên cột D
- hai tay 2 phím F2 và enter, gõ nhịp như nghe nhạc vậy đó.

Trời , hai tay 2 phím F2 và enter, gõ nhịp như nghe nhạc thì hông được rùi, mình đang đưa chúng vô chương trình mà, hôg lẽ đành bó tay

 

[ThuNghi]

Trời , hai tay 2 phím F2 và enter, gõ nhịp như nghe nhạc thì hông được rùi, mình đang đưa chúng vô chương trình mà, hôg lẽ đành bó tay

Vậy bạn thêm một code sửa ngày, đại lọai

Sub SuaDate()
Dim rng As Range
Dim iR As Long, i As Long
Set rng = Range("D2100")
iR = rng.Rows.Count
For i = iR To 1 Step -1
If IsNull(rng(i)) Then rng(i) = 0
If Application.WorksheetFunction.IsText(rng(i)) Then 'MsgBox i
rng(i) = CDate(rng(i))
rng(i).NumberFormat = "dd/mm/yy"
End If
rng(i).NumberFormat = "dd/mm/yy"
Next
Set rng = Nothing
End Sub

 

[anhtuan1066]

Món này nếu dùng công thức cũng ko phải là khó...Dùng Left, Right, Mid tách ngày tháng năm ra cột phụ... Ráp lại bằng hàm DATE... Tiếp theo copy cột phụ này paste Special\Value vào dử liệu củ... Món này tôi bị hoài khi người ta gữi dử liệu... hi... hi...
ANH TUẤN

 

[tigertiger]

Nếu là tín đồ của VBA thì dùng 2 hàm sau do tigertiger viết (đôi khi tìm hiểu thêm VBA cũng rất hay)

Public Function maxNgay(rgn As Range) As Date

Dim dd As Date

Dim ddm As Date
Dim st, sd, sm, sy As String
Dim k As Integer
k = 0
For Each st In rgn
If st <> "" Then

sd = Left(st, InStr(st, "/") - 1)
sm = Mid(st, InStr(st, "/") + 1, InStr(InStr(st, "/") + 1, st, "/") - InStr(st, "/") - 1)
sy = Right(st, Len(st) - InStrRev(st, "/"))
dd = DateSerial(Int(Val(sy)), Int(Val(sm)), Int(Val(sd)))
k = k + 1
If (k = 1) Or ((k > 1) And (ddm < dd)) Then ddm = dd
End If
Next

maxNgay = ddm
End Function

và

Public Function minNgay(rgn As Range) As Date


Dim dd, ddm As Date
Dim st, sd, sm, sy As String
Dim k As Integer
k = 0
For Each st In rgn
If st <> "" Then

sd = Left(st, InStr(st, "/") - 1)
sm = Mid(st, InStr(st, "/") + 1, InStr(InStr(st, "/") + 1, st, "/") - InStr(st, "/") - 1)
sy = Right(st, Len(st) - InStrRev(st, "/"))
dd = DateSerial(Int(Val(sy)), Int(Val(sm)), Int(Val(sd)))
k = k + 1
If (k = 1) Or ((k > 1) And (ddm > dd)) Then ddm = dd

End If
Next
minNgay = ddm

End Function

lưu ý:
chỉ lưu ý là bạn để nguyên ô dữ liệu gốc (đừng bấm phím F2 - vì khi đó sẽ trở thành date của excel hi hi) - nhưng có cách: nó là text rui thì cho nó là text lun chắc chắn luôn bằng cách chọn vùng dữ liệu -> rồi format cell \ number \ chọn text lun - khi đó sửa chỉnh thỏa mãi vẫn là text


chúc thành công

 

[anhtuan1066]

Thế sao bạn ko đưa luôn đoạn code Format toàn bộ thành Text.. như vậy có phải là thuận tiện hơn ko?
và trong trường hợp cụ thể này tôi nghĩ dùng Sub hay hơn Function.. Đàng nào thì nó chỉ dùng 1 lần trong những lúc cần thiết, đúng ko? Ko làm gì cả.. Bấm nút và.. xong..

 

[tigertiger]

Dùng sub thì nó k thay đổi khi dữ liệu thay đổi, atuan... ah,
thường trả về 1 giá trị thì ng ta dùng hàm hay hơn

chỉ khi thực hiện 1 loạt động tác như format, filter chẳng hạn thì dùng sub

Ok tôi gửi file kèm lên đây - nhưng tigertiger nghĩ việc format text cho ô đơn giản mà

 

[anhtuan1066]

Ý tôi là như vầy nè:
-Vì dử liệu vô ý bị format là text nên ta mới cần chạy code này đễ convert tất cả thành dạng DATE... Và nó chỉ dùng 1 lần duy nhất
-Sau khi dử liệu đã trở thành DATE thì việc tìm MAX và MIN ko cần dùng code làm gì... Giao cho hàm Excel có phải là hay nhất ko?
Nói chung khi Convert xong thì huỷ bỏ luôn code (ko cần thiết nữa)... Ta có thể lưu code thành file .txt dự phòng mai này cần đến
Bạn hiểu ý tôi nói chứ
ANH TUẤN

 

[tigertiger]

Ý tôi là như vầy nè:
-Vì dử liệu vô ý bị format là text nên ta mới cần chạy code này đễ convert tất cả thành dạng DATE... Và nó chỉ dùng 1 lần duy nhất
-Sau khi dử liệu đã trở thành DATE thì việc tìm MAX và MIN ko cần dùng code làm gì... Giao cho hàm Excel có phải là hay nhất ko?


Nói chung khi Convert xong thì huỷ bỏ luôn code (ko cần thiết nữa)... Ta có thể lưu code thành file .txt dự phòng mai này cần đến
Bạn hiểu ý tôi nói chứ
ANH TUẤN

y, hiểu ý atuan... rui, cám ơn atuan...,
nhưng hàm tigertiger viết là tính max và min về ngày cho dữ liệu text lun , ko cần chuyển đổi, - vì tigertiger hiểu ý vumian là : cần hàm xử lý ->KQ k quan tâm đổi dữ liệu kia hi hi hi / không rõ có phải ý vumian đúng thế k?

 

[vumian]

hi tigertiger,

Hàm bạn ra kết quả mình muốn, nhưng vùng dữ liệu của mình không số định khi lấy tư nguon khác, nên hàm bác sẽ hôg tự động chọn vùng động được

hi ThuNGhi,

Code của bác gần ok rồi, phải thêm vào cái for cho nó chạy từ đầu đến cuối, và chuyển sang dạng date của excel luôn, rùi mình sẽ dùng max - min trong VBA luôn, chà chà

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

 

[ThuNghi]

Theo tôi bạn nên xử lý từ khi trích dữ liệu ra, nên lấy ngày theo dạng dateserial(year,m,d) luôn. Tránh có những code edit, thà làm từ đầu vẫn hơn. Tóm lại nên đồng nhất. Dữ liệu này bạn trích cũng từ Excel luôn mà.
Sao bạn không nhấn thanks mọi người nhỉ. Hàm của Tigertiger hay lắm.

 

[vumian]

Bac ThuNghi ơi, Đây là dữ liệu từ nguồn khác, nó được thiết lập lúc còn Excel95, mà khi lấy dữ liệu thì read-only thôi, sao mà định dạng được bác

 

[vumian]

hi ThuNghi, khi lấy dữ liệu, thì chạy query mà, nó duoc định đạng nhu vậy, không xử lý được trước khi lấy ra,
Giờ mình có ý này, dùng For để chuyển sang dạng Date của Excel hết, rùi Sort, thế là mình lấy được đầu và cuối range

 

[vumian]

Dear all, mình đã lấy duoc Max-Min rùi

Sub cvt_Date()
Dim iR As Long, i As Long
Dim col, longest_row As Integer
With Sheet5
    longest_col = .Range("F1").Column
    longest_row = .Cells(.Rows.Count, longest_col).End(xlUp).Row
End With
For i = 2 To longest_row Step 1
    If Application.WorksheetFunction.IsText(Cells(i, 7)) Then 'MsgBox i
Cells(i, 7) = CDate(Cells(i, 7))
Cells(i, 7).NumberFormat = "dd/mm/yy"
End If
Cells(i, 7).NumberFormat = "dd/mm/yy"
Next
Dim rng, maxDate As Range
Set rng = Range("G2:G" & longest_row)
MsgBox "Max " & Application.WorksheetFunction.Max(rng)
MsgBox "Min " & Application.WorksheetFunction.Min(rng)
End Sub

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

 

[tigertiger]

Dear all, mình đã lấy duoc Max-Min rùi

oh, sub của bạn k tổng quan nhỉ vì: nó đặc thù cho sheet 5, rồi nữa max phải chép ra từ msgbox lun, hơn nữa hình như chưa chính xác - bạn ktra lại với trường hợp ngày "8/11/2007" nhưng lại chuyển thành 11/8/2007 (??? )

bạn tham khảo file tigertiger gửi kèm nhé

tigertiger đã làm tổng quát chắc là vừa ý bạn:

+ bấm nút Định dạng ngày
+ xuất hiện hộp thoại chọn vùng dữ liệu ngày dạng text định đổi
+ xuất hiện tiếp hộp thoại chọn ô sẽ đặt KQ max/min
+ chạy sẽ định dạng ngày tháng và đồng thời cho kết quả max / min dạng công thức lun tại vị trí chọn ở hộp thoại 2

xem ở trong file gửi kèm (có nút bấm), hoặc copy code sau:

Public Sub DinhdangDate()

Dim Rgn As Range
Dim Rgn2 As Range
Dim dd As Date
Dim st, sd, sm, sy As String
Dim i As Integer

On Error GoTo Thoat
Set Rgn = Application.InputBox("Chon vung muon doi ngay!", "Chon vung", Selection.Address, , , , , 8)

On Error GoTo Thoat
Set Rgn2 = Application.InputBox("Chon vi tri o KQ max min!", "Chon o", Selection.Address, , , , , 8)
If Rgn2.Cells.Count > 1 Then Set Rgn2 = Cells(Rgn2.Row, Rgn2.Column)

i = 0
For Each st In Rgn
i = i + 1
If st <> "" Then
sd = Left(st, InStr(st, "/") - 1)
sm = Mid(st, InStr(st, "/") + 1, InStr(InStr(st, "/") + 1, st, "/") - InStr(st, "/") - 1)
sy = Right(st, Len(st) - InStrRev(st, "/"))
dd = DateSerial(Int(Val(sy)), Int(Val(sm)), Int(Val(sd)))
Rgn(i, 1) = dd
End If
Next

With Rgn
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
End With

Rgn2 = "Max:": Rgn2.Offset(1, 0) = "Min:"

With Rgn2.Offset(0, 1)
.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
.Font.Bold = True
.Formula = "=MAX(" + Rgn.Address + ")"
End With

With Rgn2.Offset(1, 1)
.Offset(0, 1).NumberFormat = "dd/mm/yyyy"
.Font.Bold = True
.Formula = "=MIN(" + Rgn.Address + ")"
End With

Thoat:

End Sub

chúc thành công,

có nhiều tiện ích hay - trong sub trên đáng bàn và tham khảo - mong TV góp ý

 

[Mr Okebab]

oh, sub của bạn k tổng quan nhỉ vì: nó đặc thù cho sheet 5, rồi nữa max phải chép ra từ msgbox lun, hơn nữa hình như chưa chính xác - bạn ktra lại với trường hợp ngày "8/11/2007" nhưng lại chuyển thành 11/8/2007 (??? )

bạn tham khảo file tigertiger gửi kèm nhé

tigertiger đã làm tổng quát chắc là vừa ý bạn:

+ bấm nút Định dạng ngày
+ xuất hiện hộp thoại chọn vùng dữ liệu ngày dạng text định đổi
+ xuất hiện tiếp hộp thoại chọn ô sẽ đặt KQ max/min
+ chạy sẽ định dạng ngày tháng và đồng thời cho kết quả max / min dạng công thức lun tại vị trí chọn ở hộp thoại 2

xem ở trong file gửi kèm (có nút bấm), hoặc copy code sau:


chúc thành công,

có nhiều tiện ích hay - trong sub trên đáng bàn và tham khảo - mong TV góp ý

Phức tạp nhỉ.
Em góp thêm 1 hàm UF tính ngày Min Max nhé :

Function NgayMinMax(Mang As Range, Loai As String) As Date
    'Tim Ngay Lon Nhat va Ngay nho nhat
    Application.Volatile (False)
    Dim i1 As Byte, i2 As Byte
    Dim NgayMin As Date, NgayMax As Date, TempNgay As Date
    Dim Ngay As Range
    NgayMin = 100000
    For Each Ngay In Mang
        i1 = InStr(1, Ngay, "/")
        i2 = InStr(i1 + 1, Ngay, "/")
        If i1 > 0 And i2 > 0 Then
            TempNgay = DateSerial(Val(Mid$(Ngay, i2 + 1, 4)), Val(Mid$(Ngay, i1 + 1, i2)), Val(Left(Ngay, i1 - 1)))
            If TempNgay < NgayMin Then
                NgayMin = TempNgay
            ElseIf TempNgay > NgayMax Then
                NgayMax = TempNgay
        End If: End If
    Next
    If Loai = "Min" Then
        NgayMinMax = NgayMin
    ElseIf Loai = "Max" Then
        NgayMinMax = NgayMax
    End If
    Set Ngay = Nothing
End Function

Thân!

 

[vumian]

Hi,

Đúng là cái VBA mình chưa chính xác, vì cái range date này lộn xộn theo cách nhìn và hiểu thì nó là dd/mm/yyyy

Thành thật cám ơn bác và diễn đàn nhiều, mình làm xong rồi,
Bác dễ thương quá, hehe

 

[tigertiger]

Hi,

Thành thật cám ơn bác và diễn đàn nhiều, mình làm xong rồi,
Bác dễ thương quá, hehe

oh, sao bạn k chia sẻ code của bạn cho mọi ng - như thế mới là học hỏi, và bàn luận chứ,..

 

[kongcom]

Bạ̣n làm như sau : Chọn vùng ghi thời gian(D262)
Edit----->repplace----> thay thế các gạch cheó (/)bằng gạch ngang (-) . Sau đó format cells thành date .
Quay lại ô chứa hàm . =MIN(D262)
=Max(D262)
Ok

 

[vumian]

oh, sao bạn k chia sẻ code của bạn cho mọi ng - như thế mới là học hỏi, và bàn luận chứ,..

Hihi, nói mình share mắc cỡ quá, mình chỉnh lại tí tẹo code của bác thôi mà

Sub cvt_Date()
Dim col, longest_row, i As Integer
Dim Rgn As Range
Dim dd As Date
Dim st, sd, sm, sy As String
Sheet5.Select
With Sheet5
longest_col = .Range("I1").Column
longest_row = .Cells(.Rows.Count, longest_col).End(xlUp).Row
End With
Set Rgn = Range("G2:G" & longest_row)
 
Application.Calculation = xlCalculationManual
 
i = 0
For Each st In Rgn
i = i + 1
If st <> "" Then
sd = Left(st, InStr(st, "/") - 1)
sm = Mid(st, InStr(st, "/") + 1, InStr(InStr(st, "/") + 1, st, "/") - InStr(st, "/") - 1)
sy = Right(st, Len(st) - InStrRev(st, "/"))
dd = DateSerial(Int(Val(sy)), Int(Val(sm)), Int(Val(sd)))
Rgn(i, 1) = dd
End If
Next
With Rgn
.NumberFormat = "dd/mm/yyyy"
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
End With
Application.Calculation = xlCalculationAutomatic
Sheet3.Range("E2").Value = Application.WorksheetFunction.Min(Rgn)
Sheet3.Range("H2").Value = Application.WorksheetFunction.max(Rgn)