Date Format Issue when converting Text File to Excel File

[RajeshT (Rajesh)]

Hi Team,

By using Generate Data Table am converting Text file to Excel file,the data which i have in date format from text file automatically changing to another date format in excel.
How can i fix this:
Text file Date Data:03-Dec-2019
After converting displaying as:12/3/2019.

I want to display the data in same format(03-Dec-2019) in excel file ,by using this variable which i need to send an email in same format.
Appreciate your help!!

[ImPratham45 (Prathamesh Patil)]

@RajeshT

12/3/2019

After writing it to excel
Use Invoke VBA
and attach textfile in it with code :-

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("I:I").Select
    Selection.NumberFormat = "[$-409]d-mmm-yy;@"
End Sub

[RajeshT (Rajesh)]

Hi @ImPratham45,

Thank you for your response.
I am getting below error:

[ImPratham45 (Prathamesh Patil)]

Do following steps:

[RajeshT (Rajesh)]

Hi @ImPratham45,

I have changed the settings and no error is coming now.
But Zero(0) is missing from the date which i also want to capture .

[ImPratham45 (Prathamesh Patil)]

which zero?

[RajeshT (Rajesh)]

Hi @ImPratham45,

Instead of 03-Dec-2019,its displaying as 3-Dec-2019.

[ImPratham45 (Prathamesh Patil)]

@ImPratham45

Sub Macro2() ’ ’ Macro2 Macro ’ ’ Range(“I:I”).Select Selection.NumberFormat = “[$-409]d-mmm-yy;@” End Sub

in that
just change
d to dd

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("I:I").Select
    Selection.NumberFormat = "[$-409]dd-mmm-yy;@"
End Sub

[ImPratham45 (Prathamesh Patil)]

Is it working?

[RajeshT (Rajesh)]

Hi @ImPratham45,

Its working fine,sorry for late reply as i am out of office by the time.
Thanks a ton!!