Easy way to convert to datetime?

User Talk Formulas
1

Hi.

I’ve tried to find a solution for converting string datetime values to the data type datetime.

I’m having this string “21.01.2023, 09:11” and you can see there is comma between the date and time string. Of course SeaTable cannot recognize this as valid. So since there is no function to convert such a string, after I had to get rid of the comma, to convert it to a valid datetime data type, so I figured out a formula to do this.

grafik

In the column Datum is just used a substitute({Name}, ',', '', 1) to get rid of the comma but it would not be converted to valid datetime by SeaTable.

I used this formula in the column Datum2:

dateAdd(
  dateAdd(
    date(
      right(left({Name}, 10), 4),
      right(left({Name}, 5), 2),
      left({Name}, 2)
    ) & ' 00:00:00',
    value(left(right({Name}, 5), 2)), 
    'hours'
  ),
  value(right({Name}, 2)),
  'minutes'
)

How does it work?
In the first step it converts the date to a valid data type. The thing with the time value ‘00:00:00’ I will explain later. Then I add the hours with the inner dateAdd function. After this step I run this values again through a dateAdd function to add the minutes. Now we come to the funny part: if I leave the string ‘00:00:00’ away, SeaTable will not recognize it as a date and time formatting even if I add hours, minutes or seconds with dateAdd to it.

Is there a simplier way to convert datetime string to a valid datetime type in SeaTable?

Cheers
AkDk7

1 Like