Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)
Results 1 to 6 of 6

Thread: Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

    Hi,

    I have a database query that extracts a date in the following format 01.01.2019

    I can convert it to slashes using substitute but excel does not recognise the formula result as a real date..

    How can I force excel to recognise the date?

    Obvious answer is to change the DB formatting but this isnt possible so I am stuck with this problem..

    thanks

    J

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,510
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

    =substitute(a16,".","/")+0
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,320
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

    Try the DATEVALUE function:

    =DATEVALUE(SUBSTITUTE(A1,".","/"))
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,741
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

    --substitute(a1,".","/")
    Neil

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

    Both work like a charm! thanks..

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,588
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Converting date from 01.01.2019 to 01/01/2019 (So excel recognises it!)

    Instead of a formula, you could select the column and replace . with /

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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