Subtract date from previous row

Julio A

Hi, i need to subtract a date-time rows from consecutive row ID as a formula.

example:

Row ID: JA001, Row Date-time: 01/09/23 10:53 AM

Row ID: JA002, Row Date-time: 01/09/23 10:49 AM

can you help me?

Regards

Mike TV

I do not understand what you need.

Julio A

Hi,

I need to subtract the previous start date.

Each line has its ID, which is consecutive.

=(Line 2 - PTFE00172522 - 01/08/23 10:25 PM) - (Line 1 - PTFE00172422 - 01/08/23 8:10 PM)

= 125 min.

Jeff Reisman

@Julio A

There are probably a dozen posts in this Community on the extraordinary lengths one must use to perform calculations on time in Smartsheet. Smartsheet only performs date calculations, not time.

The strategy I recommend involves extracting the time portion of the datetime value into a helper column. Then you'll want to use a formula to convert that into 24-hour time, and then convert that into hours and decimal portions of an hour. For instance, for 8:10 PM, the hour is 20, and 10 minutes divided by 60 minutes becomes .167, so the mathematical value would be 20.167. For 10:25 PM, the hour is 22, and 25/60 = .417, so it's 22.417. Subtract 20.167 from 22.417, you get 2.25. Multiple the integer portion by 60 minutes to get 120 minutes, and multiply the decimal portion (.25) by 60 to get 15 minutes, then add them together to get 135 minutes.

Seems simple enough, right?

But what happens if you want to subtract 01/08/23 10:25 PM from 01/09/23 1:15 AM? Or what if there's a weekend in between? It gets even more crazy and complicated then.