6 Replies Latest reply on Jan 17, 2014 6:53 AM by philmodjunk

# Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

### Title

Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

### Post

Hi Smart People,

I am trying to create a calculation for benefits.  This benefit for this particular company begins after three months of their initial hire date.  Is there a way I can calculate this based on the hire date?

Something like:

IF( HireDate >= 3 months, Num1, Num2)

Thanks for the help!

Stephen

• ###### 1. Re: Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

Why the If function?

Date ( Month ( DateOfHire ) + 3 ; Day ( DateOfHire ) ; Year ( DateOfHire ) )

The date function will automatically adjust to compute correct dates for hire dates at the end of the year to produce dates in the start of the next year.

• ###### 2. Re: Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

Thanks PhilModJunk for the quick reply!  This does get me a step closer.  To answer your question, I'm not interested necessarily in the actual date but I want to return a value if the employee has exceeded three months of employment.

Can I use this function to do the following? (for example):

IF(TodayDate >= Date ( Month (DateOfHire) + 3 ; Day (DateOfHire) ; Year (DateOfHire)) , \$1000 , \$500)

Thanks!

• ###### 3. Re: Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

Use Get ( CurrentDate ) in place of "TodayDate" and this will work as a script step or as an unstored calculation field. I won't update if you use a stored calculation or auto-entered calculation.

• ###### 4. Re: Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

Thanks.  Here is a copy of my function below.  Whenever I run it, it returns the same 500 even if the hire date is within 3 months.

If ( Get ( CurrentDate )  ≥ Date ( Month ( DateOfHire ) ; Day ( DateOfHire ) ; Year ( DateOfHire ) ); 500 ; 250 )

Any suggestions?  The calculation is also unstored.

Thanks!

• ###### 5. Re: Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

Oh Nevermind!  Forgot to put the + 3!

It works now. Thanks for the help!

• ###### 6. Re: Date Calculation:  IF ( Date > 3 months, Num1 , Num2)

Make sure that DateOfHire is of type Date and has the correct date in it. If DateOfHire is a calculation field, make sure that "date" is selected as the result type.

You might put

Date ( Month ( DateOfHire ) ; Day ( DateOfHire ) ; Year ( DateOfHire ) )

into a calculation field and place it on your layout in order to check and see what date is actually being calculated.

If you have FileMaker advanced, you can put that expression in the data viewer instead of adding a new field and then you can check the value that it is calculating that way.