Creating Custom Ranges

building reports desktopintelligence
1

I’m trying to create custom year ranges that span from 09/01-08/31. So for example, I want 09/01/2018-08/31/2019 to be 2018-2019 and 09/01/2017-08/31/2018 to be 2017-2018.

My formula looks like this:

=If(FormatDate([Academic Year View].[Job Effdt];"MM/dd/yyyy")>="09/01/2018" And FormatDate([Academic Year View].[Job Effdt];"MM/dd/yyyy")<="08/31/2019";"2018-2019";0)

This results in 0 for all dates regardless of whether they are in that range or not.

I tried to make it simpler by using

=If(FormatDate([Academic Year View].[Job Effdt];"MM/dd/yyyy")>="09/01/2018";1;0)

This returned 0’s for all dates between 09/01 and 12/31 and seemed to ignore the year completely.

Any help is greatly appreciated.

avaleska (BOB member since 2018-12-03)

2

To make it work for all years use the following

=If(MonthNumberOfYear([MyDate]) < 9)
Then FormatNumber(Year([MyDate]) - 1;"0000") + "-" + FormatNumber(Year([MyDate]);"0000")
Else FormatNumber(Year([MyDate]);"0000") + "-" + FormatNumber(Year([MyDate]) + 1;"0000")

buzz :australia: (BOB member since 2005-08-12)

3

This did the trick.

Thank you!

avaleska (BOB member since 2018-12-03)