Showing the start/end date in a project plan in multiple time zones

Karen Stulc

Hi,

I am developing a project plan for a global team. I have built a project plan that shows the start and end times in PST but other team members would like to see when a task is going to be triggered in their local time zone.

In MS Project I was able to create a column that showed the date/time plus 9 hours for CET, for example.

I have used a formula to display the underlying hour/minute for a task but can't seem to find a way to add hours, only days.

this is what I have so far:

I want to add more columns to show the start date/time and end date/time in CET.

Note that the Start Date/Time PST is now a text field since I used a formula to show it. Whatever calculation I use, will have to start from the Start Date PST. For example if I use =[Start Date PST]+1 would show April 23, not April 22 12 PM.

Thanks in advance for your help.

Karen

Alejandra

Hi @Karen Stulc,

I don't think this is currently possible since Smartsheet doesn't have a time function. For example, even if you were to split the date and time up into separate columns adding a 1 to the cell containing 11:00 AM results in 11:00 AM1.

When you have a moment, submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet.

Alejandra

Hi @Karen Stulc,

I don't think this is currently possible since Smartsheet doesn't have a time function. For example, even if you were to split the date and time up into separate columns adding a 1 to the cell containing 11:00 AM results in 11:00 AM1.

When you have a moment, submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet.

Jeff Reisman

Hi Karen,

From what I understand, a fix for this is at the top of a lot of Smartsheet Admin's wish list, mine included. Supposedly a fix is coming, date TBD.

Smartsheet is notoriously inconsistent with date/time values, and it has to do with the nature of the Smartsheet architecture. Where your settings are such to present everything to you in your timezone, Smartsheet does not know how to do date calculations based on your timezone against your timezone. For example, I am on the east coast in EST. If I want Smartsheet to calculate based on rows where Date Created = TODAY, it's going to start with those created at or after 3am today, since that is 12am PST. If I want it to generate an alert when something was created one day ago but has not been modified since then, it handles that differently, and alerts when something is only 21 hours old, not 24. (In that case it seems to process modified date in EST and Created Date in PST - or vice versa? - so frustrating!)

One thing I did to get around some of these was to remove the time element where I could. I have a date column where I use the DATEONLY function against the modified date column which isolates the date modified without regards to the time. I can do the same with the Date Created column and then have two date columns that don't care about hours, just dates. It's not as granular as I'd like, but at least it removes the uncertainty.

Good luck!