LOOKUP/List Help

[diver_sol]

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

 

[Guest]

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]

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.

The cells are formatted as date. Dates are displayed as normal.

 

[Guest]

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]

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 ?

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?

 

[Guest]

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]

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.

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?

 

[Guest]

you could use the month function
=if(month(A1) = 1,true,false)

using an advance filter
=month(X1:X100) = 1

 

[diver_sol]

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

 

[Guest]

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