Hi everyone,
I have a few odd numbers that have two dots like this 00.00.00 (they're exported from a website and show up like that). How do I do a Min function in column C to see the lowest number?
I would like to keep the formatting the same if possible, because the sheet needs to be imported to the website again.
Try:
=TEXT(AGGREGATE(15,6,SUBSTITUTE(D2:I2,".",":")+0,1),"hh.mm.ss")
P/s: if you want it to be real time, but display under "hh.mm.ss"
=AGGREGATE(15,6,SUBSTITUTE(D2:I2,".",":")+0,1)
Format as "hh.mm.ss"
Last edited by bebo021999; 10-14-2020 at 02:35 AM.
Quang PT
Perfect! Thank you very much bebo021999!
Are you sure about that??
the original format is hh.mm.decimal seconds (there are several seconds values greater than 59). Bebo's formula works for hh.mm.ss
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Try this instead:
=AGGREGATE(15,6,LEFT(D2:I2,2)/24+MID(D2:I2,4,2)/1440+RIGHT(D2:I2,2)/144000,1)
Here's a better version:
=AGGREGATE(15,6,DOLLARDE(SUBSTITUTE(D2:I2,".","",2),6000)/24,1)
format as time.
Whoops celebrated slightly too early... I see any number that ends with .60 or higher it changes the actual number.
For instance 00.32.60 becomes 00.33.00.
Or 00.33.75 becomes 00.34.15.
Any idea how to get around that so it doesn't change the numbers?
I beat you to that conclusion!! See posts 4-6, especially post 6.
I converted hh.mm.decimal seconds to hh:mm:ss and then calculated the minimum.
Or do you REALLY want to keep the odd hh:mm:decimal seconds format??
You should type in the expected outcome in yellow range.
Is "00.35.61" is 0h 35 minutes and 61 seconds, equal 0h 36 minutes and 1 second?
No it looks like a decimal second.
It is indeed a decimal second. My original list has a hundred cyclists on, and sometimes the difference between who comes out on top comes down to the decimal second.
So 00.35.61 is actually 35.61 seconds. The top 5 guys all have 00.33.xx times, only separated by the decimal.
I wouldn't mind the time formats, it's just my table on the website gets real confused and can't sort properly if it's not in the 00.00.00 format.
Now I see it is mm.ss.00
Is it simple that replace the first "." with ":" to be real time, then format "mm:ss.00"
Please Login or Register to view this content.
Excellent work gentleman, that's perfect! Thank you very much!
As a token of my gratefulness I have depicted you as you both are, champions!
Please note this is actually not a depiction of a real life event, but an extremely well done photoshop job
Hahaha,
Nice pic.
According to athletes ID number, I guess I am right person?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks