Date Range Formula

[mvalley]

I have a report that has a field,(cr_prdate) that may have a single date or a date range.

How would I create a formula that would display the date range of the records I am displaying (ie for single date 1/2/2015 or for weekend from 1/2/2015 - 1/4/2015) as a header formula.

Thanks for any suggestions offered.

 

[kray4660]

Normally I have two formulas one - i.e., minimum({cr_prdate}) the second one maximum({cr_prdate}). Stick these two formulas in a text box with my other header text.

I hope this helps.

 

[mvalley]

Works great thank you. My only issue is to create a formula that would display only the Minimum date if the minimum and maximum dates are the same (example, both dates would be 01/02/2015 verses 01/02/2015 - 01/04/2015. I think it would be and if, then,else formula, but I am having trouble getting it to work.

 

[kray4660]

if minimum({cr_prdate}) = maximum({cr_prdate}) then

minimum({cr_prdate})​

else

minimum({cr_prdate})+ ' - ' +maximum({cr_prdate})​

I would believe this would work.

 

[mvalley]

I too thought this would work, but I am getting an error message saying that a number is required
where'-' is in the formula. Still trying other options. Thank you

 

[AndyMc]

After the else, try replacing the + with & :

minimum({cr_prdate}) & ' - ' & maximum({cr_prdate})

 

[mvalley]

Still not working, error message now shows the entire string, minimum({cr_prdate}) & ' - ' & maximum({cr_prdate}) with the message: "a date time is required

 

[BettyJ]

Try
if minimum({cr_prdate}) = maximum({cr_prdate}) then
totext(minimum({cr_prdate}))
else
totext(minimum({cr_prdate}))+ ' - ' +totext(maximum({cr_prdate}))

 

[mvalley]

Thank you, but I am getting the error message on the ' - ' that a number is required here. Sorry this is so frustrating!!

 

[BettyJ]

Did you add totext twice in the last line (to both mimimum and maximum)? I believe it should work.
Try & instead of +

 

[mvalley]

Thank you all for your assistance. BettyJ's suggestion of the ToText and changing + to & created an error free formula. Always appreciate the wonderful, and quick assistance I receive from this forum. Mary

 

[BettyJ]

Can you check and see if it works if you change & to +.

 

[kray4660]

Brain fart earlier, I knew I needed the totext, sorry I did not catch it before I posted.