2

(Note: This SO question is similar, but I'm interested in solutions that are more scalable. Hence the "batch-convert" in the title. Also, I originally posted this question to StackOverflow, but then I realized that I was specifically interested in solutions that required no VBA programming, so I moved the question to SuperUser, per the suggestion given here.)

What is the "least invasive" way to convert all the "datetime cells" of an Excel spreadsheet to the corresponding string value?

By "datetime cell" I mean one whose content is actually a floating point number, but whose format causes it to be displayed as a date-time string.

By "least invasive" I mean some method that requires only the most minimal modification to the spreadsheet. (In particular, I'd like to avoid, or at least minimize, the amount of cutting and pasting required.)

The ideal method would be one that would require only selecting the datetime cells to be turned to strings, and applying the required conversion "in place". I've looked for something like this without success.

I will submit the best solution I've found so far for this problem (see among the Answers below), but I find this solution extremely cumbersome and error-prone, and would like to learn of a safer, more economical way to achieve the same result.

Improve this question
1
  • 1
    Where is your data coming from? I'm assuming you're not hand entering, rather importing data? If so, the best course may be in that process. Otherwise you'll almost certainly have to incorporate some cutting and pasting in the process.
    – dav
    Commented Jun 11, 2013 at 19:11

1 Answer 1

Reset to default
4

(NB: I am a complete dunce with Excel. I am sure that there are less cumbersome ways to do what I describe below. I'm posting this solution in the hopes that someone who really knows Excel will post a better one.)

The best solution I've found so far (based on this SO answer) is the following. Suppose that the cells to be converted are in the range A1:100, and suppose column X is not being used. Put the following formula in cell X1:

=TEXT(A1, "DD/MM/YYYY hh:mm:ss")

(Of course, modify the datetime format to suit your needs.)

Then, "drag-copy" this formula to all the cells in the range X2:100. Next, copy range X1:100, and paste it onto the range A1:100 using the Paste Special... command with its Values option.

Finally, Clear > All the range X1:100.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .