Function to change m/d/yyy h:mm to just time

[mwooster21]

I have a spreadsheet with one column showing the start date and the next column showing the end date.

like so:
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=290><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" span=2 width=145><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 109pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 width=145 align=right>6/13/2011 9:00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 109pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=145 align=right>7/22/2011 22:00</TD></TR></TBODY></TABLE>

I am not interested in the date. I would like to strip out the date and find a way to filter based off of the time.

I need times between 14:00 and 18:30. I was thinking i could filter <= 18:30 in the end column and >=14:00 in the start column.

Does anyone have any ideas?

I am on Excel 2007

 

[Jonmo1]

Welcome to the board...

To extract just the time from a cell, use

=MOD(A1,1)
Format the cell with the formula in your desired time format.

Then filter on the column holding that formula.


Hope that helps.

 

[Joe4]

Let's say that your value is in cell A1.
Here is one formula that would do it (though I imagine there are probably better ones):
=TIMEVALUE(TEXT(A1,"hh:mm"))
just be sure to pick the appopriate date time format for the cell (otherwise it might just show up as a fraction, which is how Excel actually stores time)

 

[mwooster21]

Thanks both of you! I used yours jonmo and that worked.

 

[Joe4]

To extract just the time from a cell, use

=MOD(A1,1)
Format the cell with the formula in your desired time format.

Yep, there's one of those better ways I was talking about!
(I don't do a whole lot of time calculations!)

 

[T. Valko]

I have a spreadsheet with one column showing the start date and the next column showing the end date.

like so:
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=290 border=0><COLGROUP><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" span=2 width=145><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 109pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=145 height=17>6/13/2011 9:00</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 109pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=145>7/22/2011 22:00</TD></TR></TBODY></TABLE>

I am not interested in the date. I would like to strip out the date and find a way to filter based off of the time.

I need times between 14:00 and 18:30. I was thinking i could filter <= 18:30 in the end column and >=14:00 in the start column.

Does anyone have any ideas?

I am on Excel 2007

Maybe extract the time to other cells?

A2 = 6/13/2011 9:00

B2: =A2-INT(A2)

Format as Time

 

[Jonmo1]

Thanks both of you! I used yours jonmo and that worked.

Glad to help, thanks for the feedback.

Just a side note, to do the opposite..Extract DATE only from a cell containing both date and time..

=INT(A1)

Yep, there's one of those better ways I was talking about!
(I don't do a whole lot of time calculations!)

Always more than one way to skin a cat

 

[Joe4]

Always more than one way to skin a cat

Yep, Biff's function works too.
It is really just a manual MOD function, so uses the same logic as yours, just represented differently.

 

[mwooster21]

Hey again. There were more parameters added to the assignment.

So not only do I need to filter out times between 14:00-18:30, but I also need to exclude Saturday and Sunday. I added columns that have just the date now.

Now this would be simple if start and end dates were the same day, or if I could just exclude data that started on a weekend.

But how can I exclude data that is ONLY on the weekend and DOESN'T encompass anything Monday-Friday?

 

[Jonmo1]

Try putting a formula in an adjescent column.

=WEEKDAY(A1,2)<6

This will return TRUE for Mon-Fri, False for Sat-Sun

Then filter for TRUE on that column.