Converting and calculating average elapsed time

**jamminalley** · 05-04-2010, 04:25 PM

I'm trying to use Excel to calculate the average elapsed time (in seconds) it takes a student to perform an online exercise delivered via a web-based learning management system. The format in which elapsed time is reported in this sytem is a string such as "1 min 20 secs", "19 mins 01 sec" or "45 secs". (See attached worksheet.)

Which Excel format (e.g. mm:ss) would be most appropriate for performing such a calculation, and could anyone help with a formula to convert these strings into the appropriate format?

Many thanks in advance.

**DP978** · 05-04-2010, 04:38 PM

Put this function in a cell (B23), copy it the amount of columns you have over, and then the amount of rows down.

Please Login or Register  to view this content.

Convert it back after you have a calculation.

Note: This converts everything, in a makeshift way, to minutes.

**martindwilson** · 05-04-2010, 04:40 PM

what function is that then? ha ha now it's there lol

**DP978** · 05-04-2010, 04:42 PM

Well the appropriate answer, if this was a programming forum, would say to make the back end application handle the formatting. Excel is not really meant to do data cleansing, it is the end user interface.

Oh! haha yes I didnt put any function up, it was a mistake

**jamminalley** · 05-04-2010, 05:24 PM

Many thanks, DP978.

Actually, I've had a post about this on the back end-application's user forum for a while now, but it's much less active than this one.

Instead of converting back to "X mins Y secs", how to go from minutes-in-decimals to mm:ss format?

**daddylonglegs** · 05-04-2010, 05:43 PM

You could use DP978's formula and then just divide by 1440 (number of minutes in a day), i.e.

=(IFERROR(MID(B2,1,FIND(" min",B2)),0) + IFERROR((MID(B2,FIND(" sec",B2)-2,2)/60),0))/1440

format result cell as [mm]:ss

**jamminalley** · 05-04-2010, 06:09 PM

Terrific! Thanks, daddylonglegs.

**Borko** · 03-12-2012, 10:31 AM

sorry I have this simple spreadsheet--so I thought

I need to average the "elapsed time from 1st contact column"

any help is appreciated as this is due in 3 hours and I still have to graph it out