Fine-tune Date Calculation Range

[LaRetta]

Hi everyone,

Although I use GTRR for most date ranges, occasionally I need to perform finds (example, script for finding HireDate range) on smaller tables. I have about 15 situations in which I use this similar calc 'thinking'. I want them to be the best possible. Here are my two attempts:

Fields (all date)

DateFrom

DateThru

gFiscalStart (global pre-filled at host with our fiscal start)

gFiscalEnd (global pre-filled at host with our fiscal end)

My first calc (clunky)

Case(

not DateFrom and DateThru; gFiscalStart & "..." & DateThru;

DateFrom and not DateThru; DateFrom & "..." & gFiscalEnd;

not DateFrom and not DateThru; gFiscalStart & "..." & gFiscalEnd;

DateFrom & "..." & DateThru

)

Fine-tuned calc (I think, although it doesn't appear to help much):

Case(

DateFrom;

If(DateThru; DateFrom & "..." & DateThru; DateFrom & "..." & gFiscalEnd);

not DateFrom;

If(DateThru; gFiscalStart & "..." & DateThru; gFiscalStart & "..." & gFiscalEnd)

)

But I keep wondering whether Let() can be used or simply a rearrangement of the calc. This is only an example and changes as my needs change but I'm hoping you can improve my thinking on it. Can this be improved even more?

LaRetta

[comment]

Your fine-tuned calc is not equivalent to your first calc. Also, I find mixing Case() and If() confuses me - but that is a personal thing. Anyway, how about:

Case ( DateFrom ; DateFrom ; gFiscalStart )

& ".." &

Case ( DateThru ; DateThru ; gFiscalEnd )

[LaRetta]

Comment said ... Your fine-tuned calc is not equivalent to your first calc

Yes, I had changed it attempting to come up with a perfect example - sorry about that.

Yep!! Perfect and very very nice! And so simple, Michael. I can't believe I couldn't think of that!

I'm taking the day off and reviewing some of my calcs ... you know ... the ones that I've come up with by myself and I get an uneasy feeling every time I see them.

Thank you!!

LaRetta

[comment]

Why not just take a day off, period.

[LaRetta]

I actually slept in today! First time in 19 months! Slept 9 hours straight!! It was GLORIOUS!!

And for the first time in months, I'm able to study and work in FM without pressure to upload so it can be used immediately - that IS vacation, my friend. Plus took Ginger dawg for a hike and went swimming! Oh, and I'm going to sit down and watch XFiles - only show I like! Life is indeed wonderful!

LaRetta