F2 + ENTER to format cells

[sivanvega]

Hi!

I have some imported data that Excel does not recognize as date or time so I have to edit each cell pressing F2 and the enter to "convert" the value.
I have about 120.000 records and the macro takes long time to do the job.

For i = 1 To 60000
Cells(i, 1).Formula = Cells(i, 1).Formula
Cells(i, 2).Formula = Cells(i, 2).Formula
Next

Is there a way to to this without blocking my pc?

Thanks very much.

Sergio.

[contaminated]

Actually I'd do that using simple excel formulas lke DATEVALUE and TIMEVALUE
Put in C2

=DATEVALUE(A2)

Put in D2

=TIMEVALUE(B2)

Copy both formulas down as far as u need. Than copy your output, select entire column A and B rght click > Paste Special > Value > OK

[DonkeyOte]

Another option would be...

-- Enter 1 into C1
-- Copy C1
-- Highlight values in A2:B61751
-- Edit -> Paste Special: Operation: Multiply

In terms of a VBA routine - you could use Text to Columns, the above etc or use Evaluate eg:

Please Login or Register  to view this content.

[sivanvega]

Thanks DonkeyOte, the VBA code is fast!!
Could you explain me how it works? Is the first time I see the evaluate function.

Sergio.

[romperstomper]

You could also use:

Please Login or Register  to view this content.

[DonkeyOte]

Could you explain me how it works? Is the first time I see the evaluate function.

As romperstomper has already proven - the Evaluate method is overkill in this instance so don't use it (apologies on my part).

However, to answer your question... in this context easiest perhaps to think of Evaluate working along the lines of each value in the range being physically re-entered (akin to F2 - ENTER).

Like F2/Enter when the value is re-entered XL interprets and formats accordingly ie the dates will be seen as dates (integer) and time values as time (decimal).

Evaluate is a very powerful function in VBA - the VBE Help File on the method isn't great - there's a discussion on it's usage at OzGrid: http://www.ozgrid.com/forum/showthread.php?t=52372

Evaluate is more commonly seen in conjunction with SUMPRODUCT however I often use it to avoid iteration - a tip I picked up from Bill Jelen's book "Excel Gurus Gone Wild"

[mikerickson]

Have you tried using Text to Columns, with a ~ delimiter and setting General as the format in the last screen?

[sivanvega]

Yes, but the solution is the VBA code when importing the file....this because will be used in different pc's with different date/time configurations.

Thanks.

[DonkeyOte]

Though worth noting that for manual TtC you would need to process each column separately.

[tapetar]

@DonkeyOte


Multiplying with 1 also worked for me! This is awesome, you helped me so much with this "stupid" problem

Thanks!!