Best Of

@Kelly Moore

Adding two helper columns to standardize the time format is a great idea! This approach not only simplifies daily entries but also ensures consistency, which is crucial for accurate formula calculations.😁

Sorry @jmyzk_cloudsmart_jp - we worked it at the same time.

Hey @SHousewright

If you added two helper columns, you might be able to simplify your daily entries. Importantly, this would also make certain that the time format was standardized, which is critical for any formula. The two helper columns are Start time (H:MM) and Duration (min). With these columns you can then utilize the TIME function, which will automatically format your results.

Using the [Start Time PDT (H:MM)] and [Duration (min)] columns then the formulas become:

Session Time PDT

=IF([Start Time PDT (H:MM)]@row <> "", TIME([Start Time PDT (H:MM)]@row, 0) + " - " + TIME(TIME([Start Time PDT (H:MM)]@row, 0) + [Duration (min)]@row, 0))

Session Time EDT

=IF([Start Time PDT (H:MM)]@row <> "", TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 180, 0) + " - " + TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 180 + [Duration (min)]@row, 0))

Session Time GMT

=IF([Start Time PDT (H:MM)]@row <> "", TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 420, 0) + " - " + TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 420 + [Duration (min)]@row, 0))

Session Time CDT
=IF([Start Time PDT (H:MM)]@row <> "", TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 120, 0) + " - " + TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 120 + [Duration (min)]@row, 0))

Session Time MDT
=IF([Start Time PDT (H:MM)]@row <> "", TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 60, 0) + " - " + TIME(TIME([Start Time PDT (H:MM)]@row, 0) + 60 + [Duration (min)]@row, 0))

Will this work for you? If you want to keep entering the entire PDT session string, let me know and I'll adjust the formulas. I, however, would recommend limiting manual keystrokes to as few as possible
Kelly

Hi @SHousewright

You can use the following formulas to convert the session time from PDT to other time zones automatically in Smartsheet:

https://app.smartsheet.com/b/publish?EQBCT=dec2c50b1651459fa71f50461d8ad23d

Extract Start and End Time from PDT in 24 hour format

[PTD End 24] =TIME(RIGHT([Session Time PDT]@row, LEN([Session Time PDT]@row) - FIND(" - ", [Session Time PDT]@row) - 2), 1)

Convert to Other Time Zones
[MDT] =TIME([PTD Start 24]@row, 0) + MTD# * 60 + " - " + (TIME([PTD End 24]@row, 0) + MTD# * 60)
[CDT] =TIME([PTD Start 24]@row, 0) + CDT# * 60 + " - " + (TIME([PTD End 24]@row, 0) + CDT# * 60)
[EDT] =TIME([PTD Start 24]@row, 0) + EDT# * 60 + " - " + (TIME([PTD End 24]@row, 0) + EDT# * 60)
[GMT] =TIME([PTD Start 24]@row, 0) + GMT# * 60 + " - " + (TIME([PTD End 24]@row, 0) + GMT# * 60)

Explanation of the TIME Function

The TIME function in Smartsheet converts a string or numerical input into a time object.
Syntax:

TIME(time_value, [format], [precision])  

• time_value – String ("8:30 PM") or numbers (hours, minutes, seconds).
• format – Optional; 0 for 12-hour, 1 for 24-hour.
• precision – Optional; 1 = hh, 2 = hh:mm, 3 = hh:mm:ss.

Example:

=TIME("4:30 PM", 1, 3)  

Returns 16:30:00 in 24-hour format.

Explanation of + 60 * Time Shift (e.g., + GMT# * 60)

The + 60 * Time Shift part adjusts the extracted time to the target time zone.

• In Smartsheet, TIME() outputs values in minutes, so adding a time shift requires converting hours into minutes.
• Since 1 hour = 60 minutes, multiplying the time zone offset (e.g., GMT#) by 60 shifts the time correctly.

For example, converting PDT to GMT:

=TIME([PTD Start 24]@row, 0) + 7 * 60  

• If PDT time is 8:00 AM, this formula shifts it by 7 hours (7 * 60 = 420 minutes).
• The result will be 3:00 PM GMT.

Replace MTD#, CDT#, EDT#, and GMT# with the correct hour offsets (+1, +2, +3, +7) respectively.