Time & Date Formulas
 

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

Time & Date Formulas
 

In B3 anter
=A3 and format to dddd
--
Gary''s Student - gsnu200852


"Karen" wrote:

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

Time & Date Formulas
 

Gary''s Student wrote...
In B3 anter
=A3 and format to dddd
....

If the OP needs to use B3 in other formulas, maybe better that the B3
formula were

=TEXT(A3,"dddd")

with no special number formatting needed.

Time & Date Formulas
 

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

Time & Date Formulas
 

Correction:
The formula should include an additional blank row in the lookup table.
=VLOOKUP(TIMEVALUE(LEFT(J2,LEN(J2)-2)&" "&RIGHT(J2,2)),M2:N50,2,1)

"Bassman62" wrote in message
...
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

Time & Date Formulas
 

Karen wrote...
....
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) . . .

So it has to appear like that in cell J2? If so, you need trickery to
achieve your indicated format. Specifically, you'd need the custom
number format

[<0.5]hh:mm"am";[<1]hh:mm"pm";;

This will only display times, i.e., date values = 0 and < 1.

. . . 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)

Your specs are inconsistent: 10:30a - 11a without m but 1am - 1:30am
with m.

K2:
=SUBSTITUTE(SUBSTITUTE(TEXT(INT(J2*48)/48,"h:mm& - ")
&TEXT(INT(J2*48+1)/48,"h:mm&"),":00",""),"&",IF(J2<0.5,"am","pm"))

Time & Date Formulas
 

Thanks Harlan! I used this and it's returning "TRUE" Do you know what happened?
Thanks, Karen

"Harlan Grove" wrote:

Gary''s Student wrote...
In B3 anter
=A3 and format to dddd
....

If the OP needs to use B3 in other formulas, maybe better that the B3
formula were

=TEXT(A3,"dddd")

with no special number formatting needed.