0

Here is what I am trying to achieve:

I have a column with this 2014-NNNN/XXT

I need to extact the number part (NNNN) and increment with nearest multiple of 10. So for instance if the number is 5010, the result should 5020. But if the number is 5016 the result should also be 5020.

Up until now, I have only be able able to extract the number by using 2 calculated columns: one with the RIGHT function in order to remove 2014- and the LEFT function acting on result to remove /xxt.

I now need to increment with a multiple of 10, as previously explained.

Any assistance would very appreciated.

Many Thanks

  • just to clarify. 5019 = 5020; 5090 = 5100; 5101 = 5110 ? – MdMazzotti Jan 29 '14 at 12:12
  • that is correct. @MdMazzotti – Luis Carvalho Jan 29 '14 at 12:16
  • will NNNN always be 4 characters? – MdMazzotti Jan 29 '14 at 12:21
  • Yes, and it will always be a number. – Luis Carvalho Jan 29 '14 at 12:26
0

Here's your formula:

=VALUE(MID([YourField];6;4))-MOD(VALUE(MID([YourField];6;4));10)+10

(no need to have 2 calculated columns; only one will do)

  • YourField in this case would be the column with 2014-NNNN/XXT ? is this correct? – Luis Carvalho Jan 29 '14 at 12:48
  • @LuisCarvalho yes, correct – MdMazzotti Jan 29 '14 at 12:49
  • IT is working!! there was just a little typo on your text - a [ was missing just before second instance of YourField. YOU ARE THE BEST!!! – Luis Carvalho Jan 29 '14 at 12:55
  • glad it helped :) (edited my answer to fix the typo) – MdMazzotti Jan 29 '14 at 12:58
0

For this kind of manipulation of dates, I prefer using JavaScript.

Check this library of JQuery date.js. This library helps me a lot when it comes to manipulating dates.

You can do what you want in PreSaveAction SharePoint function example.

  • Can you please explain how would you do it in this case? I have no knowledge of javacsript. thanks. @diego hillesheim – Luis Carvalho Jan 29 '14 at 12:23
  • You have to sum ​​multiple of 10 or it may be that also subtract? – Diego Hillesheim Jan 29 '14 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.