Trying to convert a time value into minutes. It can be in the format of 1:11:11 or 11:11:11 (hours minutes seconds). Or it can be in the format of 1.11:11:11 (days, hours, minutes seconds).
If no hour, this one works:
= IF(HOUR(G6)>0,HOUR(G6)*60+MINUTE(G6), MINUTE(G6)))
This works if there are hours
=(LEFT(G19,FIND(".",G19)-1))*1440+(MID(G19,FIND(":",G19)-2,2))*60+MID(G19,FIND(":",G19)+1,2)
But I can't come up with something that works for either case
Any ideas?
Thank you
You should be able to do it like this:
=IF(ISNUMBER(FIND(".",G6)),LEFT(G6,FIND(".",G6)-1))*1440+RIGHT(G6,LEN(G6)-FIND(".",G6))*1440,G6*1440)
Hope this helps.
Pete
or try
=IF(ISNUMBER(Find(".",G6)), (LEFT(G19,FIND(".",G19)-1))*1440+(MID(G6,FIND(":",G6)-2,2))*60+MID(G6,FIND(":",G6)+1,2), HOUR(G6)*60+MINUTE(G6))
Not sure why you had the IF statement on the first formula as HOUR(G6) will resolve to 0 and should not be an issue
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Try this
=ROUND(IFERROR(G6*1,MID(G6,FIND(".",G6)+1,9)+LEFT(G6,FIND(".",G6)-1))*1440,0)
Audere est facere
This returns "false"
Which one?
Here are some of the values from my spreadsheet:
0:00:08 -- should convert to 0 minutes
1:13:37 -- should convert to 73 minutes
20:06:43 -- should convert to 1206 minutes
1.18:47:55 -- should convert to 2567
this formula
=(LEFT(G19,FIND(".",G19)-1))*1440+(MID(G19,FIND(":",G19)-2,2))*60+MID(G19,FIND(":",G19)+1,2)
will work for the values containing hours but fails if value has no hours
thanks everyone for trying to help me!
Don't you mean days?Originally Posted by SueC
My suggestion almost gets those values but I rounded to the nearest minute, you seem to be rounding down so try this variation
=INT(IFERROR(G6*1,MID(G6,FIND(".",G6)+1,9)+LEFT(G6,FIND(".",G6)-1))*1440)
Sorry, first time using Forum. But soo happy I found you all! Petes did not work, Chemist returned false, daddylonglegs returned wrong number of minutes. But thanks again for trying. My previous post has a litte more data. Hope I am being clear enough and not confusing everyone.
This one works with all 4 of your examples:
=ROUNDDOWN(IF(ISNUMBER(G6),G6*1440,LEFT(G6,FIND(".",G6)-1)*1440+RIGHT(G6,LEN(G6)-FIND(".",G6))*1440),0)
Hope this helps.
Pete
Two great but different solutions from Pete_UK and daddylonglegs. Thank you so much for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks