InfoPath Dev

My Infopath form submits to a SharePoint List. On submit, the instance of the form is saved with the date. By default, I think this is YYYY-MM-DDTHH_MM_SS, e.g. 2013-03-18T13_49_01. I need it to display as YYYYMMDD_HHMMSS, e.g. 20130318_134901

Solutions have been posted on these other threads, but I haven't been able to get it to work:

• now() function question
• date format
• Using functions to return date in US format

Here's my formula for saving the file name:

• concat(number(substring(now(), 1, 4)), number(substring(now(), 6, 2)), number(substring(now(), 9, 2)), "_", number(substring(now(), 10, 6)))

The output I get is 2013318_NaN. I need it to display as YYYYMMDD_HHMMSS, e.g. 20130318_134901. Any suggestions would be appreciated.

Thanks in advance,

K

 Hi Kei,

The problem is that the 6 digits that represent time in the now() function are separated by colons, so using substring(now(), 10, 6) returns some digits and some colons.  The number() function doesn't know what to do with the colons, so it returns NaN. 

 

Here is the correct formula:

 

concat(number(substring(now(), 1, 4)), number(substring(now(), 6, 2)), number(substring(now(), 9, 2)), "_", number(substring(now(), 12, 2)), number(substring(now(), 15, 2)), number(substring(now(), 18, 2)))

Thanks for the explanation Lindsay. I didn't know about the colons, so that's really helpful info.

For the date, I'm trying to output 8 digits every time. The current formula returns 2013318 and I'd like it to return 20130318. How do I go about getting the month and date to reliably put out 2 digits (01, 02, 03)?

Hi Kei,

 The reason why you are not getting the 0 digits to appear is because you are converting everything to numbers with the number() function.  When the number function sees the value '03' it will convert it into the number '3', because numbers do not have leading zeros on the left.  If you remove the number() function from your formula then the 0 digits will appear.  Use this formula instead:

 concat(substring(now(), 1, 4), substring(now(), 6, 2), substring(now(), 9, 2), "_", substring(now(), 12, 2), substring(now(), 15, 2), substring(now(), 18, 2))

 Sorry I didn't catch this earlier!

Thanks! That did the trick.

Hi Jennifer, Can you help me with not showing the date at the end of the filename on submit?

(substring(now(), 6, 2) the 6 is a start digit and the 2 is the length to be considered as a return value for the function. what is the complete format for now() function which we get some parts from it by applying (substring(now(), 6, 2) to it?

now() returns the datetime in ISO format: 2019-03-05T09:49:00 for example. You can put a calculated value on your form and set it to now() to see this. Looks like the substring you mention would return the month.

I used the code : concat(substring(now(), 1, 4), substring(now(), 6, 2), substring(now(), 9, 2), "(", substring(now(), 12, 2),":",substring(now(), 15, 2), ")") however the name sowed as 20190312(13_27), I was expecting the: to appear . to find the name as 20190312(13:27) . to resume, the: cannot appear in the name of the file.

Right - ":" is not an allowed filename in SharePoint and SharePoint will change that char to an underscore. http://www.sysadminsblog.com/microsoft/file-name-length-and-character-restrictions-for-sharepoint/ has good info on this.

I need today() to be updated column in SharePoint --------- (without filter our any items that added for example [Today]-7 >> this solution is suggested to many sites by professionals but doesn't fit my case cause I don't want to filter out any item) Any solution to this problem?