I've encountered a problem that when exporting reports (e.g. lists of transactions) from SF into Excel it is appearing with the dates in *both* US date format (MM/DD/YYYY) and international date format (DD/MM/YYYY).
This makes it impossible to further slice and dice the data (e.g. sorting by date).
Even when I clear the formats in Excel the problem persists.
Any help would be greatly appreciated!
답변 8개
Hello,
Another option is to generate the excel internal date format (number of days since 1900 - Jan - 0 (yes, 0, really). Create a custom field in your data, create a formula, output format number with 4 decimals if you want to include the time or 0 decimals if you only need the date. Then calculate:
<Date Field> - DATETIMEVALUE("1900-01-01 00:00:00") + 2 + (<x>/24)
where <Date Field> is the original date / time field and <x> is the number of hours before or behind UTC (so -5 for EST, +1 for CET).
This value becomes a date in Excel if you set the field to short-date or long-date (or custom, adding time). And it will be in the correct format for your location.
