Date format in formula

[pbdd]

Hi there,

Heads up: I might be mixing an issue with feature request in the following:

I have a page design that shall show certain values only when a condition is met.

Here is an example for the dates of a course: the end date shall only show if the course lasts longer than one day.
Wanted output on the page:
Course is 1 day: “01.06.2022”
Course is >1 day: “01.06.2022 bis 02.06.2022”

Until recently for the second part this worked with the following formula, which I then inserted into the page:
if({Kurs start} <> {Kurs ende}, " bis " & {Kurs ende}, “”)
The start date is always required so it is taken directly from the date column.

Unfortunately (probably after the recent update), it now shows up as “bis 2022-06-02”, hence, messing up the date format.
I tried having the formula detect the content as date, since it was said to be static once you did that (see this post: Formula with date /Формула с датой). But there seems to be auto-re-detect enabled now, which changes the date format back to YYYY-MM-DD immediately after inserting the “” into the formula.

Possible solutions:

• take back the change of whatever you did with the dates recently
• add an option to define date formats within formulas

Slightly related feature request for page design:

• add an option to show table fields only when conditions of their own values or different columns are met (e.g. checkbox is active, or formula returns a certain value)

Looking forward to your reply and a quick solution.

Best regards
Paul

[rdb]

Hi Paul,

@pbdd

add an option to define date formats within formulas

This feature exists (see below).

@pbdd

take back the change of whatever you did with the dates recently

None made.

@pbdd

add an option to show table fields only when conditions of their own values or different columns are met (e.g. checkbox is active, or formula returns a certain value)

We do not have the intention of adding conditional logic in the page design plugin. It would bloat the plugin. It’s unnecessary anyway. The formula column gives you much more control over conditionality that we could possible integrate in the page design plugin.

All you want to do already works just fine:

Use the “Format settings” of the formula column to set the date format to the dot-notation.

[pbdd]

Hi Ralf,

thanks for the quick reply.

Looks like I am getting weird behaviour then. With the formula mentioned in my first post, I cannot change the format settings, they are automatically set to string.

I edited the formula to the following:
if({Kurs start} <>{Kurs ende}, {Kurs start} & " bis " & {Kurs ende}, {Kurs start})

Now I am be able to change the date format settings, since auto-detect recognizes it as a date column. And for the “else”-case the start date shows the correct format. In combination with the string and the end date, both dates have the wrong date format even though the format is set to Germany, Russia etc…
See the following:

[rdb]

Your screenshot does not show the source columns of your formula. So it is diffcult to make any meaningful comment.

I assume that the date format in your source column is set to ISO. Please change to the dot-notation.

You can always prompt SeaTable to re-detect the result type in a formula column. SeaTable determines the result type based on the first few rows. If any of these rows contains a string value, SeaTable will set the result type to string.

[pbdd]

Ok, good point.

The source columns for the formula are linked formulas, which are detected as arrays and take the dates from the original input columns in a different table. The dates there are set to German/Russian.

So might this be related to the transition from the other table via linked formula? Any ideas what might be wrong in my configuration?

[cdb]

Hey pbdd,

I think I found a solution for your problem. Since I don’t now your base details I tried to rebuild how I think you use SeaTable.

This is my first table “Course”.

This is my second table “Participations”

Now I understand that you want to have a formular column. If the course starts and ends at the same day this columns should contain only this date. If the course goes longer than one day the column should say: dd.mm.yyyy bis dd.mm.yyyy, right?

It is definitely the case that as soon as you connect the two dates the column “thinks” this is a string and then the dates are shown with the wrong date format. SeaTable always stores date values in the format YYYY-MM-DD and afterwards change it in the desired format.
In this case SeaTable “thinks” the content of this formula is a string and therefore does not change the format of the dates from YYYY-MM-DD to dd.mm.YYYY.

By the way this is the formular I used to get these results:

if({Kurs start}<>{Kurs ende}, {Kurs start} & " bis " & {Kurs ende}, {Kurs start})

But there is an easy solution for this:

Change the formula to this:

if({Kurs start}<>{Kurs ende}, 
day({Kurs start}) & "." & month({Kurs start}) & "." & year({Kurs start}) & " bis " & day({Kurs ende}) & "." & month({Kurs ende}) & "." & year({Kurs ende}), 
day({Kurs start}) & "." & month({Kurs start}) & "." & year({Kurs start})
)

The idea is simple: we build the german format with the dots inside the formula. Here is the result:

Best regards
Christoph