Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Thread: Convert Date Seperated With Dot

  1. #1
    Join Date
    11th November 2006
    Location
    Amsterdam
    Posts
    10

    Convert Date Seperated With Dot

    Hi,

    I have two ways to export Forex data to Excel for analysis by another trading program. This exported data appears either as mm/dd/yyyy (in which case the cell alignment is inconsistent for some reason) or yyyy.mm.dd and I need to convert it to either yyyymmdd or dd/mm/yy.

    I have tried the Data>Text to Columns Wizard without success and I have read through various other threads here, but they seem to all want to convert "in the other direction" !

    I would be really grateful if someone could please help me make this conversion.

    J.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Convert Date To Yyyymmdd Or Dd/mm/yy

    If they are not true Excel dates then we need a sample of how the date ends up in your Worksheet.

  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Convert Date Format

    Are these real dates? (If you apply the formula =VALUE(A1) to one of your date cells, does it give you a number?) If so, you can just use Format, Cells, either picking a Date format or using Custom formatting.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    11th November 2006
    Location
    Amsterdam
    Posts
    10

    Re: Convert Date Format

    Wow - thanks for the almost instantaneous response !

    I have greatly reduced the file sizes and attach two examples.

    I am about to read the Custom Format link.... but putting on the coffee machine first, as I will need to read it V_E_R_Y S_L_O_W_L_Y

    J.

    Edit - hmm upload didn't work... will try again (file sizes only about 4kb)...
    Last edited by jtrade; November 17th, 2006 at 18:20.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Location
    Berkshire UK
    Posts
    426

    Re: Convert Date Format

    Hi

    this will convert either format to dd/mm/yy

    =IF(MID(A1,6,1)="/",MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2),RIGHT(A1,2)&"/"&MID(A1,6,2)&"/"&MID(A1,3,2))

    Robert

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th November 2006
    Location
    Amsterdam
    Posts
    10

    Re: Convert Date Format

    Screenshot, about as tiny as I could make it....
    Attached Images Attached Images

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    11th November 2006
    Location
    Amsterdam
    Posts
    10

    Re: Convert Date Format

    Quote Originally Posted by Robert B
    Hi

    this will convert either format to dd/mm/yy

    =IF(MID(A1,6,1)="/",MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2),RIGHT(A1,2)&"/"&MID(A1,6,2)&"/"&MID(A1,3,2))

    Robert
    Thank-you for this formula, Robert.

    I regret I do not seem to be able to apply it : if I select column A, paste the formula into the formula bar, I get a circular reference error. So then I referred back to these two pages :

    http://www.ozgrid.com/Excel/convert-...te-formats.htm
    http://www.ozgrid.com/News/excel-dat...-negatives.htm

    ...and successfully increased my confusion !

    Sorry about this, but could you please spell it out to me step by step ?

    J.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Convert Date Format

    Add this formula to any cell except Column A

    =DATE(LEFT(A1,5),MID(A1,6,2),RIGHT(A1,2))

    Copy down as far as needed. Now copy the results and select A1 of your date column and Edit>Paste Special - Values.

  10. #10
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Convert Date Format

    Hi,

    =SUBSTITUTE(A1,".","/")+0

    format the cell as required.

    HTH

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Text Date To True Date
    By shakerley1 in forum Excel General
    Replies: 1
    Last Post: January 10th, 2008, 09:22
  2. Convert Solar Date To Gregorian Date
    By cs2007 in forum Excel General
    Replies: 7
    Last Post: December 10th, 2007, 23:10
  3. Convert Number Representing Date to Real Date
    By Dauntless1 in forum Excel General
    Replies: 3
    Last Post: October 4th, 2007, 03:39
  4. Convert date & time to just date
    By sjobevakare in forum Excel General
    Replies: 16
    Last Post: March 22nd, 2006, 00:42
  5. Convert text od Date into Date field
    By tqv1969 in forum Excel General
    Replies: 8
    Last Post: March 17th, 2006, 00:21

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
  •  
porno