When loading data into Salesforce, I always use both a ‘Working File’ that is in Excel format as well as CSV files to load. Excel, as my default CSV editor, loves to convert Salesforce friendly dates (ex. 2013-01-01) to Excel’s default (1/1/2001) when I open and edit the CSV files. Below are three ways to convert Excel’s default date and time values to Saleforce friendly formats.

METHOD 1: USING EXCEL FORMULAS

=IFERROR(TEXT(DATEVALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),LEN(SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”))-SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),1)+1))&”-“&TEXT(LEFT(SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),1)-1),”0#”)&”-“&TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),1)+1,SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),1)+1)-SEARCH(“/”,SUBSTITUTE(SUBSTITUTE(A2,”-“,”/”),”.”,”/”),1)-1),”0#”)),”YYYY-MM-DD”),””)

Date Only
=text(A1,”yyyy-mm-yy”)
Date/Time: Central Time, USA, during daylight savings GMT-5; aka (-5/24)
Text(A1 -(-5/24),”YYYY-MM-DDThh:mm:ss.00Z”)

METHOD 2: USING VBA USER DEFINED FUNCTIONS

Date Only

Function SalesforceDate(Cell As Range) As String
SalesforceDate = Format(Cell, “yyyy-MM-dd”)
End Function

Date /Time: Central Time, USA

Function SalesforceDateTime(Cell As Range) As String
SalesforceDateTime = Format(Cell, “yyyy-MM-ddThh:mm:ss00Z”)
End Function

 

To add these functions to your Excel spreadsheet:

  • When in Excel, hit ‘Alt+F11’ to open up the Visual Basic Editor
  • In VBA, go to the ‘Insert’ menu and click new module
  • Paste one or both of the VBA functions in the new module space
  • Save the Spreadsheet (with XLSM as the type to allow macros)
  • Close the VBA window
  • In Excel, you can now type “=SalesforceDateTime(A2)” for example to convert cell A2 to a Salesforce friendly Date/Time

If you want to permanently add them, you can create an Excel add-in file or add them to you Personal.XLSB macro workbook. Contact me if you want to know how.

METHOD 3: A PERMANENT CHANGE TO THE WINDOWS OS
(my favorite method!)

This is my favorite method. I work almost exclusively with Salesforce so it works for me as a permanent solution. It’s easy enough to change if you ever want to go back to the 1/1/2001 format

  • In Windows 7/8 click ‘Start’, then ‘Control Panel’
  • ‘Click on Region and Language’
  • Change the ‘Short Date’ to ‘yyyy-MM-dd’
  • Excel (and Windows) will now default to a Salesforce friendly date format

SalesforceDateTime_ChangeWindowsDefault

a little note about time zones…

Salesforce assumes data is loaded in Greenwich Mean Time (GMT) aka Coordinated Universal Time (UTC) aka London Time (sort of). You’ll need how many hours + or – your time zone is from GMT and this differs based on your local and whether or not you’re on daylight savings time (DST). During DST, the Central Time Zone in the United States is ‘GMT – 5’ (five hours behind) Greenwich Mean Time. Since I am 5 hours behind GMT, I put a ‘-5/24’ in the formula above which is 5 out of 24 hours.

Please feel free to Email or Comment with Questions!