Extract time from date column

[webdienste]

I have two columns with start date and end date. I want to display information on a course on a website as follows (via Integromat/Make ):
Start date - end date
Start time - end time

I already have the start date and end date nicely formatted. The respective columns are only set to display the date. But I could select the option to show the time (so: in the start column, also the start time, in the end column, also the finish time).

I could achieve this in Integromat, but first wanted to see if there is a formula to allow me to simply extract the time to a separate text column which I could then pass on to Integromat?

[rdb]

Would this do the trick?

[webdienste]

Thanks Ralf, that seems to work.

The only issue now is the formatting. I need it to display in the 24-hr format. See screenshot for current result:

[rdb]

Fair point. I wasn’t thinking of that.

Quick fix: Add an if/ifs to account for the possibility of a start at :00.

[webdienste]

Hi ralf,

OK, I achieved this using 3 columns: 1 ifs formula to extract/format the hour, another ifs formula to extract/format the minutes (in 5-minute blocks) and another to concatenate the two into the format I want.

This means that, to get the following: 18:00 - 21:00, I need 5 columns - 2 columns for hours, 2 columns for minutes and 1 column to concatenate.

The screenshot shows the ifs for minutes (blocks of 5 minutes) and the concatenate for 1 block of time

Surely there is an easier way?

[AkDk7]

Hi @webdienste.

I don’t get the reason for the stepping, but you could also do something like this…
Did I get your intention right?

if(
  hour({Prüfung}) < 10,
  "0" & hour({Prüfung}),
  hour({Prüfung})
) 
& ":" &
if(
  (minute({Prüfung}) % 5) = 0,
    if(
      minute({Prüfung}) < 10,
      "0" & minute({Prüfung}),
      minute({Prüfung})
    )
)

[webdienste]

Hi AkDk7

That was brilliant, that is exactly what I want. And so much simpler than what I had!!!

The stepping was to make the “ifs” for the minutes easier to handle. Most of the start and end times are either 00 or 30 anyway as those are the only 2 times than can be chosen.