Reference Another Sheet Bug

alterettore

hi

There is a discrepancy in the value calculated by a function when I access the source sheet

 VS 

when I access it via "Reference another sheet".

As you can see in the screenshot "Smartsheet - Sheet" attached, the function NETWORKDAY returns "21" (highlighted in yellow).

Smartsheet - Sheet.JPG

However, in the screenshot "Smartsheet - Ref another Sheet" attached, the same cell returns "20".

Smartsheet - Ref another sheet.JPG

If you compare also other lines in the screenshots you will notice that it is not an isolated case.

How can I eliminate this discrepancy?

thanks

Ettore

Paul Newcome

That is certainly odd. I have never seen that before. What is in the other date column? Is it a system generated date (created or modified), or is it just a plain date column? Do you have any automations (such as Record a Date) populating either of the columns?

I feel you are going to want to loop in support, but while you wait for their response maybe we can narrow down where the issue is to help them get it resolved a little faster.

alterettore

Hi Paul

thanks for the quick reply. pls find below my comments

In the networkday formulas there are 2 dates:

a) Report Date = this is a plain static date (no formulas), which indicates the "as at date" of the report

b) PRT Sent Date = this is a date populated by an automation Record a Date when the Status changes to "Completed"

regards

Ettore

Paul Newcome

Ok. I know that with the system generated date/timestamps for created and modified, there are known timezone issues. I wonder if there are also timezone issues on the backend somewhere with the Record A Date automation.

When you reach out to support, definitely let them know exactly how each of the two columns are being populated so hopefully they can have some idea as to where to start looking.

@Andrée Starå @Genevieve P. Have either of you ever seen a discrepancy like this or heard of any kind of issues with the Record A Date automation?

alterettore

yes, thanks for the suggestion.

however, the problem may be something else: the "sent date" is not wrong; but the same cell displays different values depending how you access it: directly in the sheet OR with a reference to display it in a different sheet.

Paul Newcome

Right. I understand that. But it is storing data on the back end which is what the formula is pulling from. When you view the sheet directly it could be pulling the correct data but there may be a bug in the system somewhere that when it is viewed via cross sheet reference window it is calculating incorrectly because of the back end data storing the wrong date due to a difference in time zone.

It is just a theory though, and I do definitely recommend passing along to support your information on how each of the columns are being populated.

Genevieve P.

Hi @alterettore

I haven't seen this before, but I agree with Paul's instinct... it may have something to do with the Record a Date date and timezones.

I also agree that contacting Support with screen recordings and a detailed explanation of your issue will be the best way forward. They can troubleshoot this with you in a private channel where you can share more information and they'll be able to determine if this is a bug or expected based on the date stamp function.

Thank you!

Genevieve