Ok, so I've gotten as far as getting my durat...

[Edgar_Butron]

Ok, so I’ve gotten as far as getting my duration for each operation I’m needing. Now, I’ve been wanting to filter the data in two columns (Repair Order and Operation) in my [Entries] table in order to sum the durations and be able to view the total time value in a separate table [Repair Order]. In order to filter and sum this data I used

=SUM(SELECT(Entries[Duration Value],AND([Repair Order]=[_THISROW].[Repair Order], [Operation]=[_THISROW].[Operation])))

I use smart sheet so I ran into the issue of not being able to sum a ‘Duration’ since it wasn’t a value/number. I referenced a Timesheet app that pointed me in the direction of creating an app formula via a virtual column to convert the time value into a decimal format. In order to convert this time value in the virtual column ‘Duration Value’ I used

=HOUR([Duration])([Minutes]/60)+([Seconds]/3600)+[Hours]

This however, did not work and I am still unable to sum my durations for an ‘Operation’ from a specific ‘Repair Order’ and view that information in another table. I’ve given screen shots to detail it out a bit better as well. Any suggestions?

[Aleksi_Alkio]

What accuracy do you want to use? Is one minute enough or 15 minutes or something else?

[Edgar_Butron]

I’d like to have it down to the minute. The timestamp is down to the second so if that is possible then i wouldn’t be against being that accurate.

[Aleksi_Alkio]

It can fail if the formula is not well constructed. You should take a precausion what can go wrong and then arrange a suitable formula for those reasons.

[Edgar_Butron]

Yes, at least i’m able to get a duration in decimal form. Thanks for the help on that! I guess I just can’t wrap my head around my I can’t sum the decimals with the formula I’ve given.

[Aleksi_Alkio]

Are you using the SUM(SELECT…) expression in a normal or virtual column? If it’s in normal column, it won’t calculate the value if you don’t open the record. Because you are reading values from another table, you should use virtual.

[Edgar_Butron]

I actually had all of my columns as normal. So you are saying that my [hour], [minute] and [second] columns can stay as “normal” but I should change my [Duration] column in the other table that has the sum expression as virtual?

[Edgar_Butron]

Because how would this value display in my smart sheet if I make it a virtual column?

[Aleksi_Alkio]

When you want to calculate the result from other tables, you have two options in generally. Adding a virtual column to calculate the value or using a sheet formula in Smartsheet. If you use virtual column, it won’t save any data into the Smartsheet from that column.

[Aleksi_Alkio]

You were quite close… you can calculate the duration with the virtual column. Your formula just wasn’t correct exactly. HOUR([Duration])*60+MINUTE([Duration])+SECOND([Duration])/60 will give the duration in minutes. When you know that, you can sum them together.

[Edgar_Butron]

is my sum formula correct? because it is still referencing the [Duration Value] column which is my virtual column. Or should I reference the [Duration] column which is my physical spreadsheet column? I ask because I still wasn’t able to sum the data and populate my [Duration] column in the ‘Repair Order’ Table.

[Edgar_Butron]

Or do you think its smart sheet that is unable to read and give the value?

[Aleksi_Alkio]

You should add that formula that I described into your virtual column. When the virtual column is calculated, you can sum those values and save that decimal into the spreadsheet.

[Edgar_Butron]

Ok, so i’ve got a decimal value to pull up and represent my Duration in the [Entries] Table but I still can’t get the sum of the values to pull up on my [Repair Order] table. Is the =SUM(SELECT(Entries[Duration Value],AND([Repair Order]=[_THISROW].[Repair Order], [Operation]=[_THISROW].[Operation])))

formal incorrect?

[Aleksi_Alkio]

Yes the format is basically correct. What is the result of this formula?

[Edgar_Butron]

I’m still getting blank cells in my [Repair Order] Table for the Duration column. Now, I don’t have all my cells fully populated in the Operations column since this is a “test” sheet, but you think it would still populated any cells in the Duration column that had entries in it, correct?

[Edgar_Butron]

The entries you see highlighted below are for Repair Order 1- Bob Marley, Operation 70 and 100 so you would think it would populate Duration value for rows 6 and 9 in the [Repair Order] table. Does the expression glitch if it is unable to populate every Entry?