4 Replies Latest reply on Feb 14, 2012 8:35 AM by Kristian Desch

# Merging 2 Strings With Time Formatting

I'm working on a computing average pace (minute/mile) after collecting distances and times. I believe to have all of the calculations figured out but am coming up short on final formatting (I believe).

Here is what I have so far-

Calc 1

ROUND([Minutes]/[Distance],2)

This returns a figure like 8.45 minutes -  not the 8:27 I'm seeking.

Calc 2

FLOAT(LEFT(STR([Min/Dist(rounded)]),FIND(STR([Min/Dist(rounded)]),".")-1))

This gives all values left of the decimal point

Calc 3

(FLOAT((MID(STR([Min/Dist(rounded)]),FIND(STR([Min/Dist(rounded)]),".",1)))))*60

Grabs the digits right of the decimal and then converts to seconds

Calc 4 (Where I've hit a roadblock)

I was hoping to combine the strings and then format as minutes:seconds but this has proven to be more difficult than I anticipated. Is there a better approach than what I took above?

Jacob

• ###### 1. Re: Merging 2 Strings With Time Formatting

Jacob the attached file might work a little better for you.

--Shanw (misspelled my own name)

Richard was right putting in some description so you don't have to open the workbook is handy. (Click the picture to read it.)

So Richard where's the new avatar you promised?

Richard, Very Nice!

Message was edited by: Shawn Wallwork

• ###### 2. Re: Merging 2 Strings With Time Formatting

Exactly what I was looking for. I appreciate the notes which made it easy for me to walk through your calculation.

Thank you for your help Shawn!

• ###### 3. Re: Merging 2 Strings With Time Formatting

Jacob, glad I could help. I also wanted to mention that using the pop-up tooltip on the red X can be helpful, but some times it can lead you off chasing floats & strings & fighting the aggregate/non-aggregate battle, over-complicating everything. Other than letting it help me chase parenthesis  and commas I usually take what it says with a grain of salt. Happy coding.

--Shawn

• ###### 4. Re: Merging 2 Strings With Time Formatting

It helped me as well.  thanks.