PeePs

I was wondering if anyone here could write me a quick script for Excel that goes through a column of values and changes it to a date format. An example of 1 of the values is "20090807120000[0:GMT]" and they are all very similar like that. So the date for the above example would be 07/12/2009. It would also be helpful, if the script had some sort of way to determine if the value is already in date format and not doing anything with it.

Basically I think all you would have to do is assign the first 4 values to a certain variable, assign the next 2 values to a dif. variable, and then the 5th and 6th values to another variable. Then just have the script return the values in the correct order with a - or / in-between. (and if not too difficult have some sort of check to determine if the value actually needs to be changed to date form).

I'm very bad at programming and don't know any by heart so it would take forever for me to look this up and figure out how to actually accomplish this, although I have the basic idea. Anyone really good with excel / VB programming that could whip this up real fast?

Here is a list of the values so you can use to test:

20090713120000[0:GMT]
20090713120000[0:GMT]
20090821120000[0:GMT]
20090809120000[0:GMT]
20090916120000[0:GMT]
20090728120000[0:GMT]
20090830120000[0:GMT]
20090909120000[0:GMT]
20090721120000[0:GMT]
20090715120000[0:GMT]
20090904120000[0:GMT]
20090904120000[0:GMT]
20090712120000[0:GMT]
20090903120000[0:GMT]
20090806120000[0:GMT]
20090819120000[0:GMT]
20090712120000[0:GMT]
20090824120000[0:GMT]
20090807120000[0:GMT]
20090920120000[0:GMT]
20091004120000[0:GMT]
20090928120000[0:GMT]
20090830120000[0:GMT]
20090904120000[0:GMT]
20090710120000[0:GMT]
20090823120000[0:GMT]
20090823120000[0:GMT]
20090907120000[0:GMT]
20090816120000[0:GMT]
20090927120000[0:GMT]
20090712120000[0:GMT]
20090825120000[0:GMT]

DuckieHo

=if(len(a1)=21,mid(a1,5,2) & "/" & mid(a1,8,2) & "/" & left(a1,4),a1)

__________________
To answer most of your questions: (1) a fridge cannot cool a PC (2) 64-bit OS for over 3.4GB (3) If a PCIe card fits, it should work (4) Resolution, not screen size (5) If you have a question, it is not news (6) Report, not respond to Spam (7) Single-Rail/Non-Modular PSUs are not always better than Multi-Rail/Modular

PeePs

Welp, I guess I don't have a clue what I'm doing here. Would I input that code in the code window for the sheet or in a cell adjacent to the values I want converted to date form?

DuckieHo

In any cell in the first row... it references Cell A1.

__________________
To answer most of your questions: (1) a fridge cannot cool a PC (2) 64-bit OS for over 3.4GB (3) If a PCIe card fits, it should work (4) Resolution, not screen size (5) If you have a question, it is not news (6) Report, not respond to Spam (7) Single-Rail/Non-Modular PSUs are not always better than Multi-Rail/Modular

PeePs

Works like charm and exactly how I wanted it. Did notice that it was taking the wrong numbers for the day part, but luckily I was barely competent enough to correct it myself . Thanks a bunch I appreciate it!

=if(len(a1)=21,mid(a1,5,2) & "/" & mid(a1,8,2) & "/" & left(a1,4),a1)

=if(len(a1)=21,mid(a1,5,2) & "/" & mid(a1,7,2) & "/" & left(a1,4),a1)