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

Ask the community Formulas
#1

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

#2

Hi @Vik_Arrieta,

Welcome to Airtable Community ! :smiley:

Well, what you are trying to do seems a bit complicated. Im assuming you have 2 tables, one of them is the schedule of the visits and one is the requests.

So, in the Schedule Table, you have the Area by Days (you visit Area 1 on Tuesdays, Area 2 on Mondays and Wednesdays, and so on).

I think the problem is that you are using the day not the date, you need to use the dates.

Your best option would be the script block.

BR,
Mo

#3

Hi Mo!

Thanks for taking an interest in this topic! :slight_smile:
Actually no, I’m using DATES because after I have a “request date” I can transform days into nearests dates that match those days. So is actually DATES what I’m seeking to compare.

Maybe I can use a IF formula matching if a DATE in a unix or ‘YYYYMMDD’ format is > than all the other 4 dates (using OR). And then nest the next possible option. I need to write it, if it works I’ll post it.

I should use “less or equal” actually. I need to find that character.

But still then, I need to change the formula to have the DATE that is nor the nearest but the second nearest (second >) as a solution. And I don’t see how to make that possible. A SORT function should be available for comparing data in a row.

#4

Hi Vik,

For the less than or equal you can use <=

#5

Hi @Vik_Arrieta,

I think @Mohamed_Swellam is likely hitting the mark with this:

But I’ve read both your posts a couple times now and I’m still trying to figure out what exactly you want as the output for this process (whether it be a formula in a formula field, or a script in a Scripting block).

I am gathering that you have Zones, and you have Requests for Service on those Zones.

A Request for Service will be for a particular Date.

Is your goal for this formula/script that it will find the next best day for a Visit to this Zone, based on the Request Date? Or is your goal to track number of days between a Request Date and a Visit to a Zone? Or something else that I’m missing entirely?

I’m not sure I see what you’re after.

#6

Hi Jeremy and Mo!

I think you’re probably right about using a script block, but I’m trying to avoid going PRO for this project (current situation ask for minimum costs).

MY GOAL: to find out the first possible date for picking up the package, and then the second possible date (in case first date is a miss). These dates could be the next monday and wednesday from a “Request date”. That is already calculated from an autodate stamp and the days the service visits the zone from the client.

I rewrite the formula and I works fine to deliver the first date possible for pick up/delivery. What I’m missing is how to calculate the second date… my brain hurts.

Here’s the formula I’m using, where {retiro 1…5 value} is the date as a numeric value (YYYYMMDD+0), and {retiro # 1…5} is the actual date (then some formatting to match our language):

DATETIME_FORMAT(
IF(
AND({retiro 2 value},{retiro 3 value},{retiro 4 value},{retiro 5 value}<={retiro 1 value}),
IF(
AND({retiro 3 value},{retiro 4 value},{retiro 5 value}<={retiro 2 value}),
IF(
AND({retiro 4 value},{retiro 5 value}<={retiro 3 value}),
IF({retiro 5 value}<={retiro 4 value},
{retiro # 4},{retiro 5 value}),{retiro # 3}),{retiro # 2}),’{retiro # 1}’),‘DD/MM/YYYY’)

If you have any ideas, I’ll be happy to test them!
THANKS for taking an interest in this topic :slight_smile:

#7

With this part, are you trying to compare each of those ({retiro 2 value}, {retiro 3 value}, etc…) to see if it is <= {retiro 1 value}? Or are you only comparing 5 to 1?

#8

Hi @Vik_Arrieta – I made some minor adjustments to your formula. Can you try this and see if it gets you any closer to what you are looking for?

DATETIME_FORMAT(
  IF(
    AND(
      {retiro 2 value} <= {retiro 1 value},
      {retiro 3 value} <= {retiro 1 value},
      {retiro 4 value} <= {retiro 1 value},
      {retiro 5 value} <= {retiro 1 value}
    ),
    IF(
      AND(
        {retiro 3 value} <= {retiro 2 value},
        {retiro 4 value} <= {retiro 2 value},
        {retiro 5 value} <= {retiro 2 value}
      ),
      IF(
        AND(
          {retiro 4 value} <= {retiro 3 value},
          {retiro 5 value} <= {retiro 3 value}
        ),
        IF(
          {retiro 5 value} <= {retiro 4 value},
          {retiro # 4},
          {retiro # 5}
        ),
        {retiro # 3}
      ),
      {retiro # 2}
    ),
    {retiro # 1}
  ),
  'DD/MM/YYYY'
)
#9

THANKS Jeremy!!
That’s right, I was using the AND formula all wrong. Now it works perfectly. I don’t know why it worked before too, but now it makes perfect sense.

So… I think with this structure I could compare the result of this formula to all of the possible dates again to find one that is not equal nor less and not more than other value? I’m guessing it will be a pretty long formula but possible. Now that I have the correct syntax. :smile:

Thanks again Jeremy!

1 Like
#10

I’m sorry to tell you the formula is not working. It always retrieves the top FALSE option. Any idea why? The data that is comparing is in integer format, different numbers, not being the top FALSE option the smaller integer of them all.

This is killing my neurons.

#11

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')
#12

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.

#13

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'))
#14

@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?

1 Like
#15

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

#16

@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…).