Hi guys!
I need help figuring this one out. I was approaching the problem using MAX and MIN formulas, but that doesn’t help me sorting the 5 possible dates I have (just 2).
Problem goes like this: I’m working a logistic solution. The operator works by areas, visiting a zone one day of the week. But during peak seasons, he may decide to visit the area every day of the week (hence 5 possible days). My table calculates the next possible visit day from the autodate it assigns when a new request of service is submitted. So, if the order is posted on a monday and the operator visits the client’s area on tuesday, is the next day. But if it’s on mondays, is 7 days from the request date.
I hope you’re following me. Calculating which date it will be for that area is easy, with:
IF({retiro 1}-WEEKDAY(Autofecha)<=0,DATEADD(Autofecha,{retiro 1}-(WEEKDAY(Autofecha))+7,‘days’),DATEADD(Autofecha,{retiro 1}-(WEEKDAY(Autofecha)),‘days’)).
where ‘Autofecha’ is request day, and ‘retiro 1’ is the day of the week where the area related to the request is visited. Same formula is used to calculate up to 5 possible days for the area to be visited (Mon to Fri). It sound a bit absurd but when the area is visited just one or two days per week (p.e. Tue-Fri), is really different if the request date is before, in between or after those dates.
So, The tricky part is to know, out of 5 possible dates, which date is the first, which the second… and so on. Not just the first and last, but all of them. I’d like to have a formula for sorting the first (could be MIN), the second (no idea), third, fourth, fifth (could be MAX).
Has anyone figure out something like this yet?
I think this could work:
IF(IF(XOR(BLANK(),{retiro # 1},{retiro # 2})=1,"",IF(OR({retiro # 2},{retiro # 1})=1,IS_BEFORE({retiro # 1},{retiro # 2}),"")),{retiro # 1},{retiro # 2})
BUT… I just copied the formula and don’t really get how to make it work for 5 days.
Help people! THANKS <3