Hello!
I have a raw report that outputs time in h:mm format. For Example 1h46m. How do I convert this to minutes? I did get this to work by creating a cheat column and using the Text to Column feature and multiplying the hours by 60, then another column and added both minutes and minutes for a sum. Is there an easier way or formula to accomplish this?
JonnyP47.
Try.
Please Login or Register to view this content.
Best Regards,
Maras.
The formula did work, but it in the example of 1h 09m the formula calculated 60. I don't feel like it didn't account for the m.
Another example of 1h 53m it calculated 64.
How well did you understand how maras_mak's formula works? These "failed" examples appear to have a space in between the hour and minute values, which was not indicated in the OP, and maras_mak's formula did not account for the possible presence of these spaces. Before I make any recommendation, I would wonder if there are any other variations possible. This is bacially a "extract numbers from a text string" kind of problem, and I would want to make sure we know all of the possible text variations in order to get a robust formula.
Originally Posted by shg
My apologies. My level of excel is basic and as a result I did not have a fully understanding of his formula. I am here to learn and glean the knowledge from other users.
I have attached a sample data set to illustrate.
=IFERROR(60*LEFT(D2,FIND("h",D2)-1)+MID(D2,FIND("h",D2)+1,2),LOOKUP(9^9,LEFT(D2,{1,2,3})+0)*IF(ISERROR(SEARCH("h",D2)),1,60))
try above formulaHTML Code:
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thank you so much. Here are the results. It appears that the last digit if the minutes are two numbers, only calculating first number. This is very exciting!
1h 11m 61
45m 45
2h 04m 120
2h 16m 121
2h 18m 121
7m 7
1h 16m 61
51m 51
sorry try
I think that it is not working where there is only H or Only MPlease Login or Register to view this content.
Last edited by samba_ravi; 05-14-2020 at 08:35 PM.
That is INCREDIBLE. That worked! What is the best way to learn these formulas samba_ravi?
or can try
=SUBSTITUTE(IF(COUNTIF(D2,"*h*")=0,"0:","")&SUBSTITUTE(SUBSTITUTE(D2,"h",":"),"m","")&IF(COUNTIF(D2,"*m*")=0,"00","")," ","")*60*24
Another one
F2
=SUMPRODUCT(MID(0&D2&"00h00m",FIND({"h","m"},0&D2&"00h00m")-2,2)*{60,1})
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks