Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 913
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 913
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 913
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Please help - subtotal using vlookup depending on variable month robstton Excel Discussion (Misc queries) 2 February 5th 09 01:27 AM
Conditional SUM of column values depending on the heading (month) Pat Rob Excel Discussion (Misc queries) 1 November 12th 08 06:37 PM
Populate, Depending on Criteria Blobbies Excel Discussion (Misc queries) 1 September 27th 06 12:37 PM
How do I add totals from a range of dates depending on the month? confused Excel Worksheet Functions 3 September 12th 06 02:53 AM
Display number of day of month depending on the year Dn_ Excel Discussion (Misc queries) 1 May 24th 06 05:36 PM


All times are GMT +1. The time now is 07:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"