Jump to content
Sign in to follow this  
Ben Ball

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
Share on other sites

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

LaRettas adjustment is correct...

This works perfectly, thankyou.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.