Convert string to hh:mm

[kim_g]

Hi,
I am trying to set a class start time based on a section. Not sure how to convert the string into a time unit. This is what I’m trying:

SWITCH(Section,
“A”, DATETIME_FORMAT(“8:00”, ‘hh:mm’),
“B”, DATETIME_FORMAT(“9:00”, ‘hh:mm’))

[JonathanBowen]

Hi @kim_g to convert a string to a date/time you need to use DATETIME_PARSE(), so modifying your formula, you should use:

SWITCH(Section,
'A', DATETIME_PARSE('8:00', 'H:mm'),
'B', DATETIME_PARSE('9:00', 'H:mm')
)

However, DATETIME_PARSE() takes an input format, not an output format and always outputs the result as:

‘M/D/YYYY h:mm a’

Are you planning to use one of the date functions on the result or are you just looking to display something that looks like the time? If the latter, you could just do:

SWITCH(
Section,
'A', '8:00',
'B', '9:00'
)

JB

[kim_g]

@JonathanBowen thanks for your answer. I am trying to create a spreadsheet to schedule classes. I was using at first.

SWITCH( Section, 'A', '8:00', 'B', '9:00' )

The only issue is that I have another field called duration and I am trying to calculate the end time for the class. To use the date functions I need them to be in H:mm format and not a string.

.

[kim_g]

Figured it out. Ain’t pretty but works:

DATETIME_FORMAT(DATETIME_PARSE("8:00", 'hh:mm'), 'hh:mm')

[kim_g]

Nope still stuck, when I try:

DATEADD({Start time}, 3, "hours")
it bombs.

[JonathanBowen]

Hi @kim_g - yep this is because DATETIME_FORMAT outputs a string, which can’t then be used in any date/time formulas. How about this:

{Class Start Time} is the formula noted above:

SWITCH(Section,
'A', DATETIME_PARSE('8:00', 'HH:mm'),
'B', DATETIME_PARSE('9:00', 'HH:mm')
)

This is now outputting as a date/time, although as you can see it is displaying the date element (which shows today’s date).

Your {Duration} is simply an integer. The {Class End Time} field is a formula that adds the duration onto the start date/time:

DATEADD({Class Start Time}, Duration, 'hours')

Now that you have the start and end times as date/times you can format both and only display the time elements (last two columns) using:

DATETIME_FORMAT({Class Start Time}, 'HH:mm')

DATETIME_FORMAT({Class End Time}, 'HH:mm')

The Class Start Tie and Class End Time fields can be hidden if you don’t want to see them.

What we’re doing here is:

Date formulas, then date display

rather than

Date display, then date formulas

Does this give you what you want?

JB

[kim_g]

Almost! While what you suggested works, I need start time to be displayed in HH:mm format as well since these are weekly schedules. Ideally the durations would be displayed in HH:mm as well, but I can live with using durations as 1.25 hours rather than 1:15.

BIG THANK YOU!

[kim_g]

@JonathanBowen any idea how to resolve this? I’m still stuck.

[JonathanBowen]

Hi @kim_g - yes, figured it out. You need to set the duration field to be a “duration” rather than an integer (lol, I should have thought of that :winking_face: 😞

So you can now enter this is 1:15 for one hour 15 mins etc.

Then on the {Class End Time} set the formula to be:

DATEADD({Class Start Time}, Duration, 'seconds')

When you use DATEADD() and a duration field, the units always needs to be 'seconds'.

So, now, you end up with:

JB

[kim_g]

@JonathanBowen thank you!