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

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.