Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Range A1:JA1 has all our work days for the year (holidays and weekends are
excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen
wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff Try this formula in cell Q32: =MAX((A1:JA1)*(MONTH(A1:JA1)=P32)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen
wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff q32: =LOOKUP(2,1/(MONTH(A1:JA1)=P32),A1:JA1) --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Try this...
Assuming the dates are for the year 2010. =LOOKUP(DATE(2010,P32+1,0),A1:AJ1) Format as Date -- Biff Microsoft Excel MVP "Jeff Jensen" wrote in message ... Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Thanks T. Valko,
I gave it a try but for some reason this is only working month 1 & 2 but it doesn't for 3 - 12. Thanks again, Jeff "T. Valko" wrote: Try this... Assuming the dates are for the year 2010. =LOOKUP(DATE(2010,P32+1,0),A1:AJ1) Format as Date -- Biff Microsoft Excel MVP "Jeff Jensen" wrote in message ... Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Thanks Ron, works like a charm!
Thanks again, Jeff "Ron Rosenfeld" wrote: On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff q32: =LOOKUP(2,1/(MONTH(A1:JA1)=P32),A1:JA1) --ron . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Thanks Lars,
I gave it a try but I get a #VALUE! error. I did enter it as an array. Thanks, Jeff "Lars-Ã…ke Aspelin" wrote: On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff Try this formula in cell Q32: =MAX((A1:JA1)*(MONTH(A1:JA1)=P32)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Hmmm...
I assume the dates are in ascending order? Works just fine for me. Oh, well! -- Biff Microsoft Excel MVP "Jeff Jensen" wrote in message ... Thanks T. Valko, I gave it a try but for some reason this is only working month 1 & 2 but it doesn't for 3 - 12. Thanks again, Jeff "T. Valko" wrote: Try this... Assuming the dates are for the year 2010. =LOOKUP(DATE(2010,P32+1,0),A1:AJ1) Format as Date -- Biff Microsoft Excel MVP "Jeff Jensen" wrote in message ... Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
On Thu, 7 Jan 2010 06:59:02 -0800, Jeff Jensen
wrote: Thanks Ron, works like a charm! Thanks again, Jeff You're welcome. Glad to help. Thanks for the feedback. --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Make sure that you don't have any non valid dates, like 2010-01-35 in
the range A1:AJ1 Lars-Åke On Thu, 7 Jan 2010 07:01:03 -0800, Jeff Jensen wrote: Thanks Lars, I gave it a try but I get a #VALUE! error. I did enter it as an array. Thanks, Jeff "Lars-Åke Aspelin" wrote: On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff Try this formula in cell Q32: =MAX((A1:JA1)*(MONTH(A1:JA1)=P32)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke . |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Lars, I see what the problem was - some last cells had "", I changed that to
0 and it works now. I hoped your way would work because I thought I could just change MAX to MIN in order to get the smallest date as well. But that didn't work. Do you know a way to get the smallest date? Thank you, Jeff "Lars-Ã…ke Aspelin" wrote: Make sure that you don't have any non valid dates, like 2010-01-35 in the range A1:AJ1 Lars-Ã…ke On Thu, 7 Jan 2010 07:01:03 -0800, Jeff Jensen wrote: Thanks Lars, I gave it a try but I get a #VALUE! error. I did enter it as an array. Thanks, Jeff "Lars-Ã…ke Aspelin" wrote: On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff Try this formula in cell Q32: =MAX((A1:JA1)*(MONTH(A1:JA1)=P32)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke . . |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Try this formula for the smallest date:
=MIN((IF(A1:JA1<1,99999,A1:JA1)*(IF(MONTH(A1:JA1)= P32,1,2)))) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke On Thu, 7 Jan 2010 13:24:01 -0800, Jeff Jensen wrote: Lars, I see what the problem was - some last cells had "", I changed that to 0 and it works now. I hoped your way would work because I thought I could just change MAX to MIN in order to get the smallest date as well. But that didn't work. Do you know a way to get the smallest date? Thank you, Jeff "Lars-Åke Aspelin" wrote: Make sure that you don't have any non valid dates, like 2010-01-35 in the range A1:AJ1 Lars-Åke On Thu, 7 Jan 2010 07:01:03 -0800, Jeff Jensen wrote: Thanks Lars, I gave it a try but I get a #VALUE! error. I did enter it as an array. Thanks, Jeff "Lars-Åke Aspelin" wrote: On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff Try this formula in cell Q32: =MAX((A1:JA1)*(MONTH(A1:JA1)=P32)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke . . |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last day of month depending on criteria
Thanks Lars,
Works Great! Thanks again, Jeff "Lars-Ã…ke Aspelin" wrote: Try this formula for the smallest date: =MIN((IF(A1:JA1<1,99999,A1:JA1)*(IF(MONTH(A1:JA1)= P32,1,2)))) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke On Thu, 7 Jan 2010 13:24:01 -0800, Jeff Jensen wrote: Lars, I see what the problem was - some last cells had "", I changed that to 0 and it works now. I hoped your way would work because I thought I could just change MAX to MIN in order to get the smallest date as well. But that didn't work. Do you know a way to get the smallest date? Thank you, Jeff "Lars-Ã…ke Aspelin" wrote: Make sure that you don't have any non valid dates, like 2010-01-35 in the range A1:AJ1 Lars-Ã…ke On Thu, 7 Jan 2010 07:01:03 -0800, Jeff Jensen wrote: Thanks Lars, I gave it a try but I get a #VALUE! error. I did enter it as an array. Thanks, Jeff "Lars-Ã…ke Aspelin" wrote: On Wed, 6 Jan 2010 14:13:02 -0800, Jeff Jensen wrote: Range A1:JA1 has all our work days for the year (holidays and weekends are excluded). In P32 I want to enter the number of a month (i.e. 1 = Jan, 2 = Feb, etc.) and have a formula in Q32 that looks in A1:JA1 and returns the largest date for the month I entered in P32. Thank you, Jeff Try this formula in cell Q32: =MAX((A1:JA1)*(MONTH(A1:JA1)=P32)) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Ã…ke . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help - subtotal using vlookup depending on variable month | Excel Discussion (Misc queries) | |||
Conditional SUM of column values depending on the heading (month) | Excel Discussion (Misc queries) | |||
Populate, Depending on Criteria | Excel Discussion (Misc queries) | |||
How do I add totals from a range of dates depending on the month? | Excel Worksheet Functions | |||
Display number of day of month depending on the year | Excel Discussion (Misc queries) |