Want a date range show a particular answer for that range and update as dates change

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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.

Thanks!





Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Eric

Eric

  • 40 Points
This is probably best looked at within the context of your application. Would you please enter a support case by clicking Help->Manage My Cases? One of our care reps can then work with you on this issue and help get a solution in place.
Photo of Cecelia

Cecelia

  • 180 Points 100 badge 2x thumb
deleted