Force Excel 2007 formatting to display seconds in data time

Question

For work, we often work with generated csv files that have time data as a single column, and time data is accurate to the nearest second. Opening files in Excel 2007 (in Windows 7) the default display shows m / d / yyyy hh: mm. If we save the file (even without actually adding or modifying any data), the seconds data will be lost unless we first change the formatting to m / d / yyyy hh: mm: ss.

Is there a way to convince Excel to display second data by default, so we don’t have to worry about losing second data?

Note. We are talking about csv files, because the tools that generate these files and which subsequently work with them understand the csv formats, but not xls (x).

Note 2 - I found this answer that does not seem to work.

Answer 1

You already have a solution. You can write a macro in PERSONAL.XLSB and assign a hotkey. After opening the CSV file in Excel, just press the hot key.

Sub Macro1()
'          
    Application.FindFormat.NumberFormat = "m/d/yyyy h:mm"
    Application.ReplaceFormat.NumberFormat = "m/d/yyyy h:mm:ss"
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

Or you can use Ctrl-H to bring up the Find and Replace dialog box and click Format ...... and click Replace All: