Duration in hours formula returning #INVALID VALUE

JLK

Hello, trying to calculate the duration in hours between two dates, see below:

But the formula returns #INVALID VALUE.

After reading another help post, I have edited the column properties so that the data type is date, but doing so removed the time stamp and the formula still returms #INVALID VALUE.

So now I have two questions:

1. Have I permantently lost my time stamp data by switching the column properties?
2. What am I doing wrong in this formula?

Thanks in advance fror the help.

• Jennie

Jeff Reisman

@JLK A few things to point out:

• Smartsheet and time calculations are like oil and water. They don't mix well.
• You're definitely going to need the timestamps for this to work.
• I could write a book called "Smartsheet and Time Calculations: How to Give Yourself a Headache"
• In your formula, VALUE(RIGHT([application submitted]@row, 2) / 60 and its counterpart are dividing the number of seconds by 60, not minutes.
• #INVALID VALUE error usually means the formula contains a number outside of the range that a function's argument expects. This usually involves the FIND function.
• In your formula, VALUE(LEFT([application submitted]@row, FIND(":", [application submitted]@row, 1) -1)) would pull everything to the left of the first colon from the left. Hence the error, because it can't find a numeric value of "2021-01-01 19". You would need this to be VALUE(MID([application submitted]@row, (FIND(":", [application submitted]@row, 1) -2), 2)) to collect just the 22 from 22:02:36 for instance. But you're still left with trying to collect the minutes portion too.

Now that that is out of the way, here is a way that might work. I like to break up some of the more complex stuff into smaller chunks:

To start with, create some helper columns to pull the timestamp values into their own columns, and drop the seconds value (the rightmost colon and two digits.) Let's call this first column PCTime. Use this formula: =MID([profile created]@row, 12, 5). For row id 8873, this would leave you with 19:27.

Next you'll want to convert this value into an integer and a decimal. Call this helper column PCTimeFinal. =VALUE(LEFT(PCTime@row, 2) + (VALUE(RIGHT(PCTime@row, 2) / 60) . This leaves you with 19.45 stored as a number.

Repeat for the application submitted time (Helper columns ASTime and ASTimeFinal.)

Now just as you did before, convert the number of days into hours, subtract the value of PCTimeFinal from ASTimeFinal, and add the results together:

=(([application submitted]@row - [profile created]@row) * 24) + (ASTimeFinal@row - PCTimeFinal@row)