Excel Date/Time Format

[milobc]

Hi there,

I am exporting data from a program which has a Date/Time format of MM/DD/YYYY HH:MM:ss. When it opens in Excel, the seconds are there but not displayed. If I don't reformat the columns to have a custom format as listed then the workbook drops the seconds when the file is saved. As there are several columns that have these dates and times in them I don't want to have to always reformat the columns before saving the book. Also, there may be other people doing this export and I can't always rely on them to perform this step. Is there a way I can make the default Date/Time in Excel show the format I want? My Regional Settings match this format but I think that Excel is only looking to the short time which doesn't include the seconds.

Help would be hugely appreciated!

[xenixman]

Don't know what version of Windows your on, so this is for Win 7

As far as I know there is nothing preventing you from changing the System Short Time to display the same as the System Long Time.

Go to Control Panel
Select: Clock, Language & Region
Select: Change the Date, Time or Number Format
When the Region and Language window pops up,
Click, Additional Setting button, near the bottom
When the Customize Format window pops up,
Select: the Time tab
In the box labeled, Short Time, change the format to: HH:MM:SS TT
Click Apply
Click OK and exit out.

See if that works for you.

[milobc]

Thanks, I tried that but it didn't work. Excel still only shows HH:MM and doesn't display the ss part even though when you click in the field you can see it in the formula bar. The only way I've been able to find to make this work is to create a personal macro so that the columns can be properly formatted before the user saves the book.

[xenixman]

Sorry that did not work out for you.

Your only other option would be a Macro that kicks of with some trigger.
IE Open Workbook, or Save Workbook
or some other trigger that would auto reformat your dates.

Sorry my VBA skills are minimal.

[milobc]

Thanks...I'll keep working at it.