vba help - date issue

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Why not just use the function you created?
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Easiest thin is change Maxdate to long & use
VBA Code:
                Cells(i, 2).Value = maxdate
 

Mallesh23

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

ADVERTISEMENT

Hi Fluff,

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


Thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
My pleasure.
 

Mallesh23

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

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Get rid of the format & do it like post#4
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Just format the column to date.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,731
Members
416,333
Latest member
Time2Learn

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
Top