Help converting an Excel formula to work in SS
I've inherited an Excel spreadsheet that is currently uploaded via DataShuttle into SS. However, I have to add six columns and formulas in Excel prior to completing the upload via DataShuttle (data is exported to Excel; six columns are added w/formulas; then DataShuttle moves it to SS).
I can't figure out how to convert the Excel formulas so they work in SS. I know SS doesn't have SEARCH function but I'm not getting things to work with FIND either.
Above is the screenshot from Excel. Formulas in the Hour, Minute, Second columns are the same mostly:
Hour: =IFERROR(VALUE(MID($C2,MAX(SEARCH(D$1,$C2)3,1),2)),0)
Minute: =IFERROR(VALUE(MID($C2,MAX(SEARCH(E$1,$C2)3,1),2)),0)
Second: =IFERROR(VALUE(MID($C2,MAX(SEARCH(F$1,$C2)3,1),2)),0)
Time Value: =TIME(D2, E2, F2) (this is also formatted as Custom (h:mm:ss).
I do have a formula in the Time Value column in SS. However, it doesn't populate in the HH:MM:SS. For example, if there is 11 minutes, 8 seconds in the Time column, it shows it as 0:11:8
Appreciate any help/input. Time formulas are not something I've worked with previously.
Thanks, Peggy
Best Answer

@Peggy Parchert  Hey, change your formulas as follows (everything will have appropriate number of zeroes now, no matter what):
Hour:
=IF(LEN([Time Value]@row) = 0, "", IF(FIND("hour", [Time Value]@row) = 0, "00", RIGHT(0 + SUBSTITUTE(LEFT([Time Value]@row, FIND(" h", [Time Value]@row)  1), " ", ""), 2)))
Minute:
=IF(LEN([Time Value]@row) = 0, "", IF(FIND("minute", [Time Value]@row) = 0, "00", RIGHT(0 + SUBSTITUTE(RIGHT(LEFT([Time Value]@row, FIND(" m", [Time Value]@row)  1), 2), " ", ""), 2)))
Second:
=IF(LEN([Time Value]@row) = 0, "", IF(FIND("second", [Time Value]@row) = 0, "00", RIGHT(0 + SUBSTITUTE(RIGHT(LEFT([Time Value]@row, FIND(" s", [Time Value]@row)  1), 2), " ", ""), 2)))
If you want this to be an actual time that can be added, subtracted, etc, and still be in the correct format, then change the JOIN to:
=TIME(Hour@row+":"+Minute@row+":"+Second@row)
If you need help with functions, use the search bar on the below link. This is a great place to read through for general knowledge as well.
Answers

Hi @Peggy Parchert  ChatGPT to the rescue! See below:
If the time value has come in as a string and is formatted as "hh:mm:ss", you can still extract the hour, minute, and second using formulas in Smartsheet. Here are the updated formulas for extracting the hour, minute, and second from a string representation of time:
1. **Extract Hour from String**:
=LEFT([Time Value]@row, FIND(":", [Time Value]@row)  1)
2. **Extract Minute from String**:
=MID([Time Value]1, FIND(":", [Time Value]@row) + 1, FIND(":", [Time Value]@row, FIND(":", [Time Value]@row) + 1)  FIND(":", [Time Value]@row)  1)
3. **Extract Second from String**:
=RIGHT([Time Value]@row, LEN([Time Value]@row)  FIND(":", [Time Value]@row, FIND(":", [Time Value]@row) + 1))
These formulas assume that your string time values are in column [Time Value] and you want to extract the hour, minute, or second from the first row of that column. Adjust the cell references as needed based on your actual data structure.

Hello @Lucas Rayala
Thank you for the quick response. The data I'm attempting to extract is in the Time column (numbers & text) into the Hour, Minute, Second columns respectfully. My thought was once I have the numbers from the text in the Time column, I can use a JOIN function to get them into the h:mm:ss format (I think 🤔).
I didn't state this clearly now looking over what I initially asked. My apologies. I know the Excel formulas that are currently in use (listed above) will not work in SS. This is where I'm struggling  I can't seem to figure out how to convert them to work in SS. The Time Value column (in the screenshot above) is how I want the final output to look.
Peggy

Ah, very similar, but you’ll need to replace the “:” with “ h”, “ m”, “ s”, wrap them in an IFERROR function for when the units don’t exist, and adjust the “+ 1” accordingly. I’m not at a computer now, but if you still need help ping me and I’ll get you it when I’m back.

Hello @Lucas Rayala
I got the Hour formula to work: =IFERROR(LEFT([Time]@row, FIND("h", [Time]@row)  1), 0)
However, the Minutes formula is giving me an #INVALID VALUE error and the Seconds formula is pulling in text  not numbers  no matter what I change the "+1" to.
Suggestions?
Peggy

Hi @Peggy Parchert  here you go, these should work. I modified them a bit to provide numeric values and clear up errors you may encounter. I think this should cover it:
Hour:
=IF(FIND("hour", [Time Value]@row) = 0, "", VALUE(LEFT([Time Value]@row, FIND(" h", [Time Value]@row)  1)))
Minute:
=IF(FIND("minute", [Time Value]@row) = 0, "", VALUE(RIGHT(LEFT([Time Value]@row, FIND(" m", [Time Value]@row)  1), 2)))
Second:
=IF(FIND("second", [Time Value]@row) = 0, "", VALUE(RIGHT(LEFT([Time Value]@row, FIND(" s", [Time Value]@row)  1), 2)))
Let me know if you run into a snag. I would make these all column formulas as well.

I really do appreciate you helping me  thank you does not seem to say enough.
I've still got some issues. I modified your formula so that 00 displays if blank for the Hour, Minute, and Second columns. The Hour column is working great. I thought the Minute and Second formula was working. However, when I made them column formulas (❤️ column formulas), I have lines showing #INVALID VALUE.
Below is a screenshot:
It looks like those in the Minute column showing #INVALID VALUE they are single digit minutes; same thing with Second column  only those with single digits are showing as #INVALID VALUE.

Hi @Peggy Parchert, just a bit of troubleshooting. When there is a single digit you are grabbing spaces that the value function is erroring on. Add a substitute to get rid of spaces if they exist:
Hour:
=IF(FIND("hour", [Time Value]@row) = 0, "", VALUE(SUBSTITUTE(LEFT([Time Value]@row, FIND(" h", [Time Value]@row)  1)," ","")))
Minute:
=IF(FIND("minute", [Time Value]@row) = 0, "", VALUE(SUBSTITUTE(RIGHT(LEFT([Time Value]@row, FIND(" m", [Time Value]@row)  1), 2), " ", "")))
Second:
=IF(FIND("second", [Time Value]@row) = 0, "", VALUE(SUBSTITUTE(RIGHT(LEFT([Time Value]@row, FIND(" s", [Time Value]@row)  1), 2), " ","")))

That did it!!!!! Thank you so much!!!!! 😊
Couple more questions:
 I'm using a JOIN in the Time Value column (formula: =JOIN(Hour@row:Second@row, ":"). However, it doesn't populate with 2 digits for mm or ss if they are single digits in their respective columns. Is it possible to format this column so that it adds a "0" to the front of the single digit so that it follows h:mm:ss format?
 I've never used VALUE or SUBSTITUTE before in formulas. Could you direct me to somewhere I can learn more about these functions and how to use them?

@Peggy Parchert  Hey, change your formulas as follows (everything will have appropriate number of zeroes now, no matter what):
Hour:
=IF(LEN([Time Value]@row) = 0, "", IF(FIND("hour", [Time Value]@row) = 0, "00", RIGHT(0 + SUBSTITUTE(LEFT([Time Value]@row, FIND(" h", [Time Value]@row)  1), " ", ""), 2)))
Minute:
=IF(LEN([Time Value]@row) = 0, "", IF(FIND("minute", [Time Value]@row) = 0, "00", RIGHT(0 + SUBSTITUTE(RIGHT(LEFT([Time Value]@row, FIND(" m", [Time Value]@row)  1), 2), " ", ""), 2)))
Second:
=IF(LEN([Time Value]@row) = 0, "", IF(FIND("second", [Time Value]@row) = 0, "00", RIGHT(0 + SUBSTITUTE(RIGHT(LEFT([Time Value]@row, FIND(" s", [Time Value]@row)  1), 2), " ", ""), 2)))
If you want this to be an actual time that can be added, subtracted, etc, and still be in the correct format, then change the JOIN to:
=TIME(Hour@row+":"+Minute@row+":"+Second@row)
If you need help with functions, use the search bar on the below link. This is a great place to read through for general knowledge as well.

To add the zero, I added a zero to the left of every number, no matter what. Then I took the two rightmost digits using the RIGHT function. If it was two digits already, it the RIGHT function trimmed the zero off. If it was previously one digit, the zero was brought with.

@Lucas Rayala  thank you so very much! Appreciate all the help. I will be looking further into those functions.
Thanks, Peggy
Help Article Resources
Categories
Check out the Formula Handbook template!