Extracting Hour:Min from Date Hour:Min cell

[MaritaFL]

I have a very large CSV file (many thousands of lines) where time values are coming in with the following format:
<TABLE style="WIDTH: 182pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=242 border=0><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=129 height=20>TR Tran Start Time</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 85pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=113>TR Tran End Time</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>4/4/10 7:20 PM</TD><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>4/4/10 8:54 PM</TD></TR></TBODY></TABLE>

I need to extract just the AM/PM hour and minutes to match against a criteria, like does either the start or end time fall within a certain range in the format 5:00 PM. etc.

I tried Right(xxx,yyyy), and some other things to no avail. I keep getting 12:00 AM or just a serial number.

Does anyone have any suggestions?

Thanks,
Marita

 

[JB_Scotland]

How are the data cells formatted? Are they time or text?

 

[MaritaFL]

Time

 

[barry houdini]

In Excel dates/times are simply formatted numbers, the integer part is the date and the decimal part is the time....so if you have a date/time in A1 then you can extract just the time part by using this formula

=MOD(A1,1)

format result cell as h:mm or similar

 

[JB_Scotland]

Just change the time format to one you want i.e. hh:mm and make sure your criteria are of the same format.

The date / time is recorded in Excel as a number with 1 = one day (24 hours) so you are only interested in the decimal part. If you want to see the underlying serial numbers format your data as numbers.

 

[MaritaFL]

Thanks, but that still leaves the date part in. I just tried that, it gives me this:

<TABLE style="WIDTH: 298pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=397 border=0><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 85pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=113 height=20>4/4/10 9:02 PM</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 91pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=121>1/1/1970 2:22</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 74pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=99>1/0/00 9:02 PM</TD></TR></TBODY></TABLE>

Just formatting does not help, I need it to be in text format 9:02PM

 

[JB_Scotland]

=TEXT(A1,"h:mmAM/PM") where A1 is your Date / Time data

 

[barry houdini]

Using TEXT function will return a text value, not a time.....

If you get this result

1/0/00 9:02 PM

Then that's fine, it's just a formatting issue.....

the 1/0/00 is effectively a "zero date", you only have the time value in the cell, whether it's formatted to show the date or not....

Test this by comparing against a time value. If the formula is in C1 then try

=C1>TIME(9,0,0)

that should return TRUE because C1 = 09:02 PM....but if you use

=C1< TIME(10,0,0)

that will also return TRUE

so the value behaves like a time....which is what you want......

 

[MaritaFL]

Thanks to both of you, I will give that a try.

 

[MaritaFL]

It worked, YAY. Then again, I asked the BEST!

Thanks again.

Marita