Calculate Fiscal Year Using Workflow

This one might be simple but is often requested for. Most organizations track their numbers in an operating or fiscal year which typically spans two calendar years. For example, if bookkeeping were to start every October, then Fiscal Year 2014 would mean an operating period of Oct 2013 to Sept 2014.

This post demonstrates how to auto calculate fiscal year based on month and year selection using SharePoint Designer workflow.

To better illustrate each step, here’s a screenshot of the completed workflow:

  1. Prepare your fields
    At the minimum, you’ll need Month, Calendar Year, and Fiscal Year fields. Recommended format:

    • Month & Calendar Year = Choice fields
    • Fiscal Year = Single line of text
       
  2. Hide Fiscal Year field
    Because the value will be auto generated by the workflow, you won’t need this field to appear on the form (Don’t know how to hide? Read this post)
     
  3. Set initial Fiscal Year value
    1. Create a Variable
      • Click on “Local Variables” in SPD (SharePoint Designer) ribbon
      • Add an “Integer” variable named “CalculatedYear”

         
    2. Set value of CalculatedYear variable to Calendar Year
      • Select “Set Workflow Variable” action
      • Select “CalculatedYear” workflow variable
      • Click on “value” then click on
      • Select “Calendar Year” field. Then click OK

         
    3. Set initial Fiscal Year value
      • Select “Set Field in Current Item” action
      • Select “Fiscal Year” field
      • Click on “value” then click on
      • Type “FY” then click on “Add or Change Lookup” button
      • Select Data source of “Workflow Variables and Parameters”
      • Select “Variable: CalculatedYear” field then click OK

         
      • At this point, you should see this. Click OK.

         
  4. Create exceptions for first Calendar Year
    1. Create workflow conditions
      • Click in between the 2 workflow actions then add an “If any value equals value” condition
      • Add all the months for the first Calendar Year as workflow conditions, e.g.: if fiscal year starts in July then create different conditions from July to December
      • Change the conditions from “and” to “or” operation

         
    2. Add 1 to Calendar Year
      • Add “Do Calculation” action
      • For 1st value, select “Variable: Calculated Year”

         
      • For 2nd value, type “1”
      • Leave “Variable: calc” as is
         
    3. Assign new value to variable
      • Add “Set workflow variable” action
      • Select “Variable: CalculatedYear” workflow variable
      • Click on “value” then click on
      • Select Data source of “Workflow Variables and Parameters”
      • Select “Variable: calc”

         
  5. Create “End of Workflow” at the bottom of your workflow design. Optional step: Log workflow activities
    As a general good practice, you should create log history along the way. In this example, I created a log to differentiate between first or second Calendar Year in any given Fiscal Year.
     
  6. Start workflow automatically when item is created or changed
    Select these settings in SPD so your users won’t have to manually kick off the workflow.

     

Here’s the end product:

Advertisements

2 responses to “Calculate Fiscal Year Using Workflow

  1. links to pics are broken

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s