NEW ZEALAND TIME ZONE & TODAY() function mismatch

Hend_Farah

Hi Smartsheet Team , 

I have an issue with the Today() function not reading my date correctly . I contacted you and you provided me with a long formula to overcome this but it still didn't solve the issue for us . 

The formula i'm using now is : 

=IFERROR(IF(AND(ISBLANK([Resolved Date]@row), [Created Date]@row <= TODAY()), NETWORKDAYS([Created Date]@row, TODAY()) - 1, IF([Created Date]@row = [Resolved Date]@row, 0, IF(NETWORKDAYS([Created Date]@row, [Resolved Date]@row) >= 1, NETWORKDAYS([Created Date]@row, [Resolved Date]@row), IF(NETWORKDAYS([Created Date]@row, [Resolved Date]@row) < 0, NETWORKDAYS([Created Date]@row, [Resolved Date]@row) + 1)))), "")

But as you can see on the snapshot attached below ...  if the created date was AM , your system reads it as being created in the previous day.  if it's PM time it reads it correctly ! 

i tried to get rid of the time portion by using the "Dateonly()" functions but again it's returning the wrong value on the first 3 rows because all of them were created AM . 

 

Any solution for this annoying issue please ? this was supposed to be an easy formula . 

Thanks 

Paul Newcome

If it is always a day behind when the time is AM, then for the DATEONLY portion you could use something along the lines of this...

 

=DATEONLY([Created Date]@row) + IF(FIND("AM", [Created Date]@row) > 0, 1, 0)

 

This will take the date and add 1 if "AM" is found and 0 if AM is not found.

Hend_Farah

Thanks Paul 

I did that yesterday but again the formula to calculate the escalation lead time still returns unrealistic numbers because i'm using Today() function to calculate the LT as shown in my formula above. 

Paul Newcome

Ah. Ok. I see what you are saying. And because there is no time calculation within Smartsheet (yet but coming soon I've heard) there's no way to build that in.

 

Unfortunately, until SS rolls out better time tracking and calculations the only way I can think of to account for this would be to manually enter am or pm each time the sheet is opened which will turn into a royal annoyance. 

 

I wonder if there is a way through a third party tool such as Zapier that could possibly ping the sheet regularly throughout the day to update a Modified (date) system column. Then, instead of using TODAY you could reference the date/time in that cell and get your correct calculations.

 

I am not very familiar with Zapier, but I have read other solutions where using it to "ping" a sheet was possible. I am just not sure of the frequency or how to set it up or anything.

Andrée Starå

Hi Hend,

Paul is correct and Zapier would probably be a great solution for this scenario. Is that an option for you?

I'll check and get back to you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Andree,

 

Thanks for popping in!

 

I REALLY need to check out Zapier one of these days. I had kind of fiddled with it a few months ago, but I just couldn't get a feel for it. I need to take another look at it now that I've had some time to step back from it.

Andrée Starå

Paul,

Yes, you should. It's an excellent companion to Smartsheet. It's different, but when you get the gist of it, it's great.

Paul Newcome

I did end up getting into it a little bit, but it seems like they have it set up so that even very basic stuff has to be paid for. Would you mind if I sent you a quick email explaining what I mean to see if I am just misunderstanding the way it works? I don't want to hijack a thread too much especially for an app that is not even SS specific.

Andrée Starå

I actually think they are very generous in what's included in the free tier. I have the highest level now, but I came a long way with the free one.

Yes, feel free to email me and explain what you need!

Peggy Huang

Hi all,

I am searching for the answer about time zone and Today() function and found this discussion which seems related. 

May I know what's the time zone in Smartsheet for Today() function?  Is the Today() function applies the sheet owner's time zone or default the US time zone? 

We have put Today() in a formula to reflect the task status in visual symbol like red lights or green lights.  As the task owners are in different time zones, we need to know what's the time zone for "Today()" .  Thank you!