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
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.
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.
(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
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
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
You're welcome! Glad I could help.
