Values in the following columns must be formatted as a Refine 'date':
...
| Example date | Convert with |
|---|---|
2012-12-31 (ISO8601) | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
31/12/2012 (European style, day first - non ambiguous) | Can be converted with toDate(value), or using the "Quick Resolution" option to try automatic conversion, but recommended to use toDate(value, "dd/MM/yyyy") |
| 12/31/2012 (US style, month first - non ambiguous) | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
| 02/01/2012 (European style, day first - ambiguous) | toDate(value, "dd/MM/yyyy") |
| 02/01/2012 (US style, month first - ambiguous) | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
| 31 December 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
| 31st December 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
| 31st Dec 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
| December 2012 | toDate(value, "MMMM yyyy") |
| 2012-12 | toDate(value), or use the "Quick Resolution" option to try automatic conversion |
| 12/2012 | toDate(value, "MM/yyyy") |
One issue to note is that the toDate function will always give a full date (i.e. down to the granularity of a specific day), which means that when you convert less granular dates (e.g. "December 2012" or just "2012") the toDate format will interpret this as being the 1st of the month or year in question. This works well as a default behaviour for values in "DateFirstPackageIssue" but not for values in "DateLastPackageIssue". In the latter case some additional manipulation maybe needed.
| Example date | Convert for DateFirstPackageIssue | Convert for DateLastPackageIssue |
|---|---|---|
| 2012 | toDate(value), or use the "Quick Resolution" option to try automatic conversion | inc(inc(toDate(value),1,"year"),-1,"day") |
| 2012-12 | toDate(value), or use the "Quick Resolution" option to try automatic conversion | inc(inc(toDate(value),1,"month"),-1,"day") |