The second issue seems to have some inconsistencies in the explanation.
However one solution is to build a table of two columns. The first column
would consist of times starting at 12:00 AM, 12:30 AM, 1:00 AM. these must
be actual time values. The second column would contain the text you want to
display in cell K2. Here is where you may need better clarification. In
which time frame will times on the half hour reside? (Is 2:30am part of
2-2:30 or 2:30-3:00?)

In this example the table is M2:N49 and the times will advance to the next
row at 30 minute intervals so that 11:30am will return 11:30am-12:00pm and
12:00pm will return 12:00pm-12:30pm.

Then in K2 enter this formula:

=VLOOKUP(TIMEVALUE(LEFT(J2,LEN(J2)-2)&" "&RIGHT(J2,2)),M2:N49,2,1)



Hope this helps.





"Karen" wrote in message
...
I have a date (example: 4/1/2009) in cell A3. When I enter a date, I want
the
cell B3 to populate with the corresponding day. In this case it would be
Wednesday. What is the formula to accomplish this?

Also, I have to enter a time in J2 (example: 10:56am) - (Not sure if this
matters, but it must be entered without the space and the am or pm in
lowercase) I want a formula to populate K2 in increments of 30 minutes.
So,
when I enter 10:56am, I want K2 to display 10:30a - 11a (Exactly like
that) -
If I enter 1:15am, I want it to display, 1am - 1:30am)

Would someone please help me with this?
Thanks, Karen