Sorting 5 workday dates on columns to retrieve the first one, second one, and so on

[Vik_Arrieta]

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 ❤️

[Vik_Arrieta]

Hi @Jeremy_Oglesby! I thought it worked, but it doesn’t. Is always retrieving the FALSE option (the first IF formula FALSE option, the last one in the string “retiro #1”).

Can you see what’s wrong? I cannot! :grimacing: Thanks for your help!!

DATETIME_PARSE(IF(
    AND(
      {retiro #2} <= {retiro #1},
      {retiro #3} <= {retiro #1},
      {retiro #4} <= {retiro #1},
      {retiro #5} <= {retiro #1}
    ),
    IF(
      AND(
        {retiro #3} <= {retiro #2},
        {retiro #4} <= {retiro #2},
        {retiro #5} <= {retiro #2}
      ),
      IF(
        AND(
          {retiro #4} <= {retiro #3},
          {retiro #5} <= {retiro #3}
        ),
        IF(
          {retiro #5} <= {retiro #4},
          {retiro #4},
          {retiro #5}
        ),
        {retiro #3}
      ),
      {retiro #2}
    ),
    {retiro #1}
  ),
'YYYYMMDD')

[Jeremy_Oglesby]

Are you saying that you are ALWAYS getting the value of {retiro #1} back from this formula? If so, are you sure it’s not your data? I don’t think I see anything wrong with the formula.

[Jeremy_Oglesby]

Hey Vik,

So I read back through the posts above, and it looks like you are probably holding DATE values in these {retiro # x} fields – is that right? Are they formatted as DATE fields?

If so, Vik, then you may not be able to just compare them with math operators like that. Try using the IS_BEFORE() date function instead.

So anywhere in your function that you are comparing two {retiro # x} fields with <=, try replacing it with this:

IS_BEFORE({retiro #2}, {retiro #1})

Another possibility is that you are holding just date STRINGS in these fields. If that is the case, then you need to make the extra step of converting each one of these into a proper date before making the comparison by using DATETIME_PARSE() on it:

IS_BEFORE(DATETIME_PARSE({retiro #2}, 'YYYYMMDD'), DATETIME_PARSE({retiro #1}, 'YYYYMMDD'))

[Vik_Arrieta]

@Jeremy_Oglesby I just figured it out!! I was comparing VALUES, so I just needed to use de MIN formula. As plain and simple as that. 5 values, which is the smaller one. MIN formula.

To have the next or second MIN value, I guess I’ll need to extract that first MIN value from the 5 values string and then calculate again the MIN value from the remaining 4 values.

That makes sense to you?

[Jeremy_Oglesby]

Nice work! Definitely more efficient than what I was proposing! That sounds like it makes sense.

[Jeremy_Oglesby]

@Vik_Arrieta

So in your first workday date field, you’ll have all 5 in the MIN() function like this:

DATETIME_PARSE(
   MIN({retiro #1}, {retiro #2}, {retiro #3}, {retiro #4}, {retiro #5}),
   'YYYYMMDD'
)

In the second workday date field, as you say, you’ll need to extract the value you’ve already used in the first one:

DATETIME_PARSE(
   MIN(
      IF({first workday field} != {retiro #1}, {retiro #1}),
      IF({first workday field} != {retiro #2}, {retiro #2}),
      IF({first workday field} != {retiro #3}, {retiro #3}),
      IF({first workday field} != {retiro #4}, {retiro #4}),
      IF({first workday field} != {retiro #5}, {retiro #5})
   ),
   'YYYYMMDD'
)

those IF() functions might return 0 on a “false”, which would end up being a minimum value, which you don’t want… if that’s the case, then just add a “false” condition in them with a really big number in it that could never be the minimum, like this:

IF({first workday field} != {retiro #2}, {retiro #2}, 99999999)

In the third and beyond workday date fields, you’ll have to use a complex AND() condition to check that the {retiro #x} value isn’t in any of the previous workday date fields:

DATETIME_PARSE(
   MIN(
      IF(AND({first workday field} != {retiro #1}, {second workday field} != {retiro #1}), {retiro #1}),
      IF(AND({first workday field} != {retiro #2}, {second workday field} != {retiro #2}), {retiro #2}),
      IF(AND({first workday field} != {retiro #3}, {second workday field} != {retiro #3}), {retiro #3}),
      IF(AND({first workday field} != {retiro #4}, {second workday field} != {retiro #4}), {retiro #4}),
      IF(AND({first workday field} != {retiro #5}, {second workday field} != {retiro #5}), {retiro #5})
   ),
   'YYYYMMDD'
)

Try that and see if it works to get you all 5 dates sorted in the proper order (that last formula is going to end up with 4 conditions inside the AND() conditional, so it will end up pretty big…).