Skip to main content

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:

  1. 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").
  2. Then I'd like to have another column that shows the oldest date and a column that shows the most recent date.
  3. 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!

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

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

 

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.

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

 

Thanks so much @TheTimeSavingCo !

Reply


Terms & Conditions