Formula for updating window of time to different time zones...

I am creating a form to schedule meetings however, I need the times I have listed in Pacific Daylight Time (PDT) to be converted into Mountain Daylight Time (MDT) (PDT+1 hour), Central Daylight Time (CDT) (PDT +2 hours), Eastern Daylight Time (EDT) (PDT +3 hours), and Greenwich Mean Time (GMT) (PDT +7 hours).

I am manually inputting the meeting time availability into the Pacific Daylight Time (PDT) in the following format:
HH:MM AM/PM - HH:MM AM/PM

I have shared a screenshot of my sheet. I need help with a formula as I have it figured out in Excel but can't figure it out in Smartsheet!

Find more posts tagged with

Sheets

Formulas

help

time zone

Accepted answers

All comments

jmyzk_cloudsmart_jp

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.

Kelly Moore

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

jmyzk_cloudsmart_jp

@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.😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.