Ref TimeStamp to find difference between 2 times

BrianDN

Hi,

I'm making a Log IN & OUT application.

I've created this application in Google Sheets / Forms but I'd like to create the same in SS.

Process:

User fills out form; selects "IN" or "OUT" along with company name and person name

(Created Data column acts as Timestamp)

Issue:

When I reference the Created Dates to format in a understandable column display in-which Time IN and Time OUT reference the Created Date I can NOT subtract IN from OUT:

To reference the Created Date column titled Timestamp I use =TimeStamp@row + ""

But it seems that SS doesn't see this as a unit only a value which can not be used in a calculation.

Is there anyway around this???

Thanks for any help with this

Paul Newcome

Here are a number of solutions for calculating time in Smartsheet:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

BrianDN

Thanks Paul,

However, I'm not having an issue calculating the the times from the Created Date column.

My issue is referencing that column to fill two columns side-by-side for Time IN & Time OUT, then subtracting Time IN from Time OUT, because SS sees the Data & Time as mathematical when it is in the original Created Date column that updates as form submissions populate. Anytime the Data and time show elsewhere, they can NOT be calculated.

For instance TimeStamp column auto adds the Created Date; so the first and second row can be calculated, however, these rows are then referenced to display side-by-side in an easy to read format, however, they can no longer be calculated:

How do I get around this?

Paul Newcome

Right. You would use a method (or combination of) from the linked thread to strip out the time into a helper column then use one of the methods outlined to run those calculations.

BrianDN

Ok, I follow.

I will read deeper and experiment for awhile then post my findings. Thanks Paul