# Seperate hours and minutes & seconds

1. ## Seperate hours and minutes & seconds

Is there an easy way to convert the a list of time values shown below separated with the colons to 3 separate columns without colons and just number values.

07:10:35
08:12:58

When I try the left function or text to columns I get strange values

Many thanks

2. ## Re: Seperate hours and minutes & seconds

See HOUR, MINUTE, SECOND functions

B1: =HOUR(A1)
C1: =MINUTE(A1)
D1: =SECOND(A1)
format B1:D1 as 00 if you want to see leading 0's

(assumes values in A1 onwards < 24 hours ie 25:32:28 would return 1 for HOUR - let us know if this an issue)

3. ## Re: Seperate hours and minutes & seconds

Many thanks, did not work at first but I realized this must be a format issue, as when I copy the original source data to a new column your formulas work ok

Changing the format did not work, but copy and past of the source data to anew column sorted it.

When I first tried I got 00:00:00 on all 3 functions.

4. ## Re: Seperate hours and minutes & seconds

When I first tried I got 00:00:00 on all 3 functions.
Yes, if formatted as Time that it is to be expected given HOUR, MINUTE & SECOND return Integers and in XL Integers equate to Days, Time is Decimal.

To elaborate by means of Example:

``Please Login or Register  to view this content.``
B1 will show as 0.5 - this is because 12 hours equates to 1/2 a day (as 6 am is 0.25 and 6pm = 0.75)

C1 will show as 12 as the Hour is extracted as an Integer.

If C1 is formatted to say h:mm:ss it will show as 0:00:00 given 12 in XL DateTime terms equates to 12 days (288 hours) and once full days have been accounted for there is no Time remainder.
Were you to format the cell as [h]:mm:ss you would see it return 288:00:00 - ie the cumulative hours would be displayed.

The above has probably confused you more but in essence the key is that HOUR, MINUTE & SECOND return non-time values and the results should thus be formatted as General / Number and not Time.

There are currently 1 users browsing this thread. (0 members and 1 guests)