+ Reply to Thread
Results 1 to 3 of 3

Date format issues

  1. #1
    Registered User
    Join Date
    07-20-2010
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    2

    Question Date format issues

    I'm not the best at Excel by any manor of means. I can insert data and do the simple stuff with the formulas. I'm a web designer and devloper so I mostly use excel to arrange and structure data for upload into a database. I have a problem with this. I have 4 data columns in a spreadsheet which have date formatting on them, what I need is to take the values from these fields and combine them with data from other columns to create my import statements. When I set up the statement for a field which contains the following date: 02/02/2010 I get it showing as 40211.

    Can anyone tell me how to keep the data looking formatted but remove the actual cell formatting?

    The data I have is over 22,000 rows so I can't manually change all of this. Any help will be much appreciated.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Please help with Date issues!

    Hello,

    with a date in A1 you can use a formula like

    =text(A1,"dd/mm/yyyy")

    instead of a reference to the date in A1. This will transform the number that Excel uses to store a date into a text string.

    You can use the & operator to concatenate this text string with the values of other cells. The overall result will be a text string.

    does that help?

  3. #3
    Registered User
    Join Date
    07-20-2010
    Location
    Scotland
    MS-Off Ver
    Excel
    Posts
    2

    Re: Please help with Date issues!

    Excellent! Thankyou so much for the super fast response! Works a treat.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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