Subtract date from previous row
Hi, i need to subtract a datetime rows from consecutive row ID as a formula.
example:
Row ID: JA001, Row Datetime: 01/09/23 10:53 AM
Row ID: JA002, Row Datetime: 01/09/23 10:49 AM
can you help me?
Regards
Answers

I do not understand what you need.

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.

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 24hour 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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!