Formula listing ALL dates between between {Start Date} field & {End Date} field

Nick_Robeson · ‎Aug 26, 2022

Hi all,

Hoping someone can help with a solution! My company has numerous assets that are “active” for long periods of time. I have a formula to calculate the “Active Status” based on the Asset Launch Date & Asset End Date (see below for reference).

I would like to make an additional field that lists ALL dates an asset is going to be active based on the start and end dates (placeholder included in the image above). For example, if Asset Launch Date = 9/1/22 & Asset End Date = 9/5/22, I’d like the ALL ACTIVE DATES field to read: “9/1/22, 9/2/22, 9/3/22, 9/4/22, 9/5/22”

Note: This field would be hidden 99.9% of the time and ONLY used in an Interface Filter element to allow users to type in specific dates (i.e. 9/3/22) and see the assets that will be “Active” on that date.

If there is another solution you can think of, feel free to leave it here as well!

Thank you in advance!

kuovonne · ‎Aug 26, 2022

If you want to do this with a formula field, there is a limit to the number of possible dates.

This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.

REGEX_REPLACE(CONCATENATE(
  DATETIME_FORMAT(
    {Asset Launch Date}, 
    "M/D/YY"
  ) & ", ",
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 1, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 2, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 3, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 4, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 5, 'day'),
        "M/D/YY"
    ) & ", "
  )
), ", $", "")

See Solution in Thread

kuovonne · ‎Aug 26, 2022

If you want to do this with a formula field, there is a limit to the number of possible dates.

This formula gives you starting point. You can extend it as needed, but as you can see, it can only handle a set maximum number of dates.

REGEX_REPLACE(CONCATENATE(
  DATETIME_FORMAT(
    {Asset Launch Date}, 
    "M/D/YY"
  ) & ", ",
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 1,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 1, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 2,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 2, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 3,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 3, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 4,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 4, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({Asset End Date}, {Asset Launch Date}, 'days') >= 5,
    DATETIME_FORMAT(
        DATEADD({Asset Launch Date}, 5, 'day'),
        "M/D/YY"
    ) & ", "
  )
), ", $", "")

kuovonne · ‎Aug 26, 2022

You could have filter conditions based on “is after” the launch date and “is before” the end date. The user would have to enter dates in both filter conditions, but it is also allows for more flexible searching.

Nick_Robeson · ‎Aug 26, 2022

This is exactly what I was looking for! I really appreciate your help and everything you do for the Airtable community!

Nick_Robeson · ‎Aug 26, 2022

Tried implementing this (as well as a timeline view), but the individuals who wanted the functionality thought it was “too much work” :joy:

Summer_Schedule · ‎Jan 28, 2023

I came across this looking for something similar. I am using Airtable to schedule people for different assignments over a period of 4 months. Some people might have the same assignment for the whole time, others will receive many shorter assignments. I am creating an interface for the person who does the scheduling, and want her to be able to use a filter to search for "all people who are unassigned" on a particular day. It seems to me that I would need to use the formula you shared @kuovonne, do a rollup so that all the dates for all assignments would show in one field, and then use the filter in the interface to search for anybody who does not have a particular date in their rollup.

How far can I stretch that formula you shared - meaning how many days can I use it for?

Do you see a better way to achieve what I am trying to do?

Thank you!

kuovonne · ‎Jan 28, 2023

I do not think that this formula will work for your use case. As you can see, each additional day requires seven more lines to the formula, so if someone has a single assignment for four months, the formula would need over 840 lines. Even if you could have such a monstrous formula, you would need to do a filtered search for where the formula result does not include the string for the desired date, and the date would need to be typed exactly. And that would only tell you if the person was available that single day, not other days for which the assignment lasts.

In general, Airtable does not handle looking for "unassigned" time slots well without scripting.

isabelgee · ‎Oct 20, 2023

Hello! Would it be possible to create a calendar based on those fields in between? We are trying to a time and attendance and would like to see who is off on certain days. Thank you!

Andrew_Percy · ‎Feb 21, 2024

Brilliant @kuovonne - thank you for doing the leg work.

For anyone else looking for an extension of this formula that would cover a typical vacation of up to 24 days here's the expansion, using fields simply named Start and End:

REGEX_REPLACE(CONCATENATE(
    DATETIME_FORMAT(
      Start, 
      "M/D/YY"
    ) & ", ",
    IF(
      DATETIME_DIFF(End, Start, 'days') >= 1,
      DATETIME_FORMAT(
          DATEADD(Start, 1, 'day'),
          "M/D/YY"
      ) & ", "
    ),
    IF(
      DATETIME_DIFF(End, Start, 'days') >= 2,
      DATETIME_FORMAT(
          DATEADD(Start, 2, 'day'),
          "M/D/YY"
      ) & ", "
    ),
    IF(
      DATETIME_DIFF(End, Start, 'days') >= 3,
      DATETIME_FORMAT(
          DATEADD(Start, 3, 'day'),
          "M/D/YY"
      ) & ", "
    ),
    IF(
      DATETIME_DIFF(End, Start, 'days') >= 4,
      DATETIME_FORMAT(
          DATEADD(Start, 4, 'day'),
          "M/D/YY"
      ) & ", "
    ),
    IF(
      DATETIME_DIFF(End, Start, 'days') >= 5,
      DATETIME_FORMAT(
          DATEADD(Start, 5, 'day'),
          "M/D/YY"
      ) & ", "
    ),
    IF(
        DATETIME_DIFF(End, Start, 'days') >= 6,
        DATETIME_FORMAT(
            DATEADD(Start, 6, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 7,
        DATETIME_FORMAT(
            DATEADD(Start, 7, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 8,
        DATETIME_FORMAT(
            DATEADD(Start, 8, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 9,
        DATETIME_FORMAT(
            DATEADD(Start, 9, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 10,
        DATETIME_FORMAT(
            DATEADD(Start, 10, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 11,
        DATETIME_FORMAT(
            DATEADD(Start, 11, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 12,
        DATETIME_FORMAT(
            DATEADD(Start, 12, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 13,
        DATETIME_FORMAT(
            DATEADD(Start, 13, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 14,
        DATETIME_FORMAT(
            DATEADD(Start, 14, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 15,
        DATETIME_FORMAT(
            DATEADD(Start, 15, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 16,
        DATETIME_FORMAT(
            DATEADD(Start, 16, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 17,
        DATETIME_FORMAT(
            DATEADD(Start, 17, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 18,
        DATETIME_FORMAT(
            DATEADD(Start, 18, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 19,
        DATETIME_FORMAT(
            DATEADD(Start, 19, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 20,
        DATETIME_FORMAT(
            DATEADD(Start, 20, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 21,
        DATETIME_FORMAT(
            DATEADD(Start, 21, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 22,
        DATETIME_FORMAT(
            DATEADD(Start, 22, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 23,
        DATETIME_FORMAT(
            DATEADD(Start, 23, 'day'),
            "M/D/YY"
        ) & ", "
      ),
      IF(
        DATETIME_DIFF(End, Start, 'days') >= 24,
        DATETIME_FORMAT(
            DATEADD(Start, 24, 'day'),
            "M/D/YY"
        ) & ", "
      )

  ), ", $", "")

Admin_KTK · ‎Oct 18, 2024

This was wonderfully helpful to me.

What results on my end is a string of dates, listed in a single field.

Is there a way within airtable to translate the results of this formula into individual date fields?