How to automate formatting of a raw data dump into a spreadsheet

[jthompso]

I posted a question trying to get some formulas for dates working. Problem solved, however, I'm trying to build a template that everyone on my staff can use. It doesn't work for me to expect the users of the template to do any post-export formatting on the template I am creating. So, how do I make the formatting automatic?

Here's what's happening: Our database is dumping raw data into Excel, and from that dump, I am creating various sheets that serve up the data in ways that everyone on our staff wants to see. One of those forms of data is dates. However, merely formatting the cells as "Date" (Home -> Number -> Date), doesn't cut it, because my SUMIFS formulas don't work unless I: highlight the column, choose "Text To Columns" -> Delimited -> Check Tab,uncheck everything else -> Select "Date" and MDY from dropdown menu, and click "Finish". (Kudos to the regulars on this board for helping me figure this out).

Is it possible to automate the process I've written above in bold? It will always be the same column, and there are no other data types except dates.

I'm using Excel 2013 and sometimes 2007.

 

[RonB1111]

First, you can simplify the date formating by doing just these steps: highlight the column, choose "Text To Columns" - and click "Finish".

Second, you can record a simple macro of these steps starting from the ribbon: Developer - Record Macro - (be sure to enter a shortcut letter where indicated) - highlight the column, choose "Text To Columns" - click "Finish" - Developer - Stop Recording. So assume you assigned letter Q as your shortcut, you can just hit Ctrl-Q to invoke those 3 steps.

 

[jthompso]

Thank you!

 

[RonB1111]

You're welcome!