Converting a number of a format (2:16) into minutes and seconds

Copper Contributor

My Dear Friends,

 

  • I have been struggling for almost half a day (!) now to convert some number that my team sends me, in excel, which are in the format like 2:16 or 1:07 etc.
  • These are the minutes & seconds recorded by them for a certain activity. The excel (sent by team) shows these number / cells formatted as 'General' and no special formatting is applied by the team.
  • I now intent to convert this into seconds to find the average etc.
  • I tried several options like 'Custom formatting' to '[ss]' etc. but none seems to be working.
  • The only way it worked till now is when I add "00:" manually before each number. i.e. 2:16 is manually changed to 00:02:16 --> the I am able to convert it to seconds (using [ss]) but it is impossible to manually add "00:" before each number.

Any kind suggestions from you would be really, really appreciated!

 

Regards,

Your brother in distress :(

 

10 Replies

Hi Jayant,

 

If you'd only like to calculate the average you may use

=AVERAGE(<you cells>)*24*60

which will return the average in seconds.

 

Hi there,

excel assumes 2:16 is automatically hour:minutes; as you said, if you manually insert "00:" it understands that 2 is minutes and not hours.

 

I don't think adding "00:" manually in each cell should be worth it, so I'm asking IF you can add a column to the file, to do your job.

 

If you can, then it's easy!

=TEXT("00:"&TEXT(HOUR(A2);"00")&":"&TEXT(MINUTE(A2);"00");"[ss]")

It just convert a new string of values made by adding "00:" to the given datas (intended as hour and minutes) to convert it again in [seconds] after it understands that there are 3 slots of numbers, so it must be hours:minutes:seconds.

 

See attachement!

 

edit: or just like mr.Baklan said, just multiply the given data *24*60 and there will be the seconds. Then you'll make all ops you want with that data. 


@Sergei Baklan wrote:

Hi Jayant,

 

If you'd only like to calculate the average you may use

=AVERAGE(<you cells>)*24*60

which will return the average in seconds.

 


Dear @Sergei Baklan,

I am so thankful to you. Honestly, I wasn't even sure anyone would even reply to my message! 

Thanks so much for taking out time and replying to me - your solution worked beautifully!

I am smiling ear to ear as I thank you :)

God bless!


@Arul Tresoldi wrote:

Hi there,

excel assumes 2:16 is automatically hour:minutes; as you said, if you manually insert "00:" it understands that 2 is minutes and not hours.

 

I don't think adding "00:" manually in each cell should be worth it, so I'm asking IF you can add a column to the file, to do your job.

 

If you can, then it's easy!

=TEXT("00:"&TEXT(HOUR(A2);"00")&":"&TEXT(MINUTE(A2);"00");"[ss]")

It just convert a new string of values made by adding "00:" to the given datas (intended as hour and minutes) to convert it again in [seconds] after it understands that there are 3 slots of numbers, so it must be hours:minutes:seconds.

 

See attachement!

 

edit: or just like mr.Baklan said, just multiply the given data *24*60 and there will be the seconds. Then you'll make all ops you want with that data. 


Dear @Arul Tresoldi,

Thank you so much for your kind help! As I wrote to @Sergei Baklan, I wasn't even sure if anyone would even reply to my message! 

You are really kind - just when I wasn't sure how to use the formula you shared, I see a lovely attachment, explaining how to use it. OMG! You're so cool!!

Thanks so much for taking out time and replying to me - your solution worked beautifully!

I am smiling ear to ear as I thank you :)

God bless!

Jayant, you are welcome

I want to go the other way, example given: I have number of seconds in my case, the average number of seconds to cover 5oo meters rowing: i.e. 160 seconds, need to be converted into a format: mm:ss, leading to 02:40 164 seconds leading to 02:44

@jjrtoussaint-nl 

In Excel actually days are integers and time is decimal part of the number. In calculations one day is equal to 1, thus one hour = 1/24; one minute = 1/24/60 and one second = 1/24/60/60. With that you may do conversions from numbers to time and back, just apply proper format where necessary. Your sample:

image.png

@Sergei Baklan one wishes life will always be so easy, super, it is not just the formula that helped, but also the way approaching the problem, Thanks very much!