Splitting a date into Year, Quarter and Month for analysis purposes in InfoPath 2010 #SP2010 #SharePoint #in
Last week at a client I had the requirement to split a date into various parts for various uses on the form and the list for analysis purposes.
Although not a lot of hard work (it was done and dusted within 15 minutes). I thought I would share some of my findings and the formulas and rules I used to achieve my requirement.
The Starting Point – Fields and Settings
Initially we need some fields to store the data:
Column Name | Column Type | Column Settings | Comments |
Activity Date | Date and Time | Date Only | All other fields will be derived from this column |
Year | Single Line Of Text | No. Characters: 4 | Stored as text |
Quarter | Number | No. Decimals: 0 Min: 1 Max: 4 |
|
Month | Single Line Of Text | No. Characters: 9 | For storing Month names.
Example: |
Month No. | Number | No. Decimals: 0 Min: 1 Max: 12 |
|
Month No. Text | Single Line Of Text | No. Characters: 2 | Value Example: – 01 – 09 – 12 To aid with sorting when combining with Year values. Example (YYYY MM): |
Formula / Default Values:
Column Name | Formula / Default Values | Comments |
Year |
substring(../my:Activity_x0020_Date, 1, 4) |
SharePoint stores dates in the following format to avoid regional issues:
– YYYY-MM-DDTHH-MM-SSZ Example: – 2011-01-24T22-25-00Z The formula to the left takes this into account. |
Month No. | substring(../my:Activity_x0020_Date, 6, 2) | As a number field, this will automatically take off the preceding zero. |
Month No. Text | substring(../my:Activity_x0020_Date, 6, 2) | As a text field, the preceding zero will be retained. |
NB: By copying and pasting the default values above, it is likely that you will need to remap the field selector(../my:Activity_x0020_Date) to accommodate the date field in your form.
Rules:
Column Name | Condition | Action | Run Remaining Rules |
Month No. | Month No. >= 10 | Set Quarter = 4 | No |
Month No. >= 7 | Set Quarter = 3 | No | |
Month No. >= 4 | Set Quarter = 2 | No | |
Month No. >= 1 | Set Quarter = 1 | No | |
Month No. Text | Month No. Text = “01” | Set Month = “January” | No |
Month No. Text = “02” | Set Month = “February” | No | |
Month No. Text = “03” | Set Month = “March” | No | |
Month No. Text = “04” | Set Month = “April” | No | |
Month No. Text = “05” | Set Month = “May” | No | |
Month No. Text = “06” | Set Month = “June” | No | |
Month No. Text = “07” | Set Month = “July” | No | |
Month No. Text = “08” | Set Month = “August” | No | |
Month No. Text = “09” | Set Month = “September” | No | |
Month No. Text = “10” | Set Month = “October” | No | |
Month No. Text = “11” | Set Month = “November” | No | |
Month No. Text = “12” | Set Month = “December” | No |
The Result:
-
January 25, 2011 at 09:44Tweets that mention Splitting a date into Year, Quarter and Month for analysis purposes in InfoPath 2010 #SP2010 #SharePoint #in « Giles' SharePoint Shenanigans -- Topsy.com