TIME and DATE/TIME (long)

J. Craig Williams

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

 

Brad Jones

Craig,

 

I'm not sure if this will get you the right 'real' time or the 'displayed' time, but I've had so much fun using the LEFT() and RIGHT() functions when I need to extract data from a cell.

 

I usually have to pull numbers for a lookup out of a hyperlink.  So, I use the RIGHT() to just grab the five right-most digits.

 

Now, if you set your time zone to English UK, then all you have to do is grab the five Right most digits 01:35

 

If you use English USA, you'd have to get more creative since you may not always have two digits in the hours section, but it would still work.  Just sometimes it would have an extra space at the beginning.  You might have to combine some left and right.

 

I haven't tested using the formula on a system column like modified, but it just may do the trick for you. 

J. Craig Williams

Brad,

 

That is an interesting possibility since

 

=Start1 + "" 

 

returns a string with date and time.

I'll give it some thought.

 

Craig