Need help on a syntax error

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

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)

Photo of Krystal Goulet

Krystal Goulet

  • 120 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
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)
Photo of Krystal Goulet

Krystal Goulet

  • 120 Points 100 badge 2x thumb

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)
Photo of Krystal Goulet

Krystal Goulet

  • 120 Points 100 badge 2x thumb
Same error. :(
Please post your formula and the error.
Photo of Krystal Goulet

Krystal Goulet

  • 120 Points 100 badge 2x thumb

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.
Photo of Krystal Goulet

Krystal Goulet

  • 120 Points 100 badge 2x thumb
Yes, it is a formula - numeric field type.
Photo of Krystal Goulet

Krystal Goulet

  • 120 Points 100 badge 2x thumb

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

Exactly.  Well done!