Help

Convert string to hh:mm

5963 9
cancel
Showing results for 
Search instead for 
Did you mean: 
kim_g
5 - Automation Enthusiast
5 - Automation Enthusiast

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’))

9 Replies 9

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

@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
5 - Automation Enthusiast
5 - Automation Enthusiast

Figured it out. Ain’t pretty but works:

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

kim_g
5 - Automation Enthusiast
5 - Automation Enthusiast

Nope still stuck, when I try:

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

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:

Screenshot 2019-04-21 at 14.26.39.png

{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
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

image.png

BIG THANK YOU!

kim_g
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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: 😞

Screenshot 2019-04-23 at 15.11.04.png

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:

Screenshot 2019-04-23 at 15.13.59.png

JB

kim_g
5 - Automation Enthusiast
5 - Automation Enthusiast

@JonathanBowen thank you!