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

 

[Greg Truby]

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

Erm, if this is a classwork assignment then we are doing you a terrible disservice by simply spoonfeeding you the answers. You'll never learn to stand on your own two feet if everyone is giving you piggyback rides everywhere you go.

If it's coursework and you can't figure this out from your notes, then another way to be self-sufficient would be to use the function wizard (the Insert Function button on the Formulas tab). For example: you need to determine if a date is a Saturday or a Sunday, right? If you type "determine saturday sunday" into the function wizard's search box, the first function it would recommend to you is the WEEKDAY function - exactly what JonMo has indicated.

The function wizard is bang useful - unlike Excel 2007 's help which is about as useful as trying to get directions from a drunk that's three sheets to the wind. (Excel 2010's help is better, not excellent, but better.)

 

[mwooster21]

Erm, if this is a classwork assignment then we are doing you a terrible disservice by simply spoonfeeding you the answers. You'll never learn to stand on your own two feet if everyone is giving you piggyback rides everywhere you go.

If it's coursework and you can't figure this out from your notes, then another way to be self-sufficient would be to use the function wizard (the Insert Function button on the Formulas tab). For example: you need to determine if a date is a Saturday or a Sunday, right? If you type "determine saturday sunday" into the function wizard's search box, the first function it would recommend to you is the WEEKDAY function - exactly what JonMo has indicated.

The function wizard is bang useful - unlike Excel help which is about as useful as trying to get directions from a drunk that's three sheets to the wind.

Thanks, but this isn't for school. It's for an internship. If I can't do it, I can't do it. No big deal.

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.

Well thats what I immediately thought of doing, but it doesn't take into account the range. I want to include things that start on Saturday or Sunday if the range encompasses a weekday.

For example: 7/24/11-7/24/11 (Sunday) would be excluded but 7/24/11-7/26/11 would be included.

 

[mwooster21]

Possibly some type of formula?

If Start Date = Saturday and End Date = Start Date or End Date = Start Date + 1

or If Start Date = Sunday and End Date = Sunday


sorry for piggybacking... I got it

Edit: Sorry for double post