Skip to main content
Question

Need help on a syntax error

  • January 24, 2018
  • 0 replies
  • 17 views

Hey all,

I hope someone can help me. I'm trying to create a numeric formula that will calculate the amount of PTO available to my employees based upon their hire date, but I keep getting a syntax error when I input the formula. Can someone please look at this and see where I am going wrong?


Thanks!


Case(year(today())-year([hire date]),
   >=9,"200",
   >=3,"160",
   =2,"120",
   =1,Month(Today())+1,
   =0,Sum(month(today())-Month([hire date]))*10)

    This topic has been closed for replies.

    Try this

    IF(
    year(today())-year([hire date]) >=9,"200",
    year(today())-year([hire date]) >=3,"160",
    year(today())-year([hire date])  =2,"120",
    year(today())-year([hire date])  =1,Month(Today())+1,
    year(today())-year([hire date])  =0, (Month(today())-Month([hire date]))) * 10)
    • KrystalGoulet
    • Author
    • Registered
    • January 24, 2018

    That exact formula gives me a syntax error saying that there are extra characters at the end of the function. I tried:

    IF(
    year(today())-year([hire date]) >=9,"200",
    year(today())-year([hire date]) >=3,"160",
    year(today())-year([hire date])  =2,"120",
    year(today())-year([hire date])  =1,Month(Today())+1,
    year(today())-year([hire date])  =0, (Month(today())-Month([hire date]) * 10))

    and get that there are not enough arguments for an If function, so I tried this:

    IF(
    year(today())-year([hire date]) >=9,"200",
    year(today())-year([hire date]) >=3,"160",
    year(today())-year([hire date])  =2,"120",
    year(today())-year([hire date])  =1,Month(Today())+1,
    year(today())-year([hire date])  =0, (Month(today())-Month([hire date]) * 10),0)

    And I'm still getting Formula error - bad or missing arguments in function call.



    Try changing the last line to this

    year(today())-year([hire date])  =0, (Month(today())-Month([hire date])) * 10)
    • KrystalGoulet
    • Author
    • Registered
    • January 24, 2018
    Same error. :(
    Please post your formula and the error.
    • KrystalGoulet
    • Author
    • Registered
    • January 24, 2018

    If(
    Year(Today())-Year([Hire Date]) >=9,"200",
    Year(Today())-Year([Hire Date]) >=3,"160",
    Year(Today())-Year([Hire Date])  =2,"120",
    Year(Today())-Year([Hire Date])  =1,Month(Today())+1,
    Year(Today())-Year([Hire Date])  =0, (Month(Today())-Month([Hire Date])) * 10)

    Formula error -- Bad or missing arguments in function call

    The types of the arguments or the number of arguments supplied do not meet the requirements of the function If.

    The function is defined to be If (Boolean condition1, result1, ..., else-result).


    OK, well that in fact was not the same error.

    But I see the problem.

    What field type is this.  Is it a numeric formula numeric field type?  If so then we need to make the formula calculate to a number and not text.
    • KrystalGoulet
    • Author
    • Registered
    • January 24, 2018
    Yes, it is a formula - numeric field type.
    • KrystalGoulet
    • Author
    • Registered
    • January 24, 2018

    So by removing the quotations around the numeric results it worked! Thank you for your help!

    Exactly.  Well done!
    Terms & ConditionsAccessibility statement