VBA Separating Date & Time

  • New to VBA and I need some help with a step in this reorganizing macro. I have an indefinite amount of Date & Time entries in Column B of my excel in the format: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 216"]Monday, August 19, 2019 17:50:59[/TD]

    [/tr]


    [/TABLE]

    For every used cell in Column B, starting in row 4, I need to cut and move the Time to the adjacent cell in Column C and reformat the Date in Column B to mm/dd/yy


    Any and all help is appreciated, Thank You

  • Thank you so much but I'm getting a Type Mismatch error as soon as it hits this line:
    Range("C" & i) = Range("B" & i) - Int(Range("B" & i))


    Using the view/watch option I can see that the types don't match up but I'm still to new to this to understand how to correct it.
    Watch : : Range("B" & i) - Int(Range("B" & i)) : <Type mismatch> : Variant/Integer
    Watch : : Range("C" & i) : : Object/Range

  • I am guessing now that your data in column B is not really in Date format but rather in Text Format. But without actually seeing your data it is difficult to discern. Suggest you upload some sample data for analysis. ie. Attach a small file of representative data.

  • Hello,


    Alan is right ...


    For sure, you are using Text ... and not Numbers .... !!!


    Your initial message is misleading :

    Quote

    I have an indefinite amount of Date & Time entries


    You do need beforehand to convert your Text to Actual Dates ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • [USER="108665"]AlanSidman[/USER] Thanks a lot for your Like ... :wink:


    Building upon Alan's macro ... below is the combined version :



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Once you have tested the macro ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello again,


    You can test following :



    Hope this is in line with your request

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Once you have tested the revised macro ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello,


    Sorry for the copy mistake ...


    Below is the correction :


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Glad to hear it is all sorted out ...:wink:


    Thanks for your Thanks AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!