+ Reply to Thread
Results 1 to 3 of 3

Mixed dates that need to all be the same format

  1. #1
    Registered User
    Join Date
    12-06-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Mixed dates that need to all be the same format

    Hello

    I have a file from a database that includes birthdays and anniversaries. These are mainly in US format as far as I can tell, but I am trying to get these all in UK date format but when I try and change any of the formatting half of them don't seem to change.

    I have attached the file and would appreciate if anyone could give me any help.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Mixed dates that need to all be the same format

    welcome to the forum, aigo. Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in C1. and 12 pm in D1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day).

    so if you select the range & press CTRL + SHIFT + ~
    you should see only some of them become numbers if they are real dates. Undo it to revert to normal. if they are all numbers, you can simply right-click & format cells into whichever format you desire

    but they are not. if your dates are consistently wrong (like all in DMY or MDY, etc), select the range again (A11:A272). go to Data -> Text to Column -> Delimited -> Next -> Next -> Date: MDY -> Finish. i select MDY because the dates seem to be in MDY. to know, look at those dates with bigger than 12. months cannot possibly be bigger than 12, so it seems like the day is in the middle.

    do the same for column B. now, you can format both column's date format

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    12-06-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Mixed dates that need to all be the same format

    Thank you for that benishiryo that has worked brilliantly Cheers

+ 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. Replies: 7
    Last Post: 07-10-2013, 09:45 PM
  2. Replies: 11
    Last Post: 02-11-2013, 08:10 AM
  3. [SOLVED] macro to trim only text in mixed format spreadsheet
    By burnettec in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-23-2012, 08:46 PM
  4. Replies: 8
    Last Post: 02-21-2010, 11:33 AM
  5. Adding Column of mixed data omitting the dates
    By lpullen in forum Excel General
    Replies: 2
    Last Post: 11-17-2005, 12:45 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