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

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

2 Answers 2

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)

4
  • YourField in this case would be the column with 2014-NNNN/XXT ? is this correct? Commented Jan 29, 2014 at 12:48
  • @LuisCarvalho yes, correct
    – MdMazzotti
    Commented Jan 29, 2014 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!!! Commented Jan 29, 2014 at 12:55
  • glad it helped :) (edited my answer to fix the typo)
    – MdMazzotti
    Commented Jan 29, 2014 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.

2
  • Can you please explain how would you do it in this case? I have no knowledge of javacsript. thanks. @diego hillesheim Commented Jan 29, 2014 at 12:23
  • You have to sum ​​multiple of 10 or it may be that also subtract? Commented Jan 29, 2014 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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