Quickly change m/d/yyyy dates to mm/dd/yyyy dates in Excel

Sometimes you get data in an Excel or csv file and it will be in a m/d/yyyy format.


Here is an example

1/1/2005
2/1/2012
2/12/2012
12/5/2015

This will look fine if you open the csv file in Excel but if you need to import this data into a database you might get an error that the format is not correct.

 What you want is the data to be in this format
01/01/2005
02/01/2012
02/12/2012
12/05/2015


If you use Format Cells and then pick Date you will see that there is no mm/dd/yyyy format What you have to do is pick the Custom Category and click on the m/d/yyyy format

Now change m/d/yyyy to mm/dd/yyyy

Click OK and you are all set, you will see that the dates are now in mm/dd/yyyy format

Let me just mention that the problem was not the conversion in SQL itself but a tool that did the import choked

BTW SQL Server can do this fairly easily

If I run the following

1 2 3 4 5 6 7 8 9 10 11

;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(date,date) from dates

I will get this as output

2001-01-01
2001-11-01
2001-01-12
2001-05-05
2001-01-22
2001-12-01
2001-01-01

That of course is just a date, if you want mm/dd/yyyy format, you can use convert and style 101...

1 2 3 4 5 6 7 8 9 10 11 12 13 14

;with dates (date) as (select '1/1/2001' union all select '11/1/2001' union all select '1/12/2001' union all select '5/5/2001' union all select '1/22/2001' union all select '12/1/2001' union all select '01/01/2001' ) select convert(varchar(10),convert(date,date),101) from dates select convert(date,date) from dates

And here is that output

01/01/2001
11/01/2001
01/12/2001
05/05/2001
01/22/2001
12/01/2001
01/01/2001

Office 2007 Service Pack 1 Available For Download

Microsoft has just released their first service pack for Microsoft Office 2007, promising lots of bug fixes, performance enhancements, and improved security. The 2007 Microsoft Office suite Service Pack 1 delivers important customer-requested stability and performance improvements, while incorporating further enhancements to user security. This service pack also includes all of the updates released for the 2007 Office suite prior to December of 2007. You can get a more complete description of SP1, including a list of issues that were fixed, in the Microsoft Knowledge Base article 936982: Description of the 2007 Microsoft Office suite Service Pack 1.

Access 2007
For third-party applications that use Data Access Object code to synchronize replicated information in a Microsoft Office Access™ 2007 database, Office Access 2007 no longer returns a run-time error that causes the application to close or time out.

Communicator 2007
Microsoft Office Communicator 2007 now presents more accurate presence information and does so with consistent visual cues.

• The icons used to display presence are modified so that users who are red-green colorblind can determine people’s presence status.
  
• Office Communicator 2007 no longer causes presence icons to flicker when multiple people appear simultaneously.
  
• Presence information in Office Communicator 2007 and other Microsoft Office applications is consistent in all scenarios.
  
• Microsoft Office Outlook® 2007 no longer starts in the background along with Office Communicator 2007.

Excel 2007
Several stability, general usability, and compatibility improvements have been incorporated into Microsoft Office Excel® 2007.

• Office Excel 2007 no longer stops responding in some instances when opening a workbook containing calculations.
  
• Office Excel 2007 workbooks that are saved in Page Break Preview mode and opened in Microsoft Office Excel 2003 no longer produce an error when switched to Normal view.
  
• When inputting calculations that should result in numbers 65,534 through 65,536, Office Excel 2007 no longer returns the display error of 100,001.
  
• Office Excel 2007 now retains custom colors on shapes in Excel 2003 worksheets.
  
• Office Excel 2007 no longer misplaces or wraps labels in charts when the workbook is reopened.

Groove 2007
For Microsoft Office Groove® 2007, issues surrounding the activation of new and previously retired accounts have been resolved.

• Office Groove 2007 no longer places a 60-day limit on retired accounts that are stored in backup files.
  
• When the auto-activation feature is turned on and invited users open an invitation file (.grv), they are no longer prompted for an activation key or notified of an unconfigured account.
  InfoPath 2007 and InfoPath Forms Server
  Microsoft Office InfoPath® 2007 benefits from a more consistent user experience, especially with online forms.
  
• When users input data into an Office InfoPath 2007 form using a Web browser, the saved changes are now retained after closing and reopening the form.
  
• Font size no longer increases when pasting from one rich text box in Office InfoPath 2007 to another.

Outlook 2007
Microsoft Office Outlook 2007 benefits from a number of fixes and enhancements that make the popular messaging client more stable and easier to use.

• Office Outlook 2007 no longer closes unexpectedly when the junk e-mail rule is set to null.
  
• Body content in HTML-formatted e-mail messages now appears properly in the preview pane or when the message is opened.
  
• Items moved from an offline folder file (.ost) to a personal folder file (.pst) now display properly in the preview pane.
  
• In search folders, Office Outlook 2007 now shows the accurate count for unread items that are hidden.
  
• When scheduling a resource such as a meeting room in meeting requests, the public free/busy information for the resource is now complete and visible in Office Outlook 2007.
  
• When users forward plain-text e-mail messages with attachments, the body text is now retained.
  
• If the Mailbox Cleanup wizard starts while the user is dragging a message into a folder, Office Outlook 2007 no longer closes unexpectedly.

PowerPoint 2007
Fixes in Microsoft Office PowerPoint® 2007 make the presentation application even more reliable, especially when used in conjunction with Microsoft Office Outlook Web Access, Web sites that use forms authentication, or event macros.

• Users who have started an event macro in Office PowerPoint 2007 can now advance slides by right-clicking and then choosing Next or Previous.
  
• When users try to open a presentation in Office PowerPoint 2007 from a Web site using forms authentication, the presentation will now appear properly.
  
• Users can now open presentations in Office PowerPoint 2007 from e-mail attachments in Office Outlook Web Access.
  
• Office PowerPoint 2007 no longer crashes when the user programmatically accesses the parent property of the Hyperlinks Collection Object in the Office PowerPoint 2007 object model.
  
• Text formatting now behaves similarly to previous versions of PowerPoint, where new text retains the formatting of the deleted text instead of using the formatting of the character to the left.

Project 2007 and Project Server 2007
The 2007 Office system SP1 eliminates instances when either Microsoft Office Project 2007 or the accompanying Microsoft Office Project Server 2007 crashes due to a software error.

• Office Project 2007 no longer crashes when users click to view Windows account or Details in the Resources Information dialog box and then perform any other action.
  
• Office Project 2007 does not produce an error message when a link from one task accesses a task in a different project stored on Office Project Server 2007. This error only occurred when the linked project was renamed.
  
• Microsoft Office Project Web Access now makes task IDs available and allows users to enter a work estimate when creating a new task.

SharePoint Server 2007
Microsoft Office SharePoint® Server 2007 has improved manageability and compatibility.

• Office SharePoint Server 2007 now has the ability to be installed on Windows Server 2008 providing compatibility and support.
  
• ASP.NET AJAX has compatibility and support with Office SharePoint Server 2007 including examples for customer web parts build the AJAX 1.0 Control Toolkit and the AJAX 1.0 Extensions for ASP.NET (KB 941955)
  
• New manageability with STSADM commands for consolidating or repartitioning your content databases, renaming host named site collections, and the ability to scope the people picker to a specific active directory organizational unit.
  
• Advanced search results now include results with spaces in file names.
  
• The View by Modified Date function now works correctly in search results.
  
• Office SharePoint Server 2007 is now able to crawl case-sensitive Web content on computers not running the Windows® operating system.
  
• Office SharePoint Server 2007 can now index a public folder on a Microsoft Exchange Server with a backslash in the subject.

Visio 2007
Microsoft Office Visio® 2007 now properly redraws shapes when users apply a dynamic connector that is part of a group of shapes in Office Visio 2007.
Word 2007
By taking care of several small stability and usability details, Microsoft Office Word 2007 is a more useful and reliable tool for everyday document creation tasks.

• With Office Word 2007, users can now open and edit embedded objects that contain other embedded objects.
  
• When using smart documents in Office Word 2007, the Document Actions task pane no longer disappears when moving the pointer into the Reviewing pane. Additionally, task panes opened earlier no longer appear unexpectedly.
  
• Office Word 2007 properly creates and updates a table of contents in unprotected sections of documents that also contain sections protected for forms.
  
• Office Word 2007 no longer prints an extra page when users choose a printer with the duplex option enabled.

Download it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997-25DA01F388F5&displaylang=en