Searching for an Elegant Solution to the Auto Format Date and Time Quirks in Microsoft Excel

Jan. 4, 2012
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 cor

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.

Sponsored Recommendations

Highly Integrated 20A Digital Power Module for High Current Applications

March 20, 2024
Renesas latest power module delivers the highest efficiency (up to 94% peak) and fast time-to-market solution in an extremely small footprint. The RRM12120 is ideal for space...

Empowering Innovation: Your Power Partner for Tomorrow's Challenges

March 20, 2024
Discover how innovation, quality, and reliability are embedded into every aspect of Renesas' power products.

Article: Meeting the challenges of power conversion in e-bikes

March 18, 2024
Managing electrical noise in a compact and lightweight vehicle is a perpetual obstacle

Power modules provide high-efficiency conversion between 400V and 800V systems for electric vehicles

March 18, 2024
Porsche, Hyundai and GMC all are converting 400 – 800V today in very different ways. Learn more about how power modules stack up to these discrete designs.

Comments

To join the conversation, and become an exclusive member of Electronic Design, create an account today!