Discrepancy with result in grid versus report

Lim

Hi Smartsheet,

I'm using a formula to calculate the days open for individual case. The grid is showing the correct number of days however the report does not showing the same result. The report is only link to one grid file, which is the one showing below. Please advise. Thank you.

Both of the screenshots below were extracted at the same time.

Grid

Reporting

The formula is

=[Section 3 Date]@row - [SCAR Issuance Date]@row

PM_Reeves

Hi @Lim,

Unfortunately I don't have a solution for you, but I suspect it might have something to do with time-zones. I regularly have issues like this as I'm in Australia and Smartsheet's clock is on Pacific Time as a default.

It looks like your sheet is in 'today', but the report is still in 'yesterday'.

Be interested to see if it catches up when the day changes in Pacific Time.

Lim

Hi @PM_Reeves ,

My time zone is set to my current location under Personal settings. If this is due to time zone issue, the grid and report should show the same result.

PM_Reeves

Hi @Lim

My time-zones are all set to Australia, but it doesn't seem to affect everything Smartsheet wise.

I have one summary sheet that has a 'Last Updated' cell for each project. It can change to US format and when it does it takes the Pacific Time, with no changes having been made on the source sheet.

I just go to the source sheet, click an empty cell and hit 'Save' and it rectifies the issue.

It has sort of been explained to me by support, let me see if I can dig it out for you.

Here you go

While it is not ideal, this is expected behavior when cell linking systemcolumns "Created date" and "Modified date". This isdocumented in the help article: Troubleshooting:How Smartsheet handles time zones.

You may want to submit an enhancement request via our community page forconsideration by the product team.

In case:06876738 we had provided a workaround for this which involvedcreating a new "helper column" that pulled the system data andconverted to a date for you to cell link. the helper column would have a columnformula similar to be below to pull the relevant data.

=DATE(VALUE("20" + MID(modified@row, 7, 2)),VALUE(MID(modified@row, 4, 2)), VALUE(LEFT(modified@row, 2)))

NOTE: You may need to change the cell references.
Please test this work round and let us know if you need any further assistancewith it.

Thanks for using Smartsheet! If there are no additional questions on thistopic or your issue has been resolved, there is no need to reply to this email.

Lim

Hi @PM_Reeves,

Thank you for the tips. Currently I do not use the "Created date" column and my formula is minus 2 columns that contains with dates.

Formula=[Section 3 Date]@row - [SCAR Issuance Date]@row