Searching for an Elegant Solution to the Auto Format Date and Time Quirks in Microsoft Excel
Microsoft Excel is without doubt a terrific program and one that is used by many design engineers. However, a couple of its quirks have been annoying me for years. One is the date autoformat and the other is the time autoformat. I’m referring to Excel’s penchant to change hyphenated numbers, like 1-3, or numbers with a slash, like 2/5, into dates when you bring them into Excel. Excel also likes to change times, like 5:10.3 (five minutes, ten point three seconds) into “clock” time, i.e. 12:05.10 AM.
If you search on the web you’ll find lots of users frustrated with these quirks (mostly the date quirk) and good solid answers of how to avoid them—for some cases. Mostly, the answer is to import your data file into Excel and use the format wizard to format the 1-3 or 2/5 as text. This assumes that you have your data in a text file, of course. The other typical answer is for you to format cells as text, before you enter the data into Excel. This method assumes that you are sitting at your keyboard and entering the data into Excel by hand. Nobody does that, right?
The operation that I normally do to bring this quirk to the forefront is to copy and paste from the Web, i.e., from an HTML page. If a 1-3, 5:10.3 or any other number or time that qualifies is within the data I’m copying from a web page, I’ll get a date and time for them, even if I format cells as text beforehand. So what’s the solution?
After piecing some advice together from the web, I found the following solution. First use the Select All button in Excel to select all the cells. This button is in the upper left corner of the spreadsheet where the row and column designators essentially meet. Next right click on one of the cells and choose Format Cells>Text. Then copy the data of interest from the Web page. Now paste that data into Excel. You’ll still see all the dates and times, but you need to do one more thing. Find the little icon that looks like a clip board. It usually appears at one of the bottom corners of the pasted data. Click on that and select Match Destination Formatting. Voila! All the dates turn back to the original text and all the times return to the text version of the time. You can then manipulate the times to your heart’s desire by using the text formulas. Usually, I’ll change hours and minutes into seconds with the formulas and add, subtract, sort, etc.
This approach is the most elegant one I’ve found to date. And by the way, if you don’t want to select “all cells” and format them as text every time you want to create a new file, you can do it once and save the file as an Excel template file.
If anyone knows of a more elegant solution to this Excel quirk, I’d like to know about it.