Extract Date from Date Time Format

[pmich]

I have a monthly file with 55 Rows.
(Every month the number of rows change.)

In Col A, from Row 2 to Row 55, I have date and time as Jun 25, 2018 10:00:36 AM
I want to extract the Date from Col A to Col K from Row 2 to Row 55.

I was using =INT(A2).

Range("K2:K" & LastrecNum).Formula = "=INT(A2)"

Now it is not working in Windows 10.
(I dont know why)

I am trying to use the following code now to get my result.

   With Range("K2:K" & LastrecNum)
    .Value = DateValue(Range("A2:A" & LastrecNum).Text) 'Converts Date and Time into Number of the Date
   End With

The VBA halts in this line.
Kindly give me a suggestion.
Thanks in advance.

 

[Marc L]

I can't believe it's an issue from the Windows version but just from the data !​

'Cause the formula well works only on numeric values aka real dates​

and when it not works as expected if often means the cells are not date but text …​

​

So easy to check it out with a workbook link on a files host website like Dropbox.​

 

[pmich]

Thanks, Marc L, for the immediate reply.
I thought it was because I moved to Windows 10.
I am not saying it only because of W10.
How am I to check my fault.

 

[pmich]

Thanks, Marc L, for the immediate reply.
I thought it was because I moved to Windows 10.
I am not saying that it is only because of W10.
I already received the suggestion in this forum
Converting text to date
Since there was a message that this post is old and so I posted here now.
Thanks for your suggestion to check.
How am I to check my fault?
Please guide me.
Though I have given my replies to a few of the questions raised in this forum, I a, not an expert.
Hence, need your guidance.
Thanks in advance.

 

[pmich]

=LEFT(A2,12) works. But the result is, Jun 25, 2018.
I want it to be 25-Jun-2018.
Please suggest.

 

[pmich]

In A2 I have Jun 25, 2018
I used =LEFT(A2,12) in J2.
I got Jun 25, 2018.
I did format J2 as [DATE] and chose [14-Mar-2001] [Eng (US)] and nothing changed.
But I tried using =LEFT(A2,12) in VBA Code.
I replaced

Range("K2:K" & LastrecNum).Formula = "=INT(A2)"
Range("K2:K" & LastrecNum).NumberFormat = "dd-mmm-yyyy"

with

Range("K2:K" & LastrecNum).Formula = "=LEFT(A2,12)"
Range("K2:K" & LastrecNum).NumberFormat = "dd-mmm-yyyy"

I got 25-Jun-2018.
Thanks for the support in this forum.