Dealing with date/time formatting when importing into Excel can be a bit confusing and can cause data errors if done incorrectly. Whether you are importing data from a different data source into a date/time field or simple back populating a field based on another date field, the format you import in can have a big impact on the time that gets shown in Salesforce.
The first thing to understand is that each user account has a local setting that specifies which time zone should be used to display data. Since data in the system is displayed differently based on time zones, two users could see different times even though the underlying value is the same. Similarly, when the user enters a date/time value through the UI the time is presumed to be in their time zone, this is usually a fairly safe assumption–however when a user is inputting data in bulk its not nearly as likely that the times they are entering are from within their time zone.
For example system data from a marketing automation system could be configured to use the time zone of a central office and a user from a different office could be configured for their local time. Because of these types of scenarios, when importing date/time data its important that time zone the data was generated in be specified. Keep in mind that if the source data was exported from Salesforce it will export in the time zone of the running user, and therefore should be re-imported using that same time zone.
In order to specify the correct time zone in Excel you will need to select the cells which contain date/time data then use the format cells tool.
Lets work through an example, I have a series of records where I have a date field but we have decided to start tracking this data as a date/time. My existing records all only have date information and there is no way for me to know the exact time so the best we can do is make sure the date is right for legacy records. Typically when you set a date to a date/time format it will assume the time component is 0 or 12:00AM. If your user setting for time zone matches the setting used by the rest of the org it is safe to format the cell without time zone information, this will use your account setting as the default:
yyyy-mm-ddThh:mm:ss
However, if you org is configured for PST/PDT but you’re located on the east coast and your account is configured for EST/EDT you would need to configure the cell formatting option to use PST/PDT.
PST:
yyyy-mm-ddThh:mm:ss.���-�8��
PDT:
yyyy-mm-ddThh:mm:ss.���-�7��
For other time zones just modify the last 4 digits based on the UTC notation for the time zone you want to use.
One last thing to consider, if your organization uses multiple time zones each user will see a time in their configured time zone, in some cases this may make times show up as a day before or a day after which can be problematic in reporting. Because of this it can make sense when making artificial date times like in our example to instead set them to 12:00PM so most time zones will still be on the same day. Furthermore, if you are a multiple time zone org and you need to report across date times constantly across the entire organization you can use a formula to create a date/time field that will display in a specific time zone rather than the current users time.
