Forum Discussion
Converting a number of a format (2:16) into minutes and 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.
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!