FormatDate returns formatted date only sometimes

Y
Badge +7

On a form I have a repeating section for the date (txt_Data) and amount of kilometers (num_Km) travelled. In the workflow I convert the XML into text (Query XML and build string). Before building the final string I format the date.

186245_pastedImage_1.png

186246_pastedImage_2.png

Build String configuration for formatting the date:

186247_pastedImage_3.png

Build String configuration for more than one line in repeating section:

186248_pastedImage_4.png

After the loop this variable txt_Specificatie is written to the column SpecificatieKilometers in a list (item updated).

This is the result in the list (snippet):

186238_pastedImage_0.png

I don't understand why formatting the date only works half the time, it seems completely random as it occurs with one or two and more lines in the repeating section. In other workflows I don't have this problem with FormatDate.

Any words of advice?

5 replies

M
Badge +6

Hi Yvette,

Are you only seeing this occurring on dates where the day is greater than 12? Certainly looking at your examples, the entries where the day is 10, 11 or 3 appear to work. Whereas the days that are 23, 24 and 16 do not. It appears to be failing because it is trying to evaluate 16, 23 and 24 as month values - I think.

I would be curious to see what the txt_Data value is before the fn_FormatDate function as opposed to after.

Based on what you are showing in your examples are the first two entries supposed to be displayed as is? ie 10 June and 11 June or are they suppose to be 6 Oct and 6 Nov? Judging by your function formatting you are converting to MM-dd-yyyy.

Something does not appear right.

Regards,

Mark

Y
Badge +7

Hi Mark,

Thanks for your reply! You are right! They were all dates for June and can't return formatted when the day is greater than 12, apparently because it's getting confused with the month value.

txt_Data is from a control in a repeated section configured to only show the date, not the time. A date control connected to a list column is automatically in the Dutch date notation, but since this control is not connected the date renders differently. So the date June 11th shows as 06/11/2016 00:00:00.

I've just run two tests. One with the original formula: fn-FormatDate({WorkflowVariable:txt_Data}, MM-dd-yyyy).

Here's the result for a travel expenses specification consisting of two lines (sections) for the dates June 11th and 13th.

186277_pastedImage_0.png

I ran the second test with the formula fn-FormatDate({WorkflowVariable:txt_Data}, dd-MM-yyyy). But that does result in June 11th being shown as October 6th. And again, the 14th of June can't be converted.

186284_pastedImage_1.png

(Snippets both taken form the workflow e-mail not the list. At the end of the workflow txt_Data is combined with txt_Km  on one line.)

Now knowing this is not a random problem but connected to the FormatDate formula, I've also come across this post: UK date format displayed in SharePoint, interpreted as US date format in Nintex

I've not yet found the solution though!

Kind regards,

Yvette

M
Badge +6

Hi Yvette,

I just had a little play with the code and the following appears to work:

fn-FormatDate(fn-Substring(06/28/2016 00:00:00,3,2)/fn-Substring(06/28/2016 00:00:00,0,2)/fn-Substring(06/28/2016 00:00:00,6,4), dd-MM-yyyy)

All it does is extract the 2 digits for the day and month and the 4 for the year and rearranges them. This is of course base on the proviso that you have leading zeroes on the day and month.

Just replace the date I have type with your field and see if that works.

Cheers,

Mark

Y
Badge +7

Hi Mark,

This proved to be the solution indeed!

On the form the selected dates are shown in the Dutch notation without zeroes but in the background (in the hidden Form Data column) dates are saved in the US notation and with the time included - exactly as you illustrated in your example of the formula. Therefore I could simply use the date variable from my Get Repeating Section UDA and Query XML in combination with the formula you supplied!

Thanks for helping me out!!

Kind regards,

Yvette

M
Badge +6

You're welcome! Glad I could help.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings