Question: Formula results in reports

Michael G.

Hi!

We use formulas in sheets.

When will the calculation be done?

Ony when having a sheet open?

Or also when using the column in a report?

In other words:

If the formular uses TODAY or s.th. like that, the result differs from day to day.

But is the result okay, when the sheet hasn´t been opened for a few days, but used in a report.

Sorry for this

Find more posts tagged with

Formulas

Reports

Comments

Rob Hagan

Hi Michael,

We use TODAY() in all of our 50 or so active project sheets.

We have many reports that extract data from those active project sheets.

Our experience is that the formulas don't get re-evaluated just because a report touches an underlying sheet. SmartSheet appears to assume that an underlying sheet is fully evaluated when it extracts data from it.

Consequently, I have written a Java program (using the SmartSheet-Java-API) to make an update to one nominated cell on every active project sheet at 5am every day and this has resolved our issue as it forces a full re-evaluation of each sheet that it "touches".

The [Task Status] formula was the main one affected. It appears on every project sheet:

------------------------------------cut here

=IF(OR([Row Type]1 = "Task", [Row Type]1 = "Milestone"), IF(OR(ISBLANK([Start Date]1), ISBLANK([End Date]1), NOT(ISDATE([Start Date]1)), NOT(ISDATE([End Date]1))), "Unscheduled", IF([% Complete]1 = 1, IF((TODAY() - DATEONLY([End Date]1)) < 7, "Completed Recently", "Completed"), IF(TODAY() > DATEONLY([End Date]1), "Overdue", IF(OR(ISBLANK([% Complete]1), [% Complete]1 = 0), IF(TODAY() < DATEONLY([Start Date]1), "Not Due To Start", "Not Yet Started"), IF(TODAY() < DATEONLY([Start Date]1), "In Progress Early", "In Progress"))))))

------------------------------------cut here

You will notice that I use DATEONLY() to throw away the time information in the start and end dates so that the comparisons work correctly.

I imagine that you have a similar formula in your implementation.

Cheers,

Rob.

J. Craig Williams

Hey Rob,

Just so you know, DATEONLY() does not currently do anything.

Support has said it might someday, so we don't want to deprecate it (paraphrased).

But since I don't know what it will do when/if they ever make it do something, I've been removing them from formulas as I run across them.

Let me know if your testing proves otherwise.

Craig

Rob Hagan

Hi Craig,

I am seriously pissed of with SmartSheet this morning. To publish a behaviour in their official "these are our functions and how they operate" sheet and then to have it effectively do nothing (I did a quick test), and then to hear (thanks) that their support say "it may do something one day". Blah!

One does not expect to have to prove that published, supported functions work as specified. I am not their beta tester!

But, no damage putting it in as it will be important when they switch on time within their user accessible date data type which, given the history that I have observed, may be turned on with little notice.

Thanks for being across this one... Legend.

Jason

Hi Rob and Craig,

This discussion caught my eye. I'm hoping to be able to shed some light on this side topic.

I was surprised to see it said that DATEONLY() does nothing, so I did a little checking.

Our help article states:

"DATEONLY() will remove the time component of a date/time or system column cell."

I have found the help article to be correct.

I threw together some quick comparisons between functions with and without DATEONLY.

Check out this published sheet

This is not exhaustive or a deep explaination of how the function can be used in complex formulas. Its just a simple demo to show the function in action.

What might be confusing and unclear is that boolean comparisons between DATE/TIME and DATE operands will strip time from the DATE/TIME operand automatically (Note the difference in types.) This is not the case when comparing DATE/TIME to DATE/TIME operands. This makes using DATEONLY in conjunction with comparators such as greater-than/less-than/equal unnecessary when one of the operands is a DATE.

I believe this was done as part of the formula enhancement feature that was release in August 2016. The release notes say the following:

"Smartsheet now manages and converts between types more deliberately. For example, in a formula that pulls the date and time from a Auto-Number/System column and a text in another, you will now see the combined date, time, and text; =(Modified1 + "new") you will get 08/06/16 11:54am new. The old function only included the date and text." (Emphasis mine)

Now I can't speak authoritively as to whether or not it will change in the future. But I can say that Smartsheet doesn't change formula functions without caution and extreme consideration of customer impact.

Also, in my experience and understanding, if a formula is documented in the help articles, it is considered supported. There are some formulas that are undocumented, which if used is done at your own-risk.

Hope this helps a little.

Rob Hagan

Hi Jason,

Good to hear that DATEONLY() operates as specified in the documentation. I unreservedly withdraw the negative comment that I made.

Thanks for your investigation and explanation. I do intend to retain the DATEONLY() as it "future proofs" the code that I am writing against any change to the specification of type casting. I have only one place where TODAY() is used (althought that one place is propagated uniformly across all rows in the 50 or so active project sheets) so that's not a large imposition on my implementation.

(Knowingly off thread, but raised from your example)

I used your example to explore a bit more. What I did find interesting was that with Project Settings of an 8 hour day, and the Start Date of a task equal to the End Date of the same task, that the subtraction of Start Date from End Date gave 0.375 (9/24ths) rather than 0.3333... (8/24ths). On closer inspection it seems that an 8 hour day starts at 8am but finishes at 5PM (actually 4:59PM) and therefore lasts for 9 elapsed hours. Is this because a 1 hour lunch break is assumed?

Cheers,

Rob.

J. Craig Williams

Dammit - just lost my comment.

I'll try again.

My testing was centered around a single cell

=Finish1 - DATEONLY(Finish1)

should be 0.375

but it isn't. It is 0.

Row 21, I believe, returns an incorrect result as Start1 has a time component and DATEONLY(Modified1) does not. They should not be equal.

If they are, the Finish1 = DATEONLY(Modified1) should also be true.

(I'm curious how you got that result, I could not reproduce it)

Rob's assumption is correct. Smartsheet assumes that a work-day of 4-16 hours starts at 8am. Workdays longer than 4 hours have a 1 hour break at noon.

I tested this significantly when dealing with schedules that were planned down to the hours. I can't do that anymore. Maybe I can after studying your sheet further.

Again, my testing was centered around a single Date/Time cell.

What is most interesting to me is the result of Row 8 -- but for a single cell.

And that no longer works.

I contacted support about that and was told the DATEONLY function did not do anything anymore. That person was wrong, apparently.

I believe that testing of Rob's formula may reveal that there is no difference between having it with and without DATEONLY, but I will leave that to someone else to test.

Thanks for picking this up Jason.

Craig

J. Craig Williams

1. Very surprised to find that

=(something) = (something else)

works in a checkbox column.

Pretty sure that used to throw an #UNPARSEABLE.

Not sure I like it, but good to know.

2. Here's the original posting:

https://community.smartsheet.com/discussion/bug-comparing-maxsystem-date-system-date-returns-incorrect-result

That is related to the time component of the Date/Time, not the date component.

Craig

Jason

In respons to Michael's original question.

Formulas are recalculated every time a change is saved to the sheet. Opening a report will not cause formulas to recalc on a sheets that are in the report. Rob has effectively worked around that by using the API to change a single cell in his sheets once a day, which causes a formula recalc on the changed sheets.

Hope this helps.

Jason

Hi Craig

I think row 21 is correct. I mentioned this in my previous post, but I'll reword it: When using boolean operators, if one of the operands is a DATE type, and the other is DATE/TIME, the DATE/TIME is converted to a DATE type also (stripping the time component.) The result of DATEONLY(Modified1) is a DATE, which is then compared to a DATE/TIME (that being START1).

Your assertion, that if Row 21 is true, then Finish1 = DATEONLY(Modified1) should also be true, is true. I've added it to my published test sheet.

I didn't do anything fancy with the sheet. It is simply a new standard project sheet. Start and Finish are DATE/TIME columns. I renamed the 'Modified' column to 'Modified (Date) System Column' so it's type is clearer.

Without digging in and researching, I agree that your single cell formula "= Finish1 - DATEONLY(Finish1)" appears to be wrong. I'll see what I can find out.

As memory serves, Smartsheet didn't used to support formulas that used the equals operator in them such as '= 1 = 1' The Aug. 2016 release improved Smartsheets ability to calculate such formulas. Hence why not it works whereas before it would result in #UNPARSEABLE.

J. Craig Williams

Jason,

Your first paragraph then implies that

=DATEONLY(Start1) = Modified1

I can not test at the moment because it is 10:50pm and

when Modified is

02/02/17 10:50 PM

=DATEONLY(Modified1) + ""

returns 02/03/17

which was how I got here in the first place.

Craig

Jason

Craig

So I just tested that and put it in my published sheet.

"=DATEONLY(Start1) = Modified1" is also true. At least on my sheet.

There might be some confusion caused by time zones. The DATE/TIME in project dependency columns does not consider user time zone. The DATE/TIME in system columns is presented in your local time. So in some cases 2 users looking at the same sheet will see different values in a system column. If you then use formulas on system columns you can get some interesting/unexpected results especially when combining users across multiple time zones. My head is hurting just thinking about it.

Michael G.

Hi Jason,

thank you for coming back to my original question

Bad news for me, because my construction to work with formulars using the TODAY-function in order to support REPORTS will not work ... I have to find another way (*optimistic*).

Kind regards, Michael

J. Craig Williams

Michael -

Sorry about derailing your thread. Really. I hate that and I apologize.

I will post a new one and come back here to provide a link to it.

Update: https://community.smartsheet.com/discussion/time-and-datetime-long

Craig

Caleb Klein

Rob,

Would there be a possibliity to get a copy of your Java program used for updated the fixed cell?

We are using a =Today() formula combined with LOOKUP formulas linked to Sights Metrics to get event dashboards, but right now are having to have the first person each day to open and save the base sheet, which defeats the automation aspect.

Thanks.

J. Craig Williams

Caleb,

While you wait for Rob to answer, for single sheets, I now use Zapier to solve this:

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

UPDATE: 2017-0917 - my solution has changed slightly and the post updated to reflect new method.

Craig