Format of date changing
Format of date changing
Format of date changing
Format of date changing
Format of date changing
Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing
Format of date changing Format of date changing
Format of date changing
Go Back  Xtreme Visual Basic Talk > > > > Format of date changing


Reply
 
Thread Tools Display Modes
  #1  
Old 10-19-2016, 11:25 AM
bornwarrior bornwarrior is offline
Newcomer
 
Join Date: Apr 2012
Posts: 2
Default Format of date changing


Hi,

I have a column where the format is date and time so for example

dd/mm/yyyy/ hh/mm/ss

As you can see the date and time field are separated by a space " " I use the following coding to split this column and it works as it should.

Code:
Sub SplitColumns()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim tmpArray() As String

    '~~> This is the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For i = 2 To LastRow
            If InStr(1, .Range("B" & i).Value, " ") Then
                tmpArray = Split(.Range("B" & i).Value, " ")
                .Range("C" & i).Value = tmpArray(0)
                .Range("D" & i).Value = tmpArray(1)
            End If
        Next i
    End With
End Sub
The only problem I have is that now the date that is displayed in column C is in format of
mm/dd/yyyy when i would like it to be dd/mm/yyyy

When i highlight the column c and go into change format its already selected as English U.K so not sure why the format of the date gets changed to American?

Is there another way around this? maybe swap dd/ with /mm ? Not entirely sure so any help is appreciated greatly.
Reply With Quote
  #2  
Old 10-19-2016, 08:00 PM
Kluz's Avatar
KluzFormat of date changing Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

Try re-formatting the "C" & i range to "dd/mm/yyyy" before you put the tmpArray(0) value in it. If it still switches month and day, you may need to further split the tmpArray(0) value on "/" and re-concatinate switching the location of day and month values so when Excel switches them back, they are again correct.
__________________
No the other right mouse click
Reply With Quote
  #3  
Old 10-21-2016, 09:09 AM
MPiFormat of date changing MPi is offline
Senior Contributor

Forum Leader
* Expert *
 
Join Date: Dec 2001
Location: Quebec
Posts: 1,000
Default

If you change it like that, does it work ?
Code:
Range("C" & I) = DateSerial(Year(Range("B" & I)), Month(Range("B" & I)), Day(Range("B" & I)))
__________________
MPi²
Reply With Quote
  #4  
Old 02-10-2017, 11:20 AM
Daigon Ali Daigon Ali is offline
Regular
 
Join Date: Aug 2014
Location: London
Posts: 59
Default Formula or vba

Here are two ways:

First with formula:
Get the date in column C with
Code:
=INT(B1)
Get the time element in column D with
Code:
=MOD(B1,1)
To do this in vba, adjust your routine as follows:

Code:
Sub SplitColumns()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim tmpArray() As String

    '~~> This is the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

        For i = 2 To LastRow
            If InStr(1, .Range("B" & i).Value, " ") Then
                .Range("C" & i).Value = Int(.Range("B" & i).Value)
                .Range("D" & i).Value = .Range("B" & i).Value - 1 * Int(1 / .Range("B" & i).Value)
                'tmpArray = Split("'" & .Range("B" & i).value, " ")
                '.Range("C" & i).Value = tmpArray(0)
                '.Range("D" & i).Value = tmpArray(1)
            End If
        Next i
    End With
End Sub
Reply With Quote
Reply

Tags
format, date, i.value, column, dim, lastrow, tmparray, .rangeb, dd/mm/yyyy, time, change, english, selected, mm/dd/yyyy, highlight, u.k, /mm, swap, greatly, appreciated, changing, changed, american, displayed, field


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Format of date changing
Format of date changing
Format of date changing Format of date changing
Format of date changing
Format of date changing
Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing Format of date changing
Format of date changing
Format of date changing
 
Format of date changing
Format of date changing
 
-->