+ Reply to Thread
Results 1 to 7 of 7

Converting data general column to UK Date format in Excel 2007 - vba

  1. #1
    Registered User
    Join Date
    12-17-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    45

    Converting data general column to UK Date format in Excel 2007 - vba

    Hello There,

    I have a general column which needs to auto formatted to UK Date format mm/dd/yyyy.
    For Ex:

    General Column Required UK format Column
    (Current Data) (after calculation)
    12.07.2012 12/07/2012
    08.07.2011 08/07/2011
    02.06.2011 02/06/2011
    30.01.2011 30/01/2011
    09.09.2000 09/09/2011

    I wrote the following Macro, But it throws Run-time error 1004. Application defined or object defined error. While debugging, I see NULL value getting assigned to Application.ReplaceFormat.NumberFormat in line number 15 than getting formatted to "dd/mm/yyyy"

    Sub ReplaceGenToDateformat()

    ' - Optionally reenable improperly terminated Change Event macros

    Application.DisplayAlerts = True
    Application.EnableEvents = True 'should be part of Change Event macro
    If Application.Calculation = xlCalculationManual Then
    MsgBox "Calculation was OFF will be turned ON upon completion"
    End If
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Range("C:C").Activate
    Application.FindFormat.NumberFormat = "@"
    Application.ReplaceFormat.NumberFormat = "dd/mm/yyyy"
    Selection.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

    End Sub

    Appreciate your help.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Converting data general column to UK Date format in Excel 2007 - vba

    Have you tried Data>Text to columns...?
    If posting code please use code tags, see here.

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Converting data general column to UK Date format in Excel 2007 - vba

    Try:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    12-17-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Converting data general column to UK Date format in Excel 2007 - vba

    Yes Norie. I tried it. It did not work.

  5. #5
    Registered User
    Join Date
    12-17-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Converting data general column to UK Date format in Excel 2007 - vba

    YAY ! OllyXLS ! It works !

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,847

    Re: Converting data general column to UK Date format in Excel 2007 - vba

    With Norie's suggestion, you could have:

    Please Login or Register  to view this content.

    You could probably omit most, if not all, of the delimiters but the result depends on what you might have used previously.


    Regards, TMS

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,847

    Re: Converting data general column to UK Date format in Excel 2007 - vba

    When you use Text to Columns to convert a date, you need to select the column and choose the date format DMY.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Converting from the 1904 date format to the 1900 date format without losing data
    By Patty McJorst in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2020, 12:53 PM
  2. [SOLVED] Require vba or formula for convert general format data in date format
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-10-2013, 03:53 AM
  3. Replies: 5
    Last Post: 02-08-2013, 11:06 AM
  4. converting general cells format to text
    By junkgrrl in forum Excel General
    Replies: 3
    Last Post: 10-17-2012, 04:52 AM
  5. Converting DATE format to GENERAL format?
    By timtim in forum Excel General
    Replies: 9
    Last Post: 08-19-2011, 02:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1