We're a publishing company and we keep track of content that we publish over the course of the year with these different columns showing publish dates. I'd like to do 2 things:Create 1 column that combines the dates (combining them so that they remain separate would be ideal, but otherwise, it could just be text formatted like this "MM/DD/YYYY, MM/DD/YYYY").Then I'd like to have another column that shows the oldest date and a column that shows the most recent date.Another column that just adds the number of times the content has been published, so counts the number of fields with a date in them.The error you see currently in the far right field had the formula that I tried on my own which was:DATETIME_FORMAT({Publish Date 1}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 2}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 3}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 4}, 'MM/DD/YYYY')A screenshot is attached. Thanks in advance!

Solved

Combining Dates from Multiple Fields

Forum|Forum|11 months ago
November 29, 2024
5 replies
47 views

+12

LaurenMaine
Inspiring

We're a publishing company and we keep track of content that we publish over the course of the year with these different columns showing publish dates. I'd like to do 2 things:

Create 1 column that combines the dates (combining them so that they remain separate would be ideal, but otherwise, it could just be text formatted like this "MM/DD/YYYY, MM/DD/YYYY").
Then I'd like to have another column that shows the oldest date and a column that shows the most recent date.
Another column that just adds the number of times the content has been published, so counts the number of fields with a date in them.

The error you see currently in the far right field had the formula that I tried on my own which was:

DATETIME_FORMAT({Publish Date 1}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 2}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 3}, 'MM/DD/YYYY') & "," & DATETIME_FORMAT({Publish Date 4}, 'MM/DD/YYYY')

A screenshot is attached.

Thanks in advance!

Best answer by TheTimeSavingCo

Create 1 column that combines the dates (combining them so that they remain separate would be ideal, but otherwise, it could just be text formatted like this "MM/DD/YYYY, MM/DD/YYYY").

Try this:

REGEX_REPLACE(
  IF(
    {Date 1},
    DATETIME_FORMAT(
      {Date 1},
      'MM/DD/YYYY'
    ) & ', '
  ) &
  IF(
    {Date 2},
    DATETIME_FORMAT(
      {Date 2},
      'MM/DD/YYYY'
    ) & ', '
  ) &
  IF(
    {Date 3},
    DATETIME_FORMAT(
      {Date 3},
      'MM/DD/YYYY'
    ) & ', '
  ) & "", 
  ".{0,2}$", 
  ""
)

---

Then I'd like to have another column that shows the oldest date and a column that shows the most recent date.

For this I'd recommend two fields so that you don't have to repeat the formulas. The first field, 'Calculation', would provide the earliest date, the problem is that if there are no published dates it'll output 1 Jan 1970:

DATETIME_PARSE(
  MIN(
    IF(
      {Date 1},
      DATETIME_FORMAT(
        {Date 1},
        'x'
      ) + 0
    ),
    IF(
      {Date 2},
      DATETIME_FORMAT(
        {Date 2},
        'x'
      ) + 0
    ),
    IF(
      {Date 3},
      DATETIME_FORMAT(
        {Date 3},
        'x'
      ) + 0
    )
  ),
  'x'
)

And so we use a second field to handle the display element so that we don't need to repeat the formula that combines all the dates for us. This is a personal preference thing that makes things easier to manage and if you won't be adding more date fields you can just combine these two fields really

IF(
  DATETIME_FORMAT(
    {Oldest date - Calculation},
    'x'
  ) != 0,
  {Oldest date - Calculation}
)

---

Another column that just adds the number of times the content has been published, so counts the number of fields with a date in them.

COUNT(
  IF(
      {Date 1},
      DATETIME_FORMAT(
        {Date 1},
        'x'
      ) + 0
    ),
    IF(
      {Date 2},
      DATETIME_FORMAT(
        {Date 2},
        'x'
      ) + 0
    ),
    IF(
      {Date 3},
      DATETIME_FORMAT(
        {Date 3},
        'x'
      ) + 0
    )
)

Link to base

+12

LaurenMaine
Author
Inspiring
Forum|Forum|11 months ago
November 29, 2024

That formula by the way, gives an error when not all the fields are filled in.

1 Attachments

+31

TheTimeSavingCo
Brainy
Answer
Forum|Forum|11 months ago
November 30, 2024

Create 1 column that combines the dates (combining them so that they remain separate would be ideal, but otherwise, it could just be text formatted like this "MM/DD/YYYY, MM/DD/YYYY").

Try this:

REGEX_REPLACE(
  IF(
    {Date 1},
    DATETIME_FORMAT(
      {Date 1},
      'MM/DD/YYYY'
    ) & ', '
  ) &
  IF(
    {Date 2},
    DATETIME_FORMAT(
      {Date 2},
      'MM/DD/YYYY'
    ) & ', '
  ) &
  IF(
    {Date 3},
    DATETIME_FORMAT(
      {Date 3},
      'MM/DD/YYYY'
    ) & ', '
  ) & "", 
  ".{0,2}$", 
  ""
)

---

Then I'd like to have another column that shows the oldest date and a column that shows the most recent date.

DATETIME_PARSE(
  MIN(
    IF(
      {Date 1},
      DATETIME_FORMAT(
        {Date 1},
        'x'
      ) + 0
    ),
    IF(
      {Date 2},
      DATETIME_FORMAT(
        {Date 2},
        'x'
      ) + 0
    ),
    IF(
      {Date 3},
      DATETIME_FORMAT(
        {Date 3},
        'x'
      ) + 0
    )
  ),
  'x'
)

IF(
  DATETIME_FORMAT(
    {Oldest date - Calculation},
    'x'
  ) != 0,
  {Oldest date - Calculation}
)

---

Another column that just adds the number of times the content has been published, so counts the number of fields with a date in them.

COUNT(
  IF(
      {Date 1},
      DATETIME_FORMAT(
        {Date 1},
        'x'
      ) + 0
    ),
    IF(
      {Date 2},
      DATETIME_FORMAT(
        {Date 2},
        'x'
      ) + 0
    ),
    IF(
      {Date 3},
      DATETIME_FORMAT(
        {Date 3},
        'x'
      ) + 0
    )
)

Link to base

Adam, Airtable Consultant - thetimesaving.company

+12

LaurenMaine
Author
Inspiring
Forum|Forum|11 months ago
November 30, 2024

Thanks @TheTimeSavingCo , that worked beautifully! Is there any way on those two columns calculating the oldest and newest dates to remove the time stamp? Screenshot below.

1 Attachments

+31

TheTimeSavingCo
Brainy
Forum|Forum|11 months ago
December 2, 2024

Thanks @TheTimeSavingCo , that worked beautifully! Is there any way on those two columns calculating the oldest and newest dates to remove the time stamp? Screenshot below.

Hmm try setting the formatting for those fields and untoggle 'Include time'?

Adam, Airtable Consultant - thetimesaving.company

+12

LaurenMaine
Author
Inspiring
Forum|Forum|11 months ago
December 2, 2024

Thanks so much @TheTimeSavingCo !

1 Attachments

1 Attachments

1 Attachments

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded