Home > Work > Splitting a date into Year, Quarter and Month for analysis purposes in InfoPath 2010 #SP2010 #SharePoint #in

Splitting a date into Year, Quarter and Month for analysis purposes in InfoPath 2010 #SP2010 #SharePoint #in

January 25, 2011 Leave a comment Go to comments

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:
– January
– September
– December
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):
– 2011 01
– 2011 09
– 2011 12

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:

Categories: Work Tags: ,