Extract the hour from the h" hour(s) and "m" minute(s)" in excel-CodePudding

I have the list of data that showing the Hours and the Minutes that I extract from the system. I need to be extract the hours. As example below, column B first row, the Hours would be 64 and the minutes would be 46.

But when I used the formula =Hour , its turn up the different value since its actually decimal number.

Cannot use left() , it will give the actual decimal number.

Updated:

We tried the @harun24HR 's but cannot readable the value. But if you noticed, if i copy and paste the value is different. thats why the search not applicable.

4th Update: To Solar Mike, I have tried the formula given from the thread the i think the value not readable

CodePudding user response：

It's a time value which Excel stores as calculated value based on 24 hours = 1.

To retrieve the hours only you can use: =INT(A2*24)

To retrieve the minutes only you can use: =(A1-(INT(A1*24)/24))*24*60

CodePudding user response：

From your current sample data you try-

For hour =LEFT(A2,SEARCH(" ",A2)-1)
For minutes =RIGHT(SUBSTITUTE(A2," minutes",""),2)

CodePudding user response：

Your time value is already a number in time format so you just need it to change it to decimal system. Dates and time values are numbers. Even if you see it as 30/09/2019 or 12:00:00, actually, for Excel, both cases are numbers.

First date Excel can recognize properly is 01/01/1900 which integer numeric value is 1. Number 2 would be 02/01/1900 and so on. Actually, today is 44659.

Now, about your data, you posted this screenshoot:

So the value is numeric, not text/string. In Excel, what you see is not always what you have. Probably that column has been formatted using custom mask. My fake data is like this:

The numeric value is 02/01/1900 16:46:36 (or 02/01/1900 4:46:36 PM it depends on your regional settings) but I've applied a custom format like this:

[hh]" hours" mm " minutes"

So what I have is a number but what I see is a text!

We have to work with the number (to be honest, ist's easier to work with numbers), so to extract the total hours, minutes and seconds.

Formula in B1: =INT(A1*24) total hours

Formula in C1: =INT(A1*24*60-B1*60) total minutes

Formula in D1: =A1*24*60*60-B1*60*60-C1*60 total seconds

This should guide you on whatever are you trying to achieve.