Help with converting Timestamp into minutes

Hi all,

I am a very novice Excel user and I've just started messing around with building a database of calls taken in a contact center. Would really appreciate any help on this. I need to convert timestamps to minutes. I want any timestamp i post in column A to automatically appear in column B as minutes.

The time stamps that I have are in a ##H:##M:##S format.

The issue arises because the time stamp is in 2-3 different types. If the timestamp does not have hours, it only shows up as ##m:##s.. This makes any generic formula I could find to convert it useless as the length of the data varies.

Here are a few example timestamps, any help would be greatly appreciated!

0m:4s
11m:58s
1h:43m:8s

This needs to convert to minutes.. Thanks in advance!

Originally Posted by Rythwin

0m:4s
11m:58s
1h:43m:8s

This needs to convert to minutes.. Thanks in advance!

You say you want this converted to minutes... whole minutes or decimal minutes? If whole minutes, should we round the seconds normally? always round any seconds up? ignore the seconds completely?

Originally Posted by etaf

are these text format entries
right click and format as number - do they change

I assume the minutes do not go above 59m
but change to Hours
and are seconds ALWAYS displayed or if exactly 4 mins for example are they shown as
4m
or
4m:0s

They are Text format - Formatting as number does not affect them
Seconds are always displayed, even if it lands on exactly 4 minutes, it ends up as 4m:0s. I'll post more exact timers below (after my reply)

Originally Posted by Rick Rothstein

You say you want this converted to minutes... whole minutes or decimal minutes? If whole minutes, should we round the seconds normally? always round any seconds up? ignore the seconds completely?

I want to convert it int decimals, up to 4 decimal points - the purpose is to calculate the average time spent on calls so the more accurate the better

**

The values going from a 1 second record to a 1 hour record would be as follows:

0m:5s
0m:13s
1m:0s
12m:3s
12m:15s
1h:4m:8s
1h:11m:47s

Please do let me know if you need any more information! Thanks again!

Originally Posted by protonLeah

A little long but,

Please Login or Register  to view this content.

Thank you so much for that!

One more request, is it possible to remove the "m" at the end of it? Thanks!!

Originally Posted by protonLeah

A little long but...

I believe this shorter formula will also work...

Formula:

Please Login or Register  to view this content.

One way:

Please Login or Register  to view this content.

Originally Posted by Phuocam

One way:

Please Login or Register  to view this content.

NICE FORMULA!!!

Thank you guys! All these commands work, I'll try to learn from them.

Cheers!