Days Interval

[reggieneo]

Hi,
What is the expresssion to get this days interval?
In Excel it is similar to end date - start date:

I need to determine the number of days between orders as below and put it in interval days column.
Key . Timestamp minus Key . Timestamp above it.
Example: 4/23/2020 - 4/9/2020 = 13.1 Days

Thanks

[Steve]

help.appsheet.com

TOTALHOURS()

Count of hours in Duration

[Vinit_Mishra]

Hello @reggieneo,

You could use App formula.

[orders] - [interval] /24

Thanks,
VInit

[reggieneo]

Hi, i have used spreadsheet formula to get that interval column value.

i would like to get that interval from appsheet expression so i can limit the use of spreadsheet formula.
the challenge is i need to do calculation like [_Thisrow].[Timestamp]-[the timestamp row above thisrow timestamp].
like this image:

[Suvrutt_Gurjar]

I believe the AppSheet expression will be a bit longish and something like below you may wish to try

DECIMAL(HOUR(MIN(SELECT(Table Name[Timestamp],
IF(MAX(Table Name[_RowNUMBER])=[_THISROW].[_ROWNUMBER], [_ROWNUMBER]= [_THISROW].[_ROWNUMBER], [_ROWNUMBER] > [_THISROW].[_ROWNUMBER])))-[Timestamp]))/24.00

Please test well. Expression assumes that new records will alsways be added in higher number rows.

Edit: Also the above expression is based on rownumbers ,so assumes there will be not row delete and balnk rows.I believe, you may need to replace [_ROWNUMBER] with possibly [Timestamp] itself or a column that keeps track of row addition sequence.

Please try below as well, not tested though.

DECIMAL(HOUR(MIN(SELECT( Table Name [Timestamp],
IF(MAX( Table Name [Timestamp])=[_THISROW].[Timestamp], [Timestamp]= [_THISROW].[Timestamp], [Timestamp] > [_THISROW].[Timestamp])))-[Timestamp]))/24.00

[Steve]

Try:

(
  TOTALHOURS(
    [Timestamp]
    - ANY(
      SELECT(
        table[Timestamp]
        (
          [_ROWNUMBER]
          = MAX(
            SELECT(
              table[_ROWNUMBER],
              ([_ROWNUMBER] < [_THISROW].[_ROWNUMBER])
            )
          )
        )
      )
    )
  )
  / 24.0
)

Replace table (twice) with the name of the table.

[Suvrutt_Gurjar · Accepted solution]

Please try

DeCIMAL(HOUR([Timestamp]- MAX(SELECT(Table Name[Timestamp],
IF(MIN(Table Name[Timestamp])=[_THISROW].[Timestamp], [Timestamp]= [_THISROW].[Timestamp],
[Timestamp]< [_THISROW].[Timestamp])))))/24.00

[Suvrutt_Gurjar]

Nice use of TOTALHOURS() function @Steve

Makes the expression much compact.

[reggieneo]

Hi Suvrutt,
it calculated the row where it is supposed to be 0 or blank :please see image:

it went one row up, and since the the most top row has no previous timestamp (timestamp above it) it should be 0 interval as this is the beginning of the row.

thanks.

[reggieneo]

Hi,
Steve’s works when i put comma after Table[Timestamp]. but it gave an 18,000 plus answer on the most top part , the rest of the rows are correct intervals.

Suvrutt’s works all the way form top to bottom.

I think I can mange to understand these expression i need for this issue.

thanks both of you Gents.

[Steve]

reggieneo:

Steve’s works when i put comma after Table[Timestamp].

Whoops!