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: Date format - Excel 2007

Discussion in 'Business Applications' started by RideJunkie, Sep 27, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. RideJunkie

    RideJunkie Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    12
    I have a user who is trying to update an Excel spreadsheet somebody sent them. In one cell, there is a reference to the date located in another cell. However, the way it is formatted, the date in the cell displays as just a number instead of the date ("September 22, 2010" displays in the 2nd cell as "40443".

    In the second cell, there is some text along with the formula referencing the original cell containing the date. In the formula bar, it looks like this: ...Los Angeles, California 90013, on "&AP29&"

    Yet in the spreadsheet, it displays like this: ...Los Angeles, California 90013, on 40443

    In the original/refenced cell (AP29), the text entered into the cell is 9/22/2010, and is formatted to display as September 22, 2010.

    Any idea how to correct the formula to display the date correctly?

    Thanks a bunch.
     
  2. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    Hi Traci. :)

    It's because the underlying value of the date is a number, which is how Excel interprets it no matter how it's formatted. To change it to display the date, you have to tell it it's a date within the formula. Try this:

    ...Los Angeles, California 90013, on "&TEXT(AP29,"m/dd/yy")&"

    Without seeing the whole formula in the second cell I'm not quite sure about the positioning of the ampersands and quotes, but play around with it a little. Let me know if it doesn't work.
     
  3. RideJunkie

    RideJunkie Thread Starter

    Joined:
    Nov 19, 2009
    Messages:
    12
    That worked! Thanks for your help Wendy. ;)
     
  4. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    Just as an FYI, if you need it to still say "September 22, 2010" instead of "9/22/10" use "mmmm dd, yyyy" instead of "m/dd/yy". Glad it worked!
     
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!

Loading...
Thread Status:
Not open for further replies.

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