Format a date in a SharePoint workflow using a SharePoint formula
Learn how you can use the TEXT formula in a calculated field in a SharePoint list to format a date in a SharePoint Designer workflow.
You can use a custom SharePoint Designer action to format a date in a workflow, but if you do not want to or cannot write code, you can also use a SharePoint Calculated column to format a date in the same SharePoint list a workflow is running on or in another SharePoint list.
But whichever method you choose, you will have to pause the workflow until the value for the formatted date has been calculated, before the workflow can proceed.
There are basically two ways to pause a SharePoint Designer workflow:
- Use a Pause For Duration action.
- Use a Wait For Field Change in Current Item action.
Note: You could also use a Pause Until Date action to pause a workflow, but it does not make sense to wait days to format a date.
Using a ‘Pause For Duration’ action
If you use a Calculated column in the same SharePoint list as the workflow runs on, you would have to use a Pause For Duration action to wait until the value has been calculated before the workflow can proceed, because the calculation is not instantaneously performed.
A disadvantage of using the Pause For Duration action is that the period the workflow will wait is not precise.
Using a ‘Wait For Field Change in Current Item’ action
If you use another SharePoint list to format the date, you can use a Wait For Field Change in Current Item action to pause the workflow and wait until the value has been calculated and the value of a column in the list item updated before the workflow can proceed.
A challenge when using this method is that you will have to find a way to communicate between two SharePoint lists. This article explains how you can do just that by using two dependent SharePoint lists and workflows.
Format a date using dependent workflows in SharePoint Designer
You can do the following to create 2 SharePoint lists and 2 SharePoint workflows that depend on each other for input data.
In SharePoint, create 2 lists:
- The first SharePoint list is the list on which the main workflow runs and in which you want to have a date formatted to display e.g. 09 June 2009.
- The second SharePoint list is the list that will function as a calculator, that is, you will use it as a dependent list to only perform calculations, such as e.g. formatting a date.
In SharePoint Designer, create 2 workflows:
- The first workflow should run on the first SharePoint list whenever a new item is created in the list. Note: Do not set this workflow to run whenever an item is updated; this to prevent winding up in an endless loop when the second workflow updates the first SharePoint list.
- The second workflow should run on the second SharePoint list whenever a new item has been created.
How it works
The following figure displays the relationship between the two SharePoint lists as well as the actions taken by the each workflow to be able to format a date in the main SharePoint list.
Figure 1. Dependent SharePoint lists and workflows to format a date.
- The user manually creates an item in List 1.
- Workflow 1 starts to run and creates a new item in List 2. When it creates the new list item, it updates the Date and Parent GUID columns with values from List 1. The GUID of the Current Item is stored in the Parent GUID column of the item in List 2 and will be used by Workflow 2 to find the item in List 1 to update after the calculation has been performed.
- Workflow 1 waits until its Formatted Date column is updated.
- When the new item has been created in List 2 its Formatted Date column will be calculated and contain a value based on the following SharePoint formula:
=TEXT([Due Date],"dd MMMM yyyy")
- After the new item is created in List 2, Workflow 2 will start to run.
- Workflow 2 retrieves the list item from List 1 that issued the calculation. It uses the GUID value that was stored in the Parent GUID column in List 2 to find the parent list item in List 1.
- Workflow 2 updates the Formatted Date column of the item in List 1 with the value calculated by List 2. This is also a sign for Workflow 1 to continue processing whatever it needs to process.
- Workflow 2 deletes the item created by Workflow 1 from List 2.
If this solution is still unclear, you can watch a demo I’ve created on how to use a calculated field and two dependent workflows in SharePoint to format a date with the 'dd MMMM yyyy' format.
And if you’re looking for other date formats, try these…
|d||Days as 1-31|
|dd||Days as 01-31|
|ddd||Days as Sun-Sat|
|dddd||Days as Sunday-Saturday|
|M||Months as 1-12|
|MM||Months as 01-12|
|MMM||Months as Jan-Dec|
|y||Years as 0-99|
|yy||Years as 00-99|
|yyyy||Years as 1900-9999|
- Use a calculated field and two dependent workflows in SharePoint to format a date
- Format a date in a SharePoint Designer workflow using a custom action
- Custom SharePoint Designer workflow activity to format dates