I’m working with a Nintex workflow at the moment, and trying to do a simple thing – format a date. Unfortunately, the function fn-FormatDate does not work correctly.
I am UK based, so using a UK Locale on my SharePoint site. I’m not sure if that’s relevant, but it could be.
In theory, the use of this function should be pretty simple:
That should return “Thursday” (and does). The token that can be used – such as the dddd – are the standard .NET ones used in the DateTime.ToString() function. You can see them here: Custom Date and Time Format Strings
Except they’re not quite that simple. Let’s do a test.
For brevity, I’ve left out the workflow variable being fed into each of these functions, but it’s a workflow variable populated by querying for a list item in my SharePoint system. It is of the ‘Date and Time’ variable type.
fn-FormatDate( ,dddd, dd MMMM) //fn-FormatDate(12/18/2014 12:00 AM,dddd, dd MMMM)
fn-FormatDate( ,'dddd, dd MMMM') //fn-FormatDate(12/18/2014 12:00 AM,'dddd, dd MMMM')
fn-FormatDate( ,"dddd, dd MMMM") //fn-FormatDate(12/18/2014 12:00 AM,"dddd, dd MMMM")
fn-FormatDate( ,dddd dd MMMM) //Thursday 18 December
fn-FormatDate( ,'dddd dd MMMM') //dddd dd MMMM
fn-FormatDate( ,"dddd dd MMMM") //Thursday 18 December
Right, so the top three are pretty clearly failing to process at all. My guess is that the comma breaks whatever parsing Nintex uses.
There are 2 that work correctly, which is nice – though the opposite of what I found on the Nintex forums, though one of the later posts also gets the same result as me.
Okay, so, no comma in the format string (which the .NET format handles just fine). I can live with that, though – I’ll format before the comma and after the comma separately.
Next up, what do the tokens mean? I’d just wanted a single digit for single digit days in the month – e.g. ‘7’ rather than ’07’. In .NET, that’s a single d to format it such. However testing shows…
fn-FormatDate(12/01/2014,d) // 12/1/2014
fn-FormatDate(12/01/2014,dd) // 01
fn-FormatDate(12/01/2014,ddd) // Mon
fn-FormatDate(12/01/2014,dddd) // Monday
Right, this is a bit more obvious, and it’s one I’ve come across before. ‘d’ is BOTH the definition of the ‘Standard date format‘ (which is what we see above) and a single digit day of month in a custom format. If we try something that is definitely a custom format:
fn-FormatDate(12/01/2014,d MMM) //1 Dec
fn-FormatDate(12/01/2014,dd MMM) //01 Dec
fn-FormatDate(12/01/2014,ddd MMM) //Mon Dec
fn-FormatDate(12/01/2014,dddd MMM) //Monday Dec
…and that’s the sort of thing I want.
This leads to an interesting conclusion:
- Don’t use Commas in the date format string – it won’t be parsed correctly.
- Don’t use single letter date format strings – unless you want the respective Standard Date Format.
Also of note is that I was having other problems when I was reading the Date value from the list item in SharePoint into a ‘Single Line Text’ variable rather than a ‘Date and Time’ variable. I think this was due to the date being rendered as a UK format date which couldn’t then be parsed. I did find reference to this here: UK date format displayed in SharePoint, interpreted as US date format in Nintex