Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formulas to pull date and time from 1 cell to 2 | Excel Worksheet Functions | |||
formulas using date/time formats returning #value | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |