1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Messesd up dates

Discussion in 'Business Applications' started by cherry pie, Nov 10, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. cherry pie

    cherry pie Thread Starter

    Joined:
    Jun 4, 2007
    Messages:
    345
    Got an excell sheet with about 800 rows. It has 3 columns for dates.

    The original creator of the sheet entered the dates in the form (dd/mm/yyyy) but kept the formatting of the cells as (general) so it wasn't recognized as a date at all.

    Now I'm trying to organize it a little bit and convert those cells to date. The default of excel is to recognize mm/dd/yyyy so when I change the formatting something like 1/11/2005 is recognized as January 11th 2005 (instead of November 1st 2005 which is what I want) and 13/11/2005 is not recognized (Although i want it to be November 13th)

    I tried to format as a custom date and typed (dd/mm/yyyy) but what it seems is doing is that it's reading in it's original format then converting to the one I choose.

    I'm can deal with it as text and use the LEFT & RIGHT functions and concatinate to re-arrange the whole thing then convert it. But was wondering if there's a way to change the way of reading it as date? Thx
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi cherry pie,

    Your sheet is acting normally. The dates entered are in fact Excel recognized dates, they were just entered incorrectly. The only way for you to change this is to go into your regional settings and change the default date format. But even then you're still going to have values in Excel that are entered wrongly into Excel. What you need to do is identify which cells have this and then use an extra column to coerce the values back into the way you want (switching the day and the month).

    Here is an example...

    Code:
    'Assumes dates are in column A, formula will be in col B
    =DATE(YEAR(A1),DAY(A1),MONTH(A1))
    Make sure you note one thing from above!!! Notice the Day and Month syntaxes? Yes, they are backwards. That is because we are saying the Day was actually put in the month, and the Month was put in the day - aka backwards.

    Also, make sure that you first format all of these cells the same, so you know what you're looking at.

    HTH
     
  3. TheOutcaste

    TheOutcaste

    Joined:
    Aug 7, 2007
    Messages:
    9,028
    Morning Firefytr and Cherry Pie

    Zack, If the dates were actually entered as text the formula won't work, as year, day, and month need numeric data. I just tried it with 13/11/2005 entered as text (type '13/11/2005 to force entry as text or format cell as text, then type the date, then change format back to general)
    Doing this, your formula gives the #VALUE! error, wrong data type. Changing regional settings didn't make a difference either. However, I did come up with a way that should work.

    This should convert all of the dates for you Cherry Pie. Be sure to backup the original file first, or copy all the dates to a new book to work with.
    I'll call the book with your dates Book1. We will new a new blank book I'll call Book2
    First, change your regional settings to English (United Kingdom) (Control Panel | Regional Settings)
    Assuming your dates are all in column A, format column B as Date with locale as English (United Kingdom) and Format column C as Date with locale English (United States)
    In column B enter =DATEVALUE(A1), in column C enter =B1, then copy the formula down for all rows you need.
    In Book2 format column A as Date with locale English (United States)
    Copy all rows from Column C in Book1, then paste special | Values into Column A of Book2
    Change your regional settings back to your original setting (English (United States) I'm guessing) Note that you will get #VALUE! in columns B and C for any date like 13/11/2005 where the 1st 2 digits are >12 this is to be expected
    In Book 1, change the format for column A from General to Date with Locale as English (United States)
    Copy Column A from Book 2, and paste special | Values into Book1 Column A

    The dates in column A should now all be in mm/dd/yyyy format and entered as date serial numbers, not text.

    The Language bar may appear on your taskbar, if it does, just right click and close.

    HTH

    Jerry
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi Jerry, I had assumed that the values were date entered, which is what it sounded like. I could be wrong. (It's happened a time or two before..) :rolleyes:
     
  5. cherry pie

    cherry pie Thread Starter

    Joined:
    Jun 4, 2007
    Messages:
    345
    Thanks a lot for your replies,

    Zack, I tried your method it worked great. I changed only the date format in the regional settings (instead of converting to UK) then used your formula to convert the general to date, copy > paste special > values and used a generic date format. Switched the regional settings back and got the results I expected (y) amazing

    Then I tried Jerry's way, actually I just formatted the column as date UK, used the date value formula and it gave the required result. All I did is copy > Paste > Values then chose a generic date format and got what I wanted... (y) amazing as well...

    And for your information my formula worked as well :D
    Then format as date (y) most amazing :p

    Since I had 3 columns of dates I used one method for each one and they're all working good. Appreciate a lot your help and marking this as solved
     
  6. cherry pie

    cherry pie Thread Starter

    Joined:
    Jun 4, 2007
    Messages:
    345
    They were entered as text not date that's where the problem was generated but the regional setting change made the DATE formula work ;)
     
  7. TheOutcaste

    TheOutcaste

    Joined:
    Aug 7, 2007
    Messages:
    9,028
    Opps, i was wrong Zack, date does work with text in the cell -- I did a typo when I tested it with plain text (I pointed to wrong cell). When I point to the correct cell:eek: , it works fine, just need the regional settings set to match the format

    another proof for my sig:eek:
     
  8. cherry pie

    cherry pie Thread Starter

    Joined:
    Jun 4, 2007
    Messages:
    345
    :D
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/650087

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice