+ Post New Thread
Results 1 to 4 of 4
Scripts Thread, excel date field to general numbers in Coding and Web Development; Does anyone know of a way to get a column of dates converted automatically, using a formula, into standard numbers ...
  1. #1

    Join Date
    Oct 2007
    Location
    Cambridgeshire, UK
    Posts
    306
    Thank Post
    58
    Thanked 23 Times in 20 Posts
    Rep Power
    17

    excel date field to general numbers

    Does anyone know of a way to get a column of dates converted automatically, using a formula, into standard numbers but preserving the date numbers - sorry, sounds a tad confusing!

    e.g. date shown: 21 May 2009 or 21/05/09 etc CONVERTED TO: 210509

    Thanks,

    Dave.

  2. #2
    leco's Avatar
    Join Date
    Nov 2006
    Location
    West Yorkshire
    Posts
    2,026
    Thank Post
    595
    Thanked 125 Times in 119 Posts
    Rep Power
    41
    You could use CONCATENATE
    Date is in cell A1
    Formula: =CONCATENATE(DAY(A1),MONTH(A1),YEAR(A1))

    That works but strips out leading zeros - 03/08/09 would become 382009. Might not be what you want.

  3. #3

    maniac's Avatar
    Join Date
    Feb 2007
    Location
    Kent
    Posts
    3,037
    Thank Post
    209
    Thanked 425 Times in 306 Posts
    Rep Power
    144
    Does it have to be a formula, as the easiest way is to change the formatting of the cell to custom and type ddmmyy into the type box.

    I've just used this to generate my new users passwords, as we're using DOB this year for the new Y7's as their initial password in that format.

    Mike.

  4. #4

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,154
    Thank Post
    114
    Thanked 527 Times in 450 Posts
    Blog Entries
    2
    Rep Power
    123
    Just taking @leco's a bit further, if you need the leading zeros then:
    Code:
    =TEXT(DAY(A1),"00") & TEXT(MONTH(A1),"00") & TEXT(YEAR(A1),"0000")
    should do what you need. "&" is quicker to type than "concatenate" but you can also do:
    Code:
    =CONCATENATE(TEXT(DAY(A1),"00"),TEXT(MONTH(A1),"00"),TEXT(YEAR(A1),"0000"))

  5. Thanks to srochford from:

    leco (4th August 2009)

SHARE:
+ Post New Thread

Similar Threads

  1. HP - Get up to date for out of date PC trade-in
    By WStore_Dan in forum Our Advertisers
    Replies: 0
    Last Post: 19th May 2009, 09:46 AM
  2. Microsoft Access Help! Date Field
    By Pyroman in forum How do you do....it?
    Replies: 1
    Last Post: 2nd May 2008, 03:19 PM
  3. Replies: 5
    Last Post: 4th June 2007, 09:10 PM
  4. Useful contact numbers when contacting support numbers
    By ranj in forum Comments and Suggestions
    Replies: 10
    Last Post: 22nd December 2006, 08:21 AM
  5. Technician Numbers
    By phil_thebeans in forum Wireless Networks
    Replies: 33
    Last Post: 3rd November 2006, 12:39 AM

Thread Information

Users Browsing this Thread

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •