Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
Hi,
I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
First try formating the cells as date. 31259 when formated aas a date is
7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
On 14 May, 13:01, Joel wrote:
First try formating the cells as date. 31259 when formated aas a date is 7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot The cells are formatted as date. Dates are displayed as normal. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
Check to make sure there isn't a single quote in front of the numbers which
would turn them into text. Is the date then 31259 really 3/12/59 ? "diver_sol" wrote: On 14 May, 13:01, Joel wrote: First try formating the cells as date. 31259 when formated aas a date is 7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot The cells are formatted as date. Dates are displayed as normal. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
On 14 May, 13:24, Joel wrote:
Check to make sure there isn't a single quote in front of the numbers which would turn them into text. Is the date then 31259 really 3/12/59 ? "diver_sol" wrote: On 14 May, 13:01, Joel wrote: First try formating the cells as date. 31259 when formated aas a date is 7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot The cells are formatted as date. Dates are displayed as normal. To give an example, one cell displays '12th Spetember 2007', in the forumla bar, the cell has 12/10/2007 but in when used in a logic statement, the cell has the value 32514 (or something similar) Which formala should i be using? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
Always use the date. For example
=(A1 4/5/07,TRUE,FALSE) When you debug a formula with a date it will show up as the 31259. Excel treats dates justt like number, except it will display the date on the worksheet when the cells arre formated as dates. "diver_sol" wrote: On 14 May, 13:24, Joel wrote: Check to make sure there isn't a single quote in front of the numbers which would turn them into text. Is the date then 31259 really 3/12/59 ? "diver_sol" wrote: On 14 May, 13:01, Joel wrote: First try formating the cells as date. 31259 when formated aas a date is 7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot The cells are formatted as date. Dates are displayed as normal. To give an example, one cell displays '12th Spetember 2007', in the forumla bar, the cell has 12/10/2007 but in when used in a logic statement, the cell has the value 32514 (or something similar) Which formala should i be using? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
On 14 May, 13:56, Joel wrote:
Always use the date. For example =(A1 4/5/07,TRUE,FALSE) When you debug a formula with a date it will show up as the 31259. Excel treats dates justt like number, except it will display the date on the worksheet when the cells arre formated as dates. "diver_sol" wrote: On 14 May, 13:24, Joel wrote: Check to make sure there isn't a single quote in front of the numbers which would turn them into text. Is the date then 31259 really 3/12/59 ? "diver_sol" wrote: On 14 May, 13:01, Joel wrote: First try formating the cells as date. 31259 when formated aas a date is 7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot The cells are formatted as date. Dates are displayed as normal. To give an example, one cell displays '12th Spetember 2007', in the forumla bar, the cell has 12/10/2007 but in when used in a logic statement, the cell has the value 32514 (or something similar) Which formala should i be using? Thanks, So, if i want to say, under the january column, 'display all the dates in january from Column X', how would I write the statement? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
you could use the month function
=if(month(A1) = 1,true,false) using an advance filter =month(X1:X100) = 1 "diver_sol" wrote: On 14 May, 13:56, Joel wrote: Always use the date. For example =(A1 4/5/07,TRUE,FALSE) When you debug a formula with a date it will show up as the 31259. Excel treats dates justt like number, except it will display the date on the worksheet when the cells arre formated as dates. "diver_sol" wrote: On 14 May, 13:24, Joel wrote: Check to make sure there isn't a single quote in front of the numbers which would turn them into text. Is the date then 31259 really 3/12/59 ? "diver_sol" wrote: On 14 May, 13:01, Joel wrote: First try formating the cells as date. 31259 when formated aas a date is 7/31/85. It is the number of days since Jan 1, 1900. "diver_sol" wrote: Hi, I have a column (e.g. Column X) that lists a series of dates, entered by the user. I have a table array (Columns A-L) of all the months, Jan-Dec. What I want to be able to do is, under each month in the table, look into Column X and list all the dates within Column X that are relevant to that month. I've tried LOOKUP but because dates are formatted like 31259 I dont know how to write the logic. Can anyone help? Thanks a lot The cells are formatted as date. Dates are displayed as normal. To give an example, one cell displays '12th Spetember 2007', in the forumla bar, the cell has 12/10/2007 but in when used in a logic statement, the cell has the value 32514 (or something similar) Which formala should i be using? Thanks, So, if i want to say, under the january column, 'display all the dates in january from Column X', how would I write the statement? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
Thanks for your replies, im just a bit confused, how does those
forulae work exactly? I have my dates in Column X7:X100 I have a 'January' column starting at A5. I want to display all the January dates in X7:X100 in a list starting at A5, all the feburary dates in a list starting at B5 etc etc . Could you explain your answer in a bit more detail? Thanks a lot |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP/List Help
I think you need in Data- filter - auto filter or advance filter. I don't
know if you want to copy from column x to column A just the January values, or if you want to reference the values in column X. I think you want to copy column X using the advance filter. The advance filter would be month() = 1 "diver_sol" wrote: Thanks for your replies, im just a bit confused, how does those forulae work exactly? I have my dates in Column X7:X100 I have a 'January' column starting at A5. I want to display all the January dates in X7:X100 in a list starting at A5, all the feburary dates in a list starting at B5 etc etc . Could you explain your answer in a bit more detail? Thanks a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Values in a list | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Lookup and list | Excel Discussion (Misc queries) | |||
Lookup and List Results | Excel Worksheet Functions | |||
Lookup List to Index | Excel Worksheet Functions |