Forum Discussion
Converting a number of a format (2:16) into minutes and seconds
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 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.
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 SergeiBaklan, 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!
That also works
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.
- 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
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:
SergeiBaklan 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 SergeiBaklan,
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!
Jayant, you are welcome
