+ Reply to Thread
Results 1 to 4 of 4

Dates were written incorrectly

Hybrid View

wedzmer Dates were written incorrectly 07-07-2014, 01:30 PM
Miraun Re: Dates were written... 07-07-2014, 01:35 PM
wedzmer Re: Dates were written... 07-07-2014, 02:04 PM
flextera Re: Dates were written... 07-07-2014, 02:10 PM
  1. #1
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Dates were written incorrectly

    I have a problem because I was sent an information sheet via excel to compute the ages of students. However, the excel file sent to me wrote the date of birth in a way excel could not recognize as a date. For example:

    the students birthday was indicated with: 07-05-2003 (which mean June 5, 2003).

    Excel won't recognize it because it has to be encoded as 07/05/2003 (or something similar) and when I use the Datedif function for another cell, it won't calculate because it doesn't recognize that cell as a date.

    The problem is, all the cells from h5:h30 were encoded in that way... Is there a formula to convert these cells into a date when I use it as a reference into a separate sheet and computed their ages there with the datedif function?
    Last edited by wedzmer; 07-07-2014 at 02:39 PM. Reason: Solved ^_^

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Dates were written incorrectly

    Not incredibly quickly, but absolutely.

    Set up four helper columns:
    =LEFT(A1,2)
    =MID(A1,4,2)
    =RIGHT(A1,4)
    =DATE(D1,C1,B1)

    You can then copy the DATE responses, paste special, paste values, and remove all the previous helpers, and you're left with just perfectly formatted dates.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Forum Contributor wedzmer's Avatar
    Join Date
    06-13-2014
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    422

    Re: Dates were written incorrectly

    this one's solved.. thank!

  4. #4
    Registered User
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    72

    Re: Dates were written incorrectly

    Wedzmer. Please mark this post as "Solved"

+ 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. PivotChart Incorrectly Plotting Dates as Categories
    By tinman484 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-04-2013, 11:57 AM
  2. dates imported incorrectly
    By hclark in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2012, 11:59 AM
  3. VBA being written to another work book assigning macro incorrectly
    By Michael Wise in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2010, 08:33 AM
  4. Formula/Macro to separate dates (written in text strings)
    By jologstol in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-09-2008, 10:42 AM
  5. Dates link incorrectly
    By inoexcel in forum Excel General
    Replies: 0
    Last Post: 02-28-2008, 09:54 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