Splitting a cell value and define formatting

[skull_eagle]

Hi,

Column P is in "dd/mm/yyy hh:mm" format, I need to split this into 2 columns one with the date the other with the time, using the below array splits the data but turns the values into text (even though the format is defined) which are useless to run formulas against.

Any assistance would be greatly appreciated

Range("Q1").Select
[color=darkblue]Dim[/color] b [color=darkblue]As[/color] [color=darkblue]Long[/color]
        Row = b
        Column = 16
[color=darkblue]Do[/color]
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""dd/mm/yyyy"")"
    ActiveCell.Offset(1, 0).Select
    b = b + 1
[color=darkblue]Loop[/color] [color=darkblue]Until[/color] Cells(b, Column).Value = ""
    
       
    Range("R1").Select
[color=darkblue]Dim[/color] c [color=darkblue]As[/color] [color=darkblue]Long[/color]
        Row = c
        Column = 16
[color=darkblue]Do[/color]
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-2],""hh:mm"")"
    ActiveCell.Offset(1, 0).Select
    c = c + 1
[color=darkblue]Loop[/color] [color=darkblue]Until[/color] Cells(c, Column).Value = ""

Thanks

 

[AlphaFrog]

    Dim Lastrow As Long
    
    Lastrow = Range("P" & Rows.Count).End(xlUp).Row
    
    With Range("Q1:Q" & Lastrow)
        .FormulaR1C1 = "=INT(RC[-1])"
        .NumberFormat = "dd/mm/yyyy"
    End With
    
    With Range("R1:R" & Lastrow)
        .FormulaR1C1 = "=MOD(RC[-2],1)"
        .NumberFormat = "hh:mm"
    End With

 

[Peter_SSs]

Do you really need a macro for this?

If not ..
Q1 Formula: =INT(P1)
and format Q1 as a Date ("dd/mm/yyyy")
R1 Formula: =MOD(P1,1)
and format R1 as Custom time ("hh:mm")
Then copy the formulas down

If you do need a macro ..
Try this in a copy of your workbook. (This just does the same as the method above but by macro.)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> SplitDateTime()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("Q1:Q" & Range("P" & Rows.Count).End(xlUp).Row)<br>        .Formula = "=INT(P1)"<br>        .NumberFormat = "dd/mm/yyyy"<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 1)<br>            .Formula = "=MOD(P1,1)"<br>            .NumberFormat = "hh:mm"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[skull_eagle]

Thank you both for the fast reply.

I have an issue with doing it this way, with the hh:mm side of things excel automatically places 00/01/1900 as a hidden date in front of this time.

This is what lead me to use the 'text' function originally to try and get rid of this date as it affects my formula.


Thanks

 

[Peter_SSs]

This is what lead me to use the 'text' function originally to try and get rid of this date as it affects my formula.

What formula? Tell us more about what you have and what you are trying to achieve.

 

[skull_eagle]

Hi Peter,

Thank you for your help and taking an interest.

Just after writing my last message I had a bit of an apiffany.

I was writing my code like this:
If ActiveCell.Offset(0, -12) > TimeSerial(18, 0, 0) Then

Changing this to use literal values makes the date redundant and appears to have solve the problem:
If ActiveCell.Offset(0, -12) > #6:00:00 PM# Then

I'm very satisfied now, thanks again for your help.