Extracting Hours

[sarahma]

Hi,


I have a column named "Elapsed Open Time' and the data is in the format HH:MM:SS.


The HH is not restricted to max limit of 24. So, a sample data is "34:20:12" meaning the particular record is 34 hours 20 min and 12 seconds old.


I need to extract the HH value alone(in this case, only 34)


When I try to change it using TEXT()and extract using LEFT(), The value 34:20:12 gets converted to 10:20:12.


Help required in extracting HH(34).

 

[Hui]

Sarahma


Firstly, Welcome to the Chandoo.org Forums


If you want to just display the hours use: =INT(A1*24)/24

and apply a display format of HH


If you want to use the hours elsewhere as a Number

=INT(A1*24)

 

[sarahma]

Hi Hui,


Thanks for replying & welcoming me to the Forum.


When I tried using the formula you have suggested, the following happens:


A1 = 34:02:04 (The formula bar shows: 1/1/1900 10:02:04 AM) - got from data dump

B1 = INT(A1*24)/24 gives me 10:00:00


But, I want the B1 value to be 34:00:00 and not the value that subtracts 24 hours.

Is this possible?


And, Is the data format correct for Cell A1?

 

[Kevin@Radstock]

Hi sarahma


Assuming your data is in A1, try: =INT(A1)*24+HOUR(A1)

 

[Hui]

Change the Display Format in A1 to [h]:mm:ss

 

[Faseeh]

Hi sarahma,


Assuming everything else remains the same, use this formula:

=INT(TEXT(A1,"@")*24)&":00:00"

Regards,

 

[SirJB7]

Hi, sarahma!


You should have your A1 cell formatted as:

[h]:mm:ss

otherwise you wouldn't see 34:10:20 as a time value, only as a string value.


So if this is the case try this:

=VALOR(TEXTO(A1;"[h]")) -----> in english: =VALUE(TEXT(A1,"[h]")


Regards!

 

[sarahma]

Hi All,


The Formula given by Faseeh Worked. It Gave what I wanted.


Thanks Everyone!