+ Reply to Thread
Results 1 to 7 of 7

(Solved) Converting a date from 00/0000 to 00/00/0000

  1. #1
    Registered User
    Join Date
    12-11-2019
    Location
    Minnesota
    MS-Off Ver
    Microsoft Office 365 MSO
    Posts
    8

    (Solved) Converting a date from 00/0000 to 00/00/0000

    Hello,

    I am working on setting up a new date converter as were switching software and need to transfer numerous data sets. I am currently trying to convert a date from 00/0000 (which is currently acting as a number) to 00/00/0000. Any ideas? Thanks in advance for the help!
    Last edited by RigbyDigby; 12-11-2019 at 07:17 PM. Reason: (Solved)

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,329

    Re: Converting a date from 00/0000 to 00/00/0000

    so, going from this... 00/0000 to this 00/00/0000, how is someone to know what those numbers represent? 00/0000
    can you give an actual example of each number and are we to assume it will all be in excel?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    12-11-2019
    Location
    Minnesota
    MS-Off Ver
    Microsoft Office 365 MSO
    Posts
    8

    Re: Converting a date from 00/0000 to 00/00/0000

    I appreciate your comment, and apologize for the lack of context as it is my first time posting. I am using Microsoft Excel and for example I am trying to convert 12/2019 (today's date without the specific day) to 12/01/2019. So currently the 12/2019 is defined as a number, and I would like to convert a specific date from the original context of 00/0000 to 00/01/0000. Thanks again for the feedback!

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,329

    Re: Converting a date from 00/0000 to 00/00/0000

    this would be one way... =IF(LEN(A1)=7,DATEVALUE(LEFT(A1,2)&"/01/"&RIGHT(A1,4)),DATEVALUE(LEFT(A1,1)&"/01/"&RIGHT(A1,4)))
    BUT, I don't know if when you have 9/2019 that it isn't 09/2019 instead of 9/2019. So that is why I added the LEN formula into the datevalue formula.

  5. #5
    Registered User
    Join Date
    12-11-2019
    Location
    Minnesota
    MS-Off Ver
    Microsoft Office 365 MSO
    Posts
    8

    Re: Converting a date from 00/0000 to 00/00/0000

    I have dropped a test excel in the comments here. The data set I am working with does not include a zero before a one as such (1/2019) - Which is the way its currently formatted. Although if the date was October of 2019 it would show up with 10/2019.
    Last edited by RigbyDigby; 12-11-2019 at 07:17 PM.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,497

    Re: (Solved) Converting a date from 00/0000 to 00/00/0000

    =date(right(a2,4),--(left(a2,len(a2)-5)),1)
    Ben Van Johnson

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,025

    Re: Converting a date from 00/0000 to 00/00/0000

    Quote Originally Posted by RigbyDigby View Post
    So currently the 12/2019 is defined as a number
    What do you mean by "is defined as a number"? What number? Is it 43800? If so, it is already the date you want and all you need to do is format the cell using the date format of your choice.

+ 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. [SOLVED] Formula to restrict formatting date in Excel to 00/00/0000
    By Hedy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-19-2018, 09:41 AM
  2. Replies: 8
    Last Post: 05-15-2017, 05:54 AM
  3. MAX MIN formulas returning value 0.0000, why? Please Help me
    By Swulvi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2016, 05:23 PM
  4. Save file suffix by date mmddyy & time 0000
    By tek9step in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2010, 02:12 PM
  5. Achieving 0000 Format
    By Chris424 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2008, 09:53 AM
  6. [SOLVED] Phone number format from 000.000.0000 to (000)000-0000
    By Janice in forum Excel General
    Replies: 4
    Last Post: 06-23-2005, 08:05 PM

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