# Excel - Extracting Hours and Minutes from a Date



## Gogs (Jul 28, 2003)

Hi,

I have a column that contains a date and time in the dd/mm/yy hh:mm format
what I want to do is extract the hh:mm bit into the column next to it so I can then sort by the time. I have tried putting in C2: =B2 for example and formatting it as hh:mm which displays what I want but when I sort it it seems to remember the "hidden" dd/mm/yy bit so i have it in time order but for each day, which is not what i want.

I have also tried in C2: =RIGHT(B2,5) but that converts it into a number, regardless of what format I try.

Any ideas?

Gogs


----------



## Rollin_Again (Sep 4, 2003)

Copy the entire column to a blank "helper" column. Next select the newly copied column and then click *DATA --> TEXT TO COLUMNS* and manually add a delimeter between the date and time to break into seperate cells.

Regards,
Rollin


----------



## CastleHeart (May 4, 2002)

*= TEXT(A1,"hh:mm")* will separate hours and minutes out. But you wouldn't be able to sort by it

you could do a *=HOUR(B11)+MINUTE(B11)/60* to get a number and that SHOULD sort although I just tried it and got strange results. Don't have time to mess now but maybe there is something there you can use.

- Castleheart


----------



## Zack Barresse (Jul 25, 2004)

Use the TIME() function, sort on that column.


----------



## bomb #21 (Jul 1, 2005)

zack said:


> Use the TIME() function, sort on that column.


My personal fave for this is INT, in that it underlines the concept that dates in Excel are just plain ol' numbers. Hence:

=value-INT(value)


----------



## maxflia10 (Feb 25, 2003)

Hey Zack and Andy,

I like MOD to separate time from date/time cell.

=MOD(A1,1)

and format the cell that houses the formula as Time...

Long time no see to both of you. Hope all is well with you and your families...


----------



## bomb #21 (Jul 1, 2005)

Even better ; nice one, Brian. :up:


----------



## slurpee55 (Oct 20, 2004)

CastleHeart said:


> you could do a *=HOUR(B11)+MINUTE(B11)/60* to get a number and that SHOULD sort although I just tried it and got strange results. Don't have time to mess now but maybe there is something there you can use./QUOTE]
> 
> Gotta say, I like the MOD as the neatest of these.
> BUt CH, I wondered why yours didn't work right - fooled around and got this to work
> =(HOUR(A1))&":"&(MINUTE(A1))


----------



## Zack Barresse (Jul 25, 2004)

Hiya Brian! Long time no see. How's my favorite island doing? Holding down the fort? 

Take care


----------

