Ok so I have this multiple step query.
I have a title with 3 different start and end dates. This is when it tells me it is in and out of sale. I use this to set up my metadata for the title in my work systems. I have created date fields to reflect this:
- VOD 1 Start
- VOD 1 End
- VOD 2 Start
- VOD 2 End
- VOD 3 Start
- VOD 3 End
To add a tricky element I need the start dates to display with yyyy-mm-dd. So I created 3 fields called VOD 1 Start- Display (same for VOD 2 and 3) using this formula.
var text YYYY = ToText(Year([VOD 1 Start]));
var text MM = Right("0" & ToText(Month([VOD 1 Start])),2);
var text DD = Right("0"&ToText(Day([VOD 1 Start])),2);
If(not IsNull([VOD 1 Start]),
$YYYY & "-" & $MM & "-" & $DD)
So far so good. Now the tricky part.
So the formula field I am building is called Copy of Rental Start Date. This needs to display either VOD 1 Start- Display / VOD 2 Start-Display/ or VOD 3 Start-Display base on the current date range.
· If VOD 1 Start and End is during the current date range, display VOD 1 Start- Display
· If VOD 2 Start and End is during the current date range, display VOD 2 Start-Display
· If VOD 3 Start and End is during the current date range, display VOD 3 Start-Display
So if VOD 1 Start is Sept 1, 2015 and VOD 1 End is September 30, 2015 and today is September 15, 2015 I want it to display the date in VOD 1 Start- Display
I tried this text formula to get started but I am not having luck
Case(Max(If(Today()>=[VOD 1 Start] and Today()<=[VOD 1 End],[VOD 1 Start- Display]),
If(Today()>=[VOD 2 Start] and Today()<=[VOD 2 End],[VOD 2 Start- Display]),
If(Today()>=[VOD 3 Start] and Today()<=[VOD 3 End],[VOD 3 Start- Display]))
Additionally to add to this, if VOD 1 End date is past its date, I want it to display what is entered in the VOD 2 Start-Display field, even if it is not in range yet. Same with VOD 2 End. If it is past its date, I want it to display what is in VOD 3 Start- Display.
And just to complicate it further if VOD 2 Start –Display and/or VOD 3 Start- Display are blank, then it should default to date 2099-01-01
I hope you can help.