6 Replies Latest reply on Mar 22, 2017 11:10 AM by Jamieson Christian

# Need help with a new calculation with date fields

I am trying to return our fiscal period and fiscal year based on a date field.

Our Fiscal Period table is set up as follows

Fiscal Period Year     Start Date                    End Date

1   -       2014               01/04/2014               02/02/2014

2    -      2014               02/03/2014               03/03/2014

3    -      2014               03/04/2014               04/01/2014

4    -      2014               04/01/2014               04/29/2014

x    -      x                          x                               x

x    -      x                          x                               x

and so on

I am using the following formula. I am getting Nulls returned and I understand why but I am not sure how to modify the formula to get it to work correctly.

if [Post Date]> [Start Date] and [Post Date] < [End Date]

then [Fisc Year Per]

else null

END

• ###### 1. Re: Need help with a new calculation with date fields

Kevin,

It looks like the ranges in your reference table are inclusive, so your formula probably needs to look like this instead:

if [Post Date] >= [Start Date] and [Post Date] <= [End Date]

then [Fisc Year Per]

else null

END

• ###### 3. Re: Need help with a new calculation with date fields

I added the workbook to the original post. Thanks

• ###### 4. Re: Need help with a new calculation with date fields

I apologize. I should have had that originally. However, if adding the greater than or equal to and the less than and equal to didn't change the issue.

• ###### 5. Re: Need help with a new calculation with date fields

Kevin,

You said that you understand why you are getting nulls - can you explain that to us?  And you said that you do not know how to make the formula work correctly - does working correctly mean that you would get rid of the nulls? or does it mean something else?  It would be helpful if you could show us an image of the results you are currently getting, and a mocked-up image of what you expect the result to look like.

• ###### 6. Re: Need help with a new calculation with date fields

Kevin,

It looks like the packaged workbook you posted still has a live connection to your data source, which means we can't review it.

For workbooks that connect to a database, you must first create an extract of the data, and then you can create the packaged workbook (it will embed the extract).

For more information, please see this help article: Packaged Workbooks  (see section "Create a .twbx with non-file-based data sources")