none
Custom field‏ formulas RRS feed

  • Question

  • Hello Everybody

    I would like to kindly ask you to help me with below question which relates to custom field formulas.
     
    I have already created formula in custom field for Text24 which calculates required hours according to numbers on Text23.
    Text24 = ([Text23]/25)*44
     
    I have also got digits which contains letter M (meter) in Text23. Is there any options to make formula where it will calculate digits with letter M?
     
    example Text24  = ([Text23 (M)]/60)*44 or Text23 contains "M" /60*44???
     
    Your help and support are really appreciated.

    Monday, May 16, 2016 10:56 AM

All replies

  • I don't think I understand your question.  Are you saying that the values in Text23 contain the letter "M" such as 24M? To the best of my knowledge, you cannot perform math in a Text field.  Even if Text23 contains only numbers Ii get an error with your formula entered into Text24.  
    Monday, May 16, 2016 1:07 PM
    Moderator
  • Dear Juile,

    First of all thank you very much for your reply.

    Actually when it shows only digits on Text23 then formula works fine.

    As I said above in current formula Text24 = ([Text23]/25)*44 I can get required hours for each task. In this example there is no any letters after numbers. However in other tasks I showed digits, numbers as 155M (which mean meter as you mentioned) and I want to use another calculation , so inside Text24 formula I should have something like below

    Text24 = ([Text23]/25)*44  AND ([Text23 ( some how we need to tell program to take into consideration where numbers has letter M)]/60)*44 

    Appreciated your help.

    Kindly

    Monday, May 16, 2016 1:22 PM
  • You need to use some of the text functions to trim the m off. EG:

    IIf(isnumeric([Text23]),[Text23]/25*44,Left([Text23],Len([Text23])-1)/25*44)


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Monday, May 16, 2016 8:24 PM
    Moderator
  • Good day Mr. Gill.

    Thanks a lot for your help and support.

    Unfortunately the rows show still #ERROR and previous calculation which I had now display only -1

    Kindly

    Wednesday, May 18, 2016 5:12 AM
  • can you share some examples of the contents of the referenced fields, exactly as they are (so for instance are there spaces or other 'hidden' things which could trip up the calculation?  

    This will help us identify the correct form as I for one certainly expected Rod's calc to work :)

    Regards

    Miles

    Wednesday, May 18, 2016 6:30 AM
  • Dear Miles,

    Seems like you are right there is one hidden row where I used to have original amount of wrok. See example below

    ACTIVITY NAME PIPE CODE UNIT/AMOUNT % Complete Update Amount AMOUNT SYSTEM HDO DATE EM4 REQ HOURS
    GM NI-STL 1 88% NA
       WELDED JOINTS CS 153 EA 84% 74 153 EA 30 Oct '15 130.24
       PIPELINE CS 315.2 m 84% 271.58M 315.2 m 30 Oct '15 #ERROR
       PIPE SUPPORTS CS 134 EA 95% 110 134 EA 30 Oct '15 193.6

    NOTE : Updated amount TEXT23

                REQ HOURS TEXT 24

     Thanks a lot in advance

    Wednesday, May 18, 2016 6:55 AM
  • If 271.58M has a leading or trailing space then replace

    [Text23]

    with

    Trim([Text23])

    or even:

    IIf(isnumeric([Text23]),Csng([Text23])/25*44,Csng(Left(Trim([Text23]),Len(Trim([Text23]))-1))/25*44)


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Wednesday, May 18, 2016 11:08 PM
    Moderator