+ Reply to Thread
Results 1 to 4 of 4

Date format error

  1. #1
    Registered User
    Join Date
    08-12-2009
    Location
    Perth, WA
    MS-Off Ver
    Excel 2003
    Posts
    93

    Date format error

    OK Im one step away from completing my spreadsheet and I have this problem which has been ongoing throughout development.

    The dates that I am pasting into my spreadsheet are formatted as dd.mm.yyyy , the macro that I run to check that the dates are correct will only work if the date is in dd/mm/yyyy format. So I do a simple Find and Replace ie "." for "/".
    The problem is after the Find and Replace some not all of the dates are rearranged as: mm/dd/yyyy. This of course throws out my data validation.

    Note, I've tried Format cells>Date but the macro still doesnt work and only recongnises "/". (Ive included the macro below in case theres a workaround there instead).

    Please Login or Register  to view this content.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Date format error

    hermithead - if you choose to cross post in another forums please have the courtesy to provide links accordingly.

    http://www.mrexcel.com/forum/showthread.php?t=451433

  3. #3
    Forum Contributor
    Join Date
    04-03-2007
    Location
    Auckland, New Zealand
    MS-Off Ver
    2007
    Posts
    137

    Re: Date format error

    Hi,

    If you don't want to use the Text to Columns functions as suggested in the other forum then you could try the below to pass over the column that contains the oddly formatted dates.

    Please Login or Register  to view this content.

    This assumes your data is one contiguous list & that the dd in the dd.mm.yyyy string has a leading zero for day numbers less than 10
    Last edited by Jbentley; 02-27-2010 at 05:40 AM. Reason: spelling; additional comment;better string conversion

  4. #4
    Registered User
    Join Date
    08-12-2009
    Location
    Perth, WA
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Date format error

    Apologies all for cross posting. I thought I was saving myself time didnt realise it could waste other peoples. Will learn from this. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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