Help with Excel required

Discussion in 'Strategy Development' started by m22au, Jun 1, 2003.

  1. m22au


    I have a table of historical data in columns, that is in reverse chronological order .... ie. may 2003 data starting in row 2, with feb 2003 data in row 100.

    Apart from going through line by line and rearranging myself, eg. in cell j2 having =c100

    is there any quick way to invert the table of data so that the entries are in chronological order?
  2. Try Data - Sort :D
  3. m22au


    Thanks vladiator I did forget that function.

    However in one of my data sets the date column is formatted (not my choice) as text, and the format of the date is not recognised by Excel as a date format (even as a custom date format).

    So although Data - Sort will sort {30/11, 29/11, 28/11} in the opposite order, it will sort {1/12, 30/11, 29/11} as {1/12, 29/11, 30/11}.

    Any other suggestions people?
  4. ktm


    Try reformatting the data in place using right-click, format cells and try some date options. Remember save first and undo if necessary.

    There is also a DATE function that may allow you to convert into a new cell.

    If that doesn't work, make a new column and parse the text out in pieces to make a new date. For example:

    Cell A1 (bad date) = 30/01/02 as text (DD/MM/YY).
    Cell B1 formula = MID(a1,4,2)&'/'&LEFT(a1,2)&'/'&RIGHT(a1,2)
  5. m22au


    I was able to format the "bad date" as a custom date format after all. Thanks vladiator and ktm.

    Thanks again to vladiator for reminding me about the Data - Sort thing.

  6. A different question for Excel experts. I would like to include a time function in a macro such that the macro kicks off at a certain time. For example, if time = xyz, than . . . Is there anything like this in Excel?
  7. cashonly

    cashonly Bright Trading, LLC

    Look in the help files for OnTime

  8. m22au


    Finally I did it ... after 10 separate formulae ... using functions LEFT, RIGHT, SUBSTITUTE, TRIM, VLOOKUP, CONCATENATE, VALUE

    thanks ktm for introducing me to all the text functions