When importing data, the timescale can be mapped on names or using dates. If you choose match descriptions, then it will require an exact character match with the labels as they appear in the grid. For example Jul 11, Aug 11 etc.
If you map on dates then the process is as follows:
- Choose Map on dates.
- Choose the date type of the source file: Dates, Weeks, Periods without a year or Periods with a year.
- Choose the date format.
- Set the option to determine whether you are dealing with calendar years or financial years.
- DD: a one or two digit calendar day between 1 and 31
- WW: a one or two digit week number between 1 and 53 (53 week years are needed every 7 years to catch up as a calendar year is 365 days where as 52 weeks =52*7=364)
- MM: a one or two digit month between 1 and 12
- Q or QQ: a one digit quarter number between 1 and 4
- YY: a two digit year (or if you type YYY into a custom format, as in FYYY, it will look for a valid two digit number anywhere in the string)
- YYYY: a four digit year
Date Formats with separators
Select the date format. For example Christmas day in 2014 in US format is 12/25/14 which would need date format M-D-Y, whereas in the UK, 25/12/14 would be D-M-Y. The hyphen in M-D-Y represents any separator, and the letter represents any legitimate number so 12-25-11, 12 25 11, 12/25/11 and 12/25/2011 are all the M-D-Y format.
Fixed Position Date Formats
If there is no separator, then the date is determined by its position. So, Christmas day in 2014 in US format which is 122514 would be MMDDYY whereas 251214 (UK format) would be DDMMYY.
Custom Time Formats
Custom formats are permitted. For example FY14 03 (representing March 2014) would be custom format ??YY MM. The wildcard character ? can be any character.
What if the number represents a financial year (or month) not a calendar year (or month)?
Sometimes, the year represents the financial year, not the calendar year. For example, suppose a company has a financial year that starts in April.
- If the source file contains a date ‘2014 02' this is ambiguous.
- 2014 could represent the calendar year, the year in which the financial year ends, or the year in which the financial year starts.
- 02 could mean February, the second month of the calendar year, or it could mean May, the second month of the financial year.
An option on the Time Mapping tab lets you choose whether the year number represents the calendar year, or year in which the financial year starts or end. You can also set the week, month and quarter number to be the calendar month or financial year month.