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?
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?
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)
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.
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?
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