TIME and DATE/TIME (long)

Hi,

This is not a new thread, but rather a branch of the thread derailed (mostly be me) of this thread

https://community.smartsheet.com/discussion/question-formula-results-reports

starting here:

https://community.smartsheet.com/discussion/question-formula-results-reports?page=1#comment-17893

The derailed topic is concerning the DATEONLY() formula.

In August, the functionality changed and broken several of my project sheets.

I wrote a post on the Community and contacted support.

Support told me that DATEONLY() no longer does anything.

I have been saying it is broken (which is true) and that it does not nothing (which is false) whenever someone shows a formula that includes the DATEONLY() functionality.

Jason (an employee of Smartheet) has built a sheet to prove that DATEONLY() does indeed do something.

With a little bit of sleep, I think this is right, but I still think it is broken.

To further complicate things, the way that Smartsheet handles TIME in the two different column types that are DATE/TIME related is different and causes confusion.

However, I've had a little bit of sleep and am a bit clearer in head this morning, so I'm going to tackle it one more time.

For the examples below Start1 and Finish1 are Date/Time fields associated with the Project Settings. Modified1 is the Modified (Date) System field.

All are on the same date, the Start is at the beginning of the workday, Finish somewhere but not at the end of the workday, the row was modified on the same date too, but after the beginning of the start of the workday.

Craig's claim #1

1. Finish1 - DATEONLY(Finish1) = some non-zero value

This was true prior to Aug 2016. It is no longer true.

Jason showed using a CheckBox column that

Finish1 = DATEONLY(Modified1)

I restated my claim #1 and realize now that as a mathematician, I was missing the obvious.

By the Commutative Property if A = B, then A - B = 0

I still wanted to believe claim #1 (I sometimes make mistakes)

My claim, however is based on the fact that Finish1 has both date and time.

For example, this is not true:

1.1 = INT(1.1)

However, I was treating DATEONLY() as the INT() function but only with dates and time.

That does not appear to be quite the case, otherwise

Finish1 - DATEONLY(Modified1) 

or

DATEONLY(Start1) - Modified1

would be non-zero. But they are not.

Craig's claim #2

2. Handling of time by Smartsheet appears to cause problems.

I am currently in the eastern USA (EST), everything I can uncover leads me to believe that nearly all of the time zone settings for a specific sheet are EST.

The sheet is shared to (but not owned) by a user in the UK (GMT).

Late at night, when the modified date was nearing midnight,

02/02/17 10:50 PM

=DATEONLY(Modified1) + ""

returns 02/03/17

This is a known issue and has been discussed at length elsewhere.

(Phone call derailed my train of thought. Crap)

Craig's claim #3 

3. How I think it works.

a. Unlike the INT function for a floating point number, DATEONLY is not really "stripping out the time" but is rather converting the value from one data type (DATE/TIME) to another (DATE).

If any values of a formula are DATE, then the formula is treated a DATE.

Non of the current examples in Jason's sheet disprove this.

Testing with a DATE/TIME field and a DATE field is needed.

b. The two types of DATE/TIME fileds are different. Underlying Smartsheet cells are two values. They are actual value and displayed value. 

We see 02/02/17 for Finish1 but the actual value is something different - because of the time component.

We see 02/02/17 3:49 PM for Modified1 value but testing shows that the underlying value is different.

This impacts the time-zone issue more (I think) than the DATETIME() issue.

What I want:

1. I want to be able to strip out / capture the TIME component of a DATE/TIME field

DATEONLY() is focussed on the DATE component. I need the get the time.

That's why I was using

Finish1 - DATEONLY(Finish1) 

in the first place.

2. I want time fixed - but my wants are not clearly defined, so I will work on them.

(The problem is actual vs display vs how formulas work on timestamps)

(Another phone call - sigh)

To paraphrase Blaise Pascal, if I had more time, this thread would be shorter.

Craig