# calculation between two dates

## Recommended Posts

I am trying to calculate a value as an "If" function but I can't seem to get it to work...

I am trying to create a calculation that will take in consideration the following.

If a "date_field" is between dates1 and dates 2, calculate result,

If a "date_field" is between Dates3 and Dates 4, calculate result.

I cannot seem to get this 2 work... I can view records in table based on a date from and to but cannot work out the calculation for this result..

TIA

##### Share on other sites

Does this work?

`Case (Date_Field ≤ Date1 and Date_Field  ≥  Date2;result1;Date_Field ≤ Date3 and Date_Field  ≥  Date4;result2;default result)`

##### Share on other sites

Does this work?

Case (Date_Field ≤ Date1 and Date_Field ≥ Date2 ; result1 ; Date_Field ≤ Date3 and Date_Field ≥ Date4 ; result2 ; default result)

I don't believe that fits the criteria of 'between'.

[color:gray]Date_Field ≤ Date1 and Date_Field ≥ Date2

To get between (inclusive), I would suggest reversing, as: Date_Field [color:red]≥ Date1 and Date_Field [color:red]≤ Date2 ; result1. I assume that's what you meant but I wanted to clarify. :wink2:

##### Share on other sites

Thanks jstaphse, and yes to clarify, and I always put these wrong way round first of all...

This works perfectly, thankyou.

##### Share on other sites

Thanks for correcting that LaRetta. I knew it was too early for that reply. ???

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...