Extract time from date/time column

[Jeff]

Is there a way to extract the time from a date/time formatted column so I
can sort the list on the time? I'm using Excel 2007.

The data I'm trying to work with is here
http://www.nuforc.org/webreports/ndxlAZ.html

I want to import this table, extract the time from the date/time column and
then sort on the time column. My goal is to get a list of all events that
occured between 5am to 7am.

I figured out that I can simply create a new column, format it as Time, and
then copy the date/time column over. That will disply time only but I still
can't sort on it. Seems like it still sorts by date.

Jeff

 

[Niek Otten]

Time in Excel is stored as a fraction of a day.
So, with time and date in A1,

=MOD(A1,1)

gives you only the time

 

[Peo Sjoblom]

Use this formula to get the time only


=MOD(A1,1)


replace A1 with the first cell of your data and copy the formula down

most likely it will look like a date because excel thinks it is a date
but what is showing is Jan 0 1900 and the time so all you have to do is to
reformat
the cell as time and do your sorting





--


Regards,


Peo Sjoblom

 

[Bernard Liengme]

If A1 has date/time such as 1/1/2008 3:00 PM
In B1 use =INT(A1) and format Date (dd/mm/yyyy or whatever is your local
preference)
In C1 use =MOD(A1,1) and format as Time
Now you have the two measures date/time in separate columns because Excel
stores dates as serial numbers and time as fractions of a day
best wishes

 

[Jeff]

Great! Thanks!

Jeff

Time in Excel is stored as a fraction of a day.
So, with time and date in A1,

=MOD(A1,1)

gives you only the time

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[ShaneDevenshire]

Hi,

1. Select the column of Dates/Times and choose Data, Text to Columns, Fixed
width, Next, move the line to 10 and click Next, select the Date column in
the Preview area and choose Do not import column (skip), select the
destination cell and click Finish.

And just for fun:
2. In addition to MOD, which I agree is best you could use
=--MID(B2,6,15)
=--RIGHT(B3,11)
=B4-QUOTIENT(B4,1)

 

[Jeff]

Just found another one

with the date/time column in B2, go to A2 and enter =time(hour(b2),
minute(b2), second(b2))

drag that down column A, then format column A as Time

 

[Pete_UK]

Another one:

=B2-INT(B2)

Pete

Just found another one

with the date/time column in B2, go to A2 and enter =time(hour(b2),
minute(b2), second(b2))

drag that down column A, then format column A as Time

message





- Show quoted text -