How to extract '27/08/2015' from '27/08/2015 14:46:17'

[ThrottleWorks]

Hi,

I have value as '27/08/2015 14:46:17' in a column.

I am trying to convert it to 27/08/2015 however after applying format the values are changing.

For example '05/09/2015 14:46:17' is getting changed to '09/05/2015 14:46:17'.

Can anyone please help me this.

If we manually delimit the value it's working but if we try the same with macro it does not work.

That is why I tried deriving year, month and day in separate column.

Till " Sht1.Range("c2").Formula = "=D2&""/""&E2&""/""&F2"" this line it's working nice but when I apply "Columns("C:C").NumberFormat = "m/d/yyyy" format then somehow month changes to day and day changes to month.

Sht1.Range("d2").Formula = "=DAY(B2)"
    Sht1.Range("d2: d" & TempLr).Formula = Sht1.Range("d2").Formula
    Sht1.Range("d2: d" & TempLr).Value = Sht1.Range("d2: d" & TempLr).Value
 
    Sht1.Range("e2").Formula = "=MONTH(B2)"
    Sht1.Range("e2: e" & TempLr).Formula = Sht1.Range("e2").Formula
    Sht1.Range("e2: e" & TempLr).Value = Sht1.Range("e2: e" & TempLr).Value
 
    Sht1.Range("f2").Formula = "=YEAR(B2)"
    Sht1.Range("f2: f" & TempLr).Formula = Sht1.Range("f2").Formula
    Sht1.Range("f2: f" & TempLr).Value = Sht1.Range("f2: f" & TempLr).Value
 
    Columns("d:f").NumberFormat = "0"
 
    Sht1.Range("c2").Formula = "=D2&""/""&E2&""/""&F2"
    Sht1.Range("c2: c" & TempLr).Formula = Sht1.Range("c2").Formula
    Sht1.Range("c2: c" & TempLr).Value = Sht1.Range("c2: c" & TempLr).Value
    Columns("C:C").NumberFormat = "m/d/yyyy"

 

[Deepak]

Hi,

If the time stamp is numeric as u have then simply use INT(A1) to extract date from timestamp.

for a range by VBA

set r  = [a1:A10]

r = "=INT(A1)"

r.value = r.value

r.NumberFormat = "dd/mm/yyyy"

 

[Marc L]

Hi !

What a real date in Excel ? (real means not a text)

Just a number ! The integer part is the date, the decimal the time …

So if B2 cell is a real date, C2 yet formated as date :​

Sub Demo()
    [C2].Value = Fix([B2].Value2)
End Sub

Edit : Deepak was fastest !
​

 

[ThrottleWorks]

Hi @Deepak Sir, thanks a lot for the help. But I am getting a bug while running this macro. May be I am missing something.

Could you please help if possible.

Kindly refer attached file for your reference.

 

[ThrottleWorks]

Hi @Marc L , thanks a lot for the help. I tried your code however it gives me result as '27/09/2015 00:00:00'.

 

[Deepak]

Hi @Deepak Sir, thanks a lot for the help. But I am getting a bug while running this macro. May be I am missing something.

Could you please help if possible.

Kindly refer attached file for your reference.

Neither u changed the range nor declare the variable.

Sub test()
Dim r As Range
Set r = [b1:b10]
r = "=INT(A1)"
r.Value = r.Value
r.NumberFormat = "dd/mm/yyyy"
End Sub

 

[Deepak]

Another is!

With [b1:b10]
    .Formula = "=INT(A1)"
    .Value = .Value
    .NumberFormat = "dd/mm/yyyy"
End With

 

[Deepak]

Hi @Marc L , thanks a lot for the help. I tried your code however it gives me result as '27/09/2015 00:00:00'.

In order to marc approach..

You need to loop/array!

Just copy/paste doesn't work as sometimes we show the path to walk on your own foot.

 

[ThrottleWorks]

Hi @Deepak Sir, my mistake. Will re-check again and revert with details.

To be honest, I was a bit apprehensive about playing with your code. So as you said I just 'Copy and Paste'

Also, if we try the formula manually in excel sheet, I guess it should return a result but it's not.

I tried "=INT(A1)" but it returned #VALUE!

Good night.

 

[ThrottleWorks]

Hi @Deepak Sir and @Marc L

Int(A1) formula is working with sample values but not working with original values.

Please check attached file for your reference.

May be some formatting issues with values in original file. Will check and revert.

Thanks a lot for the help.

 

[Deepak]

I am reading on mobile and xlsb is not supported. so pls upload xlsx and code in body

 

[ThrottleWorks]

Hi @ deepak Sir, please take your own time, not urgent.

Please check the attached file when you get time. Good night.

I tried below mentioned code and also tried entering the formula manually.

With [b1:b10]
    .Formula = "=INT(A1)"
    .Value = .Value
    .NumberFormat = "dd/mm/yyyy"
End With

For your quick glance, this is how the results were populated when I entered the formula manually.

27/08/2015 14:46:17 #VALUE!
9/10/15 12:00 AM 9/10/2015
9/10/15 0:00 9/10/2015
27/08/2015 14:46:17 #VALUE!
Thursday, September 10, 2015 9/10/2015
9/10/15 0:00 9/10/2015
27/08/2015 14:46:17 #VALUE!
9/10/15 12:00 AM 9/10/2015
9/10/15 0:00 9/10/2015

 

[Deepak]

Its due to date is either string or different with system date format.
Have u imported the data then would u pls share it.

I will check on having access to pc.

 

[Marc L]

#VALUE error means source data is not numeric !
Not a valid date but text as I yet wrote …

According to your last attachment column A :​

Sub Demo()
    With Cells(1).CurrentRegion.Columns(1)
                  .Formula = .Value
        With .Offset(, 1)
             .NumberFormat = "m/d/yyyy"
                  .Formula = "=INT(A1)"
                  .Formula = .Value
        End With
    End With
End Sub

Works well on my side maybe 'cause some column A data are updated
at workbook opening in my local Excel version (not US) …

In fact I just need this :​

Sub Demo()
    With Cells(1).CurrentRegion.Columns(2)
        .NumberFormat = "m/d/yyyy"
             .Formula = "=INT(A1)"
             .Formula = .Value
    End With
End Sub

Like Deepak's code …​

 

[ThrottleWorks]

Hi @Deepak Sir and @Marc L ,

Kindly see the below mentioned code. It seems to be working.
Values are in number format (checked with =IsNumber).

Thanks a lot for your help and valuable time. Have a nice day ahead.

Sht.Range("c2").Formula = "=INT(B2)"
Sht.Range("c2: c" & TempLr).Formula = Sht.Range("c2").Formula
Sht.Range("c2: c" & TempLr).Value = Sht.Range("c2: c" & TempLr).Value
Columns("C:C").NumberFormat = "m/d/yyyy"