vba help - date issue

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I am splitting Column A Data, and updating it in Column B.
Column A data is in DD/MM/YYYY Format, while pasting values are getting pasted in mm/dd/yyyy format.

How to solve it.


Expected output is in Column D. ... Macro is giving Column B output.

Book4
ABCD
1DateMax DateExpected
230-01-2020/02-09-2019/16-09-201930/01/202030/01/2020
304-09-2019/18-09-2019/14-10-2019/23-01-202023/01/202023/01/2020
418-09-2019/30-09-2019/05-11-2019/23-01-202023/01/202023/01/2020
516-09-2019/25-09-2019/04-11-201911/04/201904/11/2019
601-10-2019/23-01-2020/09-10-2019/04-11-201923/01/202023/01/2020
701-10-2019/28-10-2019/30-01-2020/11-11-201930/01/202030/01/2020
803-02-2020/01-10-2019/30-01-202002/03/202003/02/2020
Sheet1



'Below is the Code
VBA Code:
Option Explicit
Sub test()

    Dim ar As Variant
    Dim i As Long
    Dim maxdate As Date
    Dim arrdate As Long
    Dim d As Long
    
    For i = 2 To 10
   arrdate = 0
        If Cells(i, 1).Value <> "" And Len(Cells(i, 1).Value) > 5 Then
            ar = Split(Cells(i, 1).Value, "/")
                For d = LBound(ar) To UBound(ar)
                If CDate(ar(d)) > arrdate Then
                    arrdate = CDate(ar(d))
                End If
                  
                Next
                 maxdate = arrdate
                Cells(i, 2).Value = Format(maxdate, "dd/mm/yyyy")
        End If
  
    Next i

end Sub

Thanks
mg
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Why not just use the function you created?
 
Upvote 0
Hi Fluff,

Yes, I am going to use that only.
Just for learning purpose asking this question , I face lot of date issues like that.



Thanks
mg
 
Upvote 0
Easiest thin is change Maxdate to long & use
VBA Code:
                Cells(i, 2).Value = maxdate
 
Upvote 0
Hi Fluff,

It worked ! Small but really helpful. Thanks once again ! (y)?


Thanks
mg
 
Upvote 0
Hi Fluff,

Need one more help , Same Situation Date issue.

Getting mm/dd/yyyy ...... as a output Instead of DD/MM/YYYY

What needs to change in below code , to get correct output. unable to find which line creating problem.


VBA Code:
Sub test2()

Dim ar As Variant
Dim str As String

Dim i As Long
Dim maxdate As Long

For i = 2 To 8
    str = Cells(i, 1).Value
    maxdate = MAXSPLIT(str, "/")
     Cells(i, 2).Value = Format(maxdate, "dd/mm/yyyy")
Next i


End Sub

Public Function MAXSPLIT(ByVal Text As String, ByVal Delimiter As String) As Long
    Dim i As Long
    Dim TextArray() As String
    TextArray = Split(Text, Delimiter)
    Dim ValueArray() As Long
    ReDim Preserve ValueArray(UBound(TextArray))
    For i = LBound(TextArray) To UBound(TextArray)
        ValueArray(i) = CLng(CDate(TextArray(i)))
    Next
    ' You can use any other function here: Average, Min etc.
    MAXSPLIT = WorksheetFunction.Max(ValueArray)
End Function


Thanks
mg
 
Upvote 0
Get rid of the format & do it like post#4
 
Upvote 0
Hi Fluff,

Getting Below output, getting output as Numbers and Date in Column B.
When removed date format,
and When Manually converted to short date output is matching.

How to avoid manually converting to short date. via vba.

Below is Output Generated.
Book4
ABCD
1DateMax DateExpected
230-01-2020/02-09-2019/16-09-20194386030/01/2020
304-09-2019/18-09-2019/14-10-2019/23-01-20204385323/01/2020
418-09-2019/30-09-2019/05-11-2019/23-01-20204385323/01/2020
516-09-2019/25-09-2019/04-11-201904/11/201904/11/2019
601-10-2019/23-01-2020/09-10-2019/04-11-20194385323/01/2020
701-10-2019/28-10-2019/30-01-2020/11-11-20194386030/01/2020
803-02-2020/01-10-2019/30-01-202003/02/202003/02/2020
Sheet1


Thanks
mg
 
Upvote 0
Just format the column to date.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top