Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel - Extracting Hours and Minutes from a Date


(!)

Gogs's Avatar
Gogs Gogs is offline
Member with 121 posts.
THREAD STARTER
 
Join Date: Jul 2003
14-Mar-2008, 08:58 AM #1
Excel - Extracting Hours and Minutes from a Date
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's Avatar
Member with 4,697 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
14-Mar-2008, 09:35 AM #2
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

Last edited by Rollin_Again; 14-Mar-2008 at 09:41 AM..
CastleHeart's Avatar
CastleHeart CastleHeart is offline
Senior Member with 743 posts.
 
Join Date: May 2002
Location: Coast of North Carolina
14-Mar-2008, 12:41 PM #3
= 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's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
14-Mar-2008, 12:42 PM #4
Use the TIME() function, sort on that column.
bomb #21's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
14-Mar-2008, 07:07 PM #5
Quote:
Originally Posted by zack
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's Avatar
maxflia10 maxflia10 is offline
Member with 331 posts.
 
Join Date: Feb 2003
15-Mar-2008, 03:38 PM #6
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's Avatar
Member with 8,268 posts.
 
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
15-Mar-2008, 04:18 PM #7
Even better ; nice one, Brian.
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
15-Mar-2008, 04:51 PM #8
[QUOTE=CastleHeart;5701356]
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's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
16-Mar-2008, 01:05 AM #9
Hiya Brian! Long time no see. How's my favorite island doing? Holding down the fort?

Take care
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑