Help converting an Excel formula to work in SS

Peggy Parchert

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

Lucas Rayala

@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.

Smartsheet help | Smartsheet Learning Center

Lucas Rayala

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.

Peggy Parchert

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

Lucas Rayala

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.

Peggy Parchert

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

Lucas Rayala

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.

Peggy Parchert

@Lucas Rayala

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.

Lucas Rayala

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), " ","")))

Peggy Parchert

@Lucas Rayala

That did it!!!!! Thank you so much!!!!! 😊

Couple more questions:

1. 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?
2. 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?

Lucas Rayala

@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.

Smartsheet help | Smartsheet Learning Center

Lucas Rayala

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.

Peggy Parchert

@Lucas Rayala - thank you so very much! Appreciate all the help. I will be looking further into those functions.

Thanks, Peggy