6

For work, we frequently work with generated csv files which have time data as one column, and the time data is accurate to the nearest second. Opening the files in Excel 2007 (On Windows 7), the display defaults to show m/d/yyyy hh:mm. If we save the file (even without actually adding or changing any data), the seconds data is lost, unless we first change the formatting to m/d/yyyy hh:mm:ss.

Is there some way to convince Excel to default to showing the seconds data, so that we don't have to worry about losing the seconds data?

Note - this is about csv files, since the tools which generate these files, and which operate on them afterwards, understand csv but not xls(x) formats.

Note 2 - I found this answer, which doesn't seem to work.

Community
  • 1
  • 1
Anthony Argyriou
  • 61
  • 1
  • 3
  • Is it time and date or just time? A quick hack to force time formatting is to include a decimal part in the data : `00:00:01.00`. You will lose the date part though. Beyond that, I am really struggling to find a way to do it. Even changing the default workbook style (and forcing `Normal` to be formatted as `mm/dd/yyyy HH:mm:ss`) is not enough because Excel ignores that formatting when opening a CSV. It does work however when importing the CSV as a `Data->From Text` but now your normal template is messed up and that's not ideal for opening files. – Byron Wall Jun 03 '15 at 18:04
  • It is worth mentioning that Excel reads this information correctly but saves the CSV with the Number Format applied in the cell. Since the default does not include `ss` it drops that data by default. I agree that changing Regional Settings (short time) does not affect default display format. Interesting question. I'll be following to see if someone can solve this. – Byron Wall Jun 03 '15 at 18:24
  • Right - the problem is saving without losing data. I've been doing this by hand - reformatting to include seconds every time, but I want something I can pass on to my less Excel-friendly coworkers. – Anthony Argyriou Jun 03 '15 at 21:27

2 Answers2

0

You already have the solution. You may write a macro in the PERSONAL.XLSB and assign a hot key. After open the CSV file in Excel, just hit 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 may use Ctrl-H to call the Find and Replace dialog box and click Format ... ... and click Replace All: Find and Replace format

kelvin 004
  • 413
  • 2
  • 7
  • The goal was to avoid having some additional step. The concern is that if you accidentally open the file and save it before running this code, you will lose data. Ideally, Excel would open the file with the proper formatting so as to prevent this lose of data after saving. – Byron Wall Jun 30 '15 at 13:31
  • Try to use addin, xla or xlam. The code in the addin can be: If file opened is csv, then scan the usedRange with the format ... hh:mmand change the format to ... hh:mm:ss without prompting user. Hence all happen behind the scene. – kelvin 004 Jul 01 '15 at 05:28
0

I had success with this formula applied to the datestamp cell:

=text(F3, "yyyy-mm-dd hh:mm:ss")

Keeps the seconds in csv and imports find into mysql.

Jack Straw from Wichita
  • 57
  • 2