Designating a specific date range with a single value

[former_member1109494]

Hi,

I am a beginner at Business Objects and I'm trying to do two things. First, I am trying to format date to a specific format without the time stamp and then I am trying to designate three specific date ranges each with it's own date value. For date range 1/1/2019 through 4/30/2019, I'd like to designate a value of "01/01/2019" for this range. For 5/1/2019 through 7/31/2019, I'd like to use "05/01/2019" and for 8/1/2019 through 12/31/2019 I'd like to use "08/01/2019".

This is what I entered for the first value.

=IF (FormatDate(ToDate([Order Date];"yyyyMMdd") Between ("20190101";"20190430");"01/01/2019"))

I'm getting an error for missing arguments or closing parenthesis at position 93. When I try to address the error, I get a new error usually regarding a semicolon.

Any help is greatly appreciated.

-K

[denis_konovalov]

As you haven't mention what actual tool you are using, I have fixed your tags to better much general nature of your question.

[amitrathi239]

Between and formatdate() functions will not worktogether in the formula.

try to create similar formula as per below.

=If(MonthNumberOfYear([Order Date]) InList (1;2;3;4) ) Then "01/01/2019"

if order date is not date data type then first convert to date and then use.

[former_member1109494]

I created a new dimension for the reformatted date and used If and ElseIf statements for the specific date ranges and it worked.

Thanks for your help!

[daniele_tiles2]

Hi Kane,

in the formula you're missing a parenthesis before "between", to close the format date:

=IF (FormatDate(ToDate([Order Date];"yyyyMMdd") <here missing ) for closing format date > Between ("20190101";"20190430");"01/01/2019"))

Not sure it'll work either (can't test it right now), but it might be. Maybe you can avoid the ToDate and just use the format date?

HTH

Daniele