Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Ok I have this frustrating task I hope you can help solve:
Known informatoin:
Startdate (presented by red star)
Enddate (presented by the lightning) --> its the startdate + 18 months
Event dates: 1 march and 1 september, every year.
Given any startdate, how do I find the closest future event date?
I created a small picture for visualization:
What I have tried so far:
Lightningdate = AddMonths(Startdate, 18)
Thanks for the constructive answers Peter and Rob!
The following gave me the desired result.
if(AddMonths(myDate, 18) > MakeDate(Year(AddMonths(myDate, 18)),9,1), MakeDate(Year(AddMonths(myDate, 18)+1), 3, 1)
,if(AddMonths(myDate, 18) >= MakeDate(Year(AddMonths(myDate, 18)),3,1), MakeDate(Year(AddMonths(myDate, 18)), 9, 1)
,MakeDate(Year(AddMonths(myDate, 18)), 3, 1)
))
Maybe like this: Min( If(EventDate>AddMonths(Startdate,18),EventDate))
If the event dates are fixed at March 1 and Sept 1, it seems that finding the next future event from any date is:
if(myDate > MakeDate(Year(myDate),9,1), MakeDate(year(myDate)+1, 3, 1)
,if(myDate > MakeDate(Year(myDate),3,1), MakeDate(year(myDate), 9, 1)
,MakeDate(year(myDate), 3, 1)
))
-Rob
Does the closest future event date have to be completely outside of the period between StartDate and EndDate? Your drawing seems to imply as much.
If so, replace all occurrences of myDate in Rob's formula with the expression you made yourself: AddMonths(StartDate, 18)
If you have a list of EventDates somewhere in your data model, Gysbert's solution will do wonders.
Thanks for the constructive answers Peter and Rob!
The following gave me the desired result.
if(AddMonths(myDate, 18) > MakeDate(Year(AddMonths(myDate, 18)),9,1), MakeDate(Year(AddMonths(myDate, 18)+1), 3, 1)
,if(AddMonths(myDate, 18) >= MakeDate(Year(AddMonths(myDate, 18)),3,1), MakeDate(Year(AddMonths(myDate, 18)), 9, 1)
,MakeDate(Year(AddMonths(myDate, 18)), 3, 1)
))