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

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!

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"
    ) & ", "
  )
), ", $", "")
2 Likes

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.

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

1 Like

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.