Changing date as string to date format

[Baldev Kumar]

HI

I am having problem in converting Date column as string to date format, when i am using date format "dd/mm/yy it is converting first two digit of strings as "mm" and next two as "dd" instead of "dd" and "mm" respectively

 Sub IdbiStatement ()  
 Range("D1:E500").Select 
    With Selection 
    .NumberFormat = "MM/DD/yyyy" 
    .Value = .Value 
 
 
         
    End With 
End Sub  

[sintek]

That's because your code does that...

Please Login or Register  to view this content.

change it to...

Please Login or Register  to view this content.

[Baldev Kumar]

I have tried that one first but it is not helping, mean actual date is "02 Jun 2020" but excel is reading it as "06 feb 2020" if i convert it through VBA.
Without vba, in cell D2 if i use F2 key and press enter than it is reading correct date

[Marc L]

Hi,

without any code with an Exel basics : the convert feature (maybe in Data menu, known also as Text To Columns)
so just well anwsering to its Assistant …

If really really a VBA procedure is needed it requires a single codeline just using the TextToColumns VBA method …

[sintek]

Look at this...

[Baldev Kumar]

HI Marc L

thanks for reply
I just need a vba code to correct the date as "02 Jun 2020", excel is reading it as "06 feb 2020" after converting from string to date in each formats i.e dd/mm/yy or mm/dd/yy

[Marc L]

Directly use the Excel feature on the source column to convert bad text dates to Excel dates …

[Baldev Kumar]

it is still showing data as a text, excel is not grouping the cell values as date

[Baldev Kumar]

i have to download 50+ file daily and combine them in their master sheets, so i can't do this manually for each sheet, so i have created a code to do all other formatting and adding them to master sheets but but stuck with date correction.

[Marc L]

How do you load data to Excel ? As maybe it's just a bad import …

[Marc L]

The Excel feature well works on my side with your attachment …

[Baldev Kumar]

this is the "example file" i am directly downloading from banks website.

[Baldev Kumar]

and after using code it is covering first two digits into "mm" instead of "dd"

[sintek]

this is the "example file" i am directly downloading from banks website.

Where.....

[Marc L]

Kumar,

what are your Windows Regional Settings for dates : US, UK or ?

[Baldev Kumar]

steps

1. downloading file in download folder in c drive
2. opening file
3. copy code to downloaded file
4. running

vba code

 
Sub IdbiStatement ()  
 
 Range("D1:E500").Select 
    With Selection 
    .NumberFormat = "dd/mm/yyyy" 
    .Value = .Value 
       
    End With 
End Sub  

5. getting result in cell d2= 06/02/2020


i am using uk date format

Is there a way to interchange date value and month value

[sintek]

Have you just ignored Post 5

[Baldev Kumar]

i have tried that but in that excel is reading date as text.

[Marc L]

Yes, just warming a couple of neurons & well using the Excel feature as I yet wrote …
The next demonstration was started by a kid - I gave him as a training - using the Macro Recorder
and just operating manually you know what : he well succeed !

According to your attachment as a very VBA beginner starter :

Sub Demo4Noob1() 
    With Range("E2:E" & [C1].CurrentRegion.Rows.Count) 
        .Formula = "=LEFT(D2,10)" 
        .Formula = .Value 
        .TextToColumns  .Cells(1), xlDelimited, FieldInfo:=[{1, 4}] 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Baldev Kumar]

Thanks for help,

it working fine with single sheet but when combining with whole macro,

it is taking long time in formatting and copying data to master sheet,

but still this is serving my purpose.

i will come back if found some better solution.

[Marc L]

Ask the bank to be clever with at least correct Excel dates (so not as text !) in its Excel workbooks !

[Vraag en antwoord]

Excel is smart enough these days.

Please Login or Register  to view this content.

[Baldev Kumar]

Excel is smart enough these days.

Please Login or Register  to view this content.

i have already tried this
all date in sheet are for June month but my excel is reading 06 i.e. June as "dd"

[nankw83]

Try below code but not sure if its any faster than Marc's code …

Please Login or Register  to view this content.

[jindon]

Try

Please Login or Register  to view this content.