Help converting a time formula in Excel to SmartSheet

Lisa

I have an Excel spreadsheet with a column called Set times, an example value being -

6:30am > 6:45am (00:15 total)

This value is unchangeable because it is exported like this from another program and is unchangeable.

I have two more columns in Excel, called Time IN and Time Out

The formula for Time In is -

=TIMEVALUE(CONCATENATE([@Column4],":",[@Column5]," ",[@[AM/PM]]))

and Time Out is -

=TIMEVALUE(CONCATENATE([@Column2],":",[@Column3]," ",[@[AM/PM2]]))

The Time In converts to 6:30:00 AM and the Time Out converts to 6:45:00 AM

How can I achieve this in SmartSheet?

thanks in advance!

James Keuning

Are you looking for guidance on how to take this from beginning to end? Or do you have some ideas about what you do first, or what you do last, or what you might do in between?

Because first you need to break the Set Times field into columns. I would use a combination of FIND and LEFT, RIGHT, and MID to do that. In other words, find the first space, and take everything to the left of that to get the first time, and then something a bit more complicated to get the second time. But that will get you there.

And then you need to break out the hours and minutes into columns, and use the AM/PM to convert the hour to 24 hours.

Once you get it into hours and columns you can convert the minutes to tenths of an hour. From there you can perform the calculations and convert the result from tenths of an hour back to minutes.

Check out this thread here. The opening question includes a formula for converting to 24 hours, and the first link is about calculating time worked, which is what you want.

Formulas for Calculating Time

by @Paul Newcome