Best Of
Re: Formula for updating window of time to different time zones...
You can use the following formulas to convert the session time from PDT to other time zones automatically in Smartsheet:
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.
Re: September Question of the Month - Join the conversation and receive a badge
This summer I tried the new Lego LED light kits!!! Chanced my life 🤣
Joe Goetschel
Re: September Question of the Month - Join the conversation and receive a badge
I tried using AI to figure out a complicated Smartsheet formula. Typing out what I was trying to do in the prompt got me about 80% where I needed to be, making the last 20% a breeze.
TamieKing
Re: September Question of the Month - Join the conversation and receive a badge
I recently tried using the new "add to dashboard" feature in the Analyze data AI tool in Smartsheet, and it is a game changer! To create dashboard charts previously, I had to create many many sheet summary fields or column formulas, then a report with select summary fields for each chart I wanted, then create the chart from the report and hope it is formatted the way I was hoping for. Now, I can create all the same charts as before, ** plus ** ask the tool to change the chart type, add sorts, apply filters (including current user filters!), and more- no reports or sheet summaries required!!
I had been contemplating creating 100 column formulas and 25 reports to display survey results on a dashboard, but with the new tool, all I have to do is edit my prompt for each chart, and voila- instant charts!
I hope this tool continues to improve in capability- I have many more uses that it can't handle yet (density charts would be great!)
Janae G.
Re: September Question of the Month - Join the conversation and receive a badge
This summer I started to practice Archery more to mediate in peaceful focus. There hasn't been much more that excited me as much as hitting the target!
Oscar Gonzalez_1
Re: September Question of the Month - Join the conversation and receive a badge
I recently tried to create a dashboard. It did not go well, but I'm going to keep at it and find some tools to help.
Kcarrasq
Re: September Question of the Month - Join the conversation and receive a badge
Recently I learned how to map PDFs in the Smartsheet platform which is making life soooo much easier. I built a system for the county continuous improvement leadership with the automations that start the mapping. He indicated it saved him an incredible amount of production time. He was able to send 65 mapped PDFs that would normally take three days to enter data into a regular PDF in 45 minutes! This is remarkable for someone at that salary grade to shave more than two and half days off of a project like this.
herickson
Re: September Question of the Month - Join the conversation and receive a badge
Our company added Data Shuttle…and I love it! It helps with all the manual work and formulas. I can't wait to see if we get Data Mesh! 😁 that would be life changing! 🤩
noshiro
Re: September Question of the Month - Join the conversation and receive a badge
Wow some great posts in the community! I have been inspired to try to learn Spanish in my free time. The progress is slow, but still improving every day.
JM2863
Re: September Question of the Month - Join the conversation and receive a badge
After attending Engage last year and checking out the Seattle and Portland areas for the first time, I decided to move to Portland, Oregon! I've been living in the Portland area now for a few months and LOVING the PNW. Everything is green, songbirds are everywhere, and living by big rivers has been a new experience. My views while working remotely all day has massively improved. Now I just need a free ticket to Engage 2025 so I can attend the conference again that changed it all!
Tracey A Siepser