Range of dates in an if calculation

[REGGIE MAGER]

Hi. I was wondering if someone could help with a question I have. I'm trying to do a calculation that needs to find a range of dates. If the date is between 1-1-2009 and 3-31-2009 then it needs to return a statement like "1-1-2009 through 3-31-2009" and then the if statement has to continue and do the same for April -Jun, Jul-Sept, and October-December. I've tried it, but everyting I try always ends up returning everything after 1-1-2009. Any help would be greatly appreciated.

[Lee Smith]

Do you know how to do a search for a Range?

[Raybaudi]

Case(

YourDateField ≤ Date ( 12 ; 31 ; 2008 ) ; "" ;

YourDateField ≤ Date ( 3 ; 31 ; 2009 ) ; "1-1-2009 through 3-31-2009" ;

YourDateField ≤ Date ( 6 ; 30 ; 2009 ) ; "4-1-2009 through 6-30-2009" ;

YourDateField ≤ Date ( 9 ; 30 ; 2009 ) ; "7-1-2009 through 9-30-2009" ;

YourDateField ≤ Date ( 12 ; 31 ; 2009 ) ; "10-1-2009 through 12-31-2009"

)

[Lee Smith]

Why not let them reply first?

[comment]

If I understand this correctly, you don't need an if statement:

Let ( [

m = Month ( YourDate ) ;

y = Year ( YourDate )

] ;

Date ( 3 *  Div ( m - 1 ; 3 ) + 1 ; 1 ; y )

& " through " &

Date ( 3 *  Div ( m - 1 ; 3 ) + 4 ; 0 ; y )

)

[Raybaudi]

Because the question isn't on searching in a range.

[Raybaudi]

What if y < 2009 or y > 2009 ?

[comment]

What is the question?

[Raybaudi]

If I understand correctly, she has many records with dates but wants that calc only for the year 2009.

[REGGIE MAGER]

Thank you for the help. That works great. And I learned a lot from your solution. It was frustrating because I thought I needed an If caluclation. This is so easy. Thanks again.

[Lee Smith]

:) oops, misread the need.

Lee

[LaRetta]

What if y < 2009 or y > 2009 ?

Simply wrap Comment's calc with an If() or Case () like:

[color:green]If ( Year ( YourDate ) = 2009 ;

Let ( [

m = Month ( YourDate ) ;

y = Year ( YourDate )

] ;

Date ( 3 * Div ( m - 1 ; 3 ) + 1 ; 1 ; y )

& " through " &

Date ( 3 * Div ( m - 1 ; 3 ) + 4 ; 0 ; y )

)

[color:green])

But it doesn't make much sense to limit the calc when it would automatically work whenever, whereever one would want it to, whenever viewed. It is obviously a calc for display purposes only.

Michael - I didn't mean to alter your calc which is perfect as it stands. I just wanted to produce an example of how; also indicating it wouldn't be necessary (from what I can tell).

Edited December 13, 2008 by Guest
Added sentence

[comment]

We build to last.

Now, seriously: if someone wanted a calculation that only returns a result for 2009, I would tell them to rethink their strategy. Because "2009" is data - and it doesn't belong in a calculation formula, which is part of the schema.

[LaRetta]

:waytogo: Exactly!

BTW, elegant calc!