Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
Hi,
I have in column A descending dates as: Mar 1/11 Feb 1/11 Jan 3/11 Dec 1/10 Nov 1/10 Oct 1/10 Sep 1/10 Aug 2/10 Jul 1/10 Jun 1/10 May 3/10 Apr 1/10 Mar 1/10 Feb 1/10 Jan 4/10 Dec 1/09 Nov 2/09 I need a formula, that given a month (as number, 12 for example in cell B1) and the year (2009 for example, in cell B2) gives me the position of the matching date (16). I think it should be and array formula= Match(...... Thanks Mika |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
Try this:
=MATCH(DATE(B2,B1,1),A:A,0) This returns the relative position of the matched item, so I'm assuming your dates start in A1. Do not put the formula in column A. Hope this helps. Pete On Apr 3, 6:57*pm, TechTrend wrote: Hi, I have in column A *descending dates as: Mar 1/11 Feb 1/11 Jan 3/11 Dec 1/10 Nov 1/10 Oct 1/10 Sep 1/10 Aug 2/10 Jul 1/10 Jun 1/10 May 3/10 Apr 1/10 Mar 1/10 Feb 1/10 Jan 4/10 Dec 1/09 Nov 2/09 I need a formula, that given a month (as number, 12 for example in cell B1) and the year (2009 for example, in cell B2) gives me the position of the matching date (16). I think it should be and array formula= Match(...... Thanks Mika |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
On Apr 3, 6:57 pm, TechTrend wrote:
I have in column A descending dates as: Mar 1/11 [....] Jan 4/10 Dec 1/09 Nov 2/09 [....] I need a formula, that given a month (as number, 12 for example in cell B1) and the year (2009 for example, in cell B2) gives me the position of the matching date (16). On Apr 3, 1:02*pm, Pete_UK wrote: =MATCH(DATE(B2,B1,1),A:A,0) I presume Pete means: =MATCH(DATE(B2,B1,1),A:A,-1) Note that not all dates in column A are on day 1. The -1 MATCH option finds the smallest date greater than equal to month/1/year. That assumes that column has try numeric dates that are formatted as Custom "mmm d/yy" without quotes. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
On Apr 3, 10:22*pm, joeu2004 wrote:
On Apr 3, 6:57 pm, TechTrend wrote: I have in column A *descending dates as: Mar 1/11 [....] Jan 4/10 Dec 1/09 Nov 2/09 [....] I need a formula, that given a month (as number, 12 for example in cell B1) and the year (2009 for example, in cell B2) gives me the position of the matching date (16). On Apr 3, 1:02*pm, Pete_UK wrote: =MATCH(DATE(B2,B1,1),A:A,0) I presume Pete means: =MATCH(DATE(B2,B1,1),A:A,-1) Note that not all dates in column A are on day 1. *The -1 MATCH option finds the smallest date greater than equal to month/1/year. That assumes that column has try numeric dates that are formatted as Custom "mmm d/yy" without quotes. Thanks Pete and Joeu Indeed had to use -1 instead of 0. Cheers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
On Apr 3, 10:39*pm, TechTrend wrote:
On Apr 3, 10:22*pm, joeu2004 wrote: On Apr 3, 6:57 pm, TechTrend wrote: I have in column A *descending dates as: Mar 1/11 [....] Jan 4/10 Dec 1/09 Nov 2/09 [....] I need a formula, that given a month (as number, 12 for example in cell B1) and the year (2009 for example, in cell B2) gives me the position of the matching date (16). On Apr 3, 1:02*pm, Pete_UK wrote: =MATCH(DATE(B2,B1,1),A:A,0) I presume Pete means: =MATCH(DATE(B2,B1,1),A:A,-1) Note that not all dates in column A are on day 1. *The -1 MATCH option finds the smallest date greater than equal to month/1/year. That assumes that column has try numeric dates that are formatted as Custom "mmm d/yy" without quotes. Thanks Pete and Joeu Indeed had to use -1 instead of 0. Cheers The formula =MATCH(DATE(B2,B1,1),A1:A100,-1) works very well except when the date (date(b2,b1,1) does not exist in the range a1:a100. In that case it gives me the position of the last row with data (A100). Is there a way to modify the formula to get in those cases #N/A or a text "Don't exist" ?? Cheers Mika |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
On Apr 8, 5:42*am, JIL wrote:
The formula =MATCH(DATE(B2,B1,1),A1:A100,-1) *works very well except when the date (date(b2,b1,1) does not exist in the range a1:a100. In that case it gives me the position of the last row with data (A100). No. It gives the position of the earliest date greater than or equal to DATE(B2,B1,1). That will be the last position only if DATE(B2,B1,1) is earlier than the last date in the range. It will be #N/A if DATE(B2,B1,1) is later (more recent) than the first date in the range. Mika wrote: Is there a way to modify the formula to get in those cases #N/A or a text "Don't exist" ?? You question is unclear. If you simply want "doesn't exist" if the month/year in B1:B2 is earlier than the last month/year represented in A1:A100, then: =IF(DATE(B2,B1,1)<A100-DAY(A100), "doesn't exist", MATCH(DATE(B2,B1,1),A1:A100,-1)) If you also want "doesn't exist" if B1:B2 is later (more recent) than the first month/year represented in A1:A100, then: =IF(OR(DATE(B2,B1,1)<A100-DAY(A100),DATE(B2,B1,0)A1-DAY(A1)), "doesn't exist", MATCH(DATE(B2,B1,1),A1:A100,-1)) or for XL2007 and later: =IF(DATE(B2,B1,1)<A100-DAY(A100), "doesn't exist", IFERROR(MATCH(DATE(B2,B1,1),A1:A100,-1), "doesn't exist")) However, if you also want "doesn't exist" there are month/years not represented in the middle of A1:A100, then: =IF(SUMPRODUCT(--(DATE(B2,B1,0)=A1:A100-DAY(A1:A100))), MATCH(DATE(B2,B1,1),A1:A100,-1), "doesn't exist") |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find the position of a date
Perfect! , Thanks again
This is what I was looking for: "If you also want "doesn't exist" if B1:B2 is later (more recent) than the first month/year represented in A1:A100, then: =IF(OR(DATE(B2,B1,1)<A100-DAY(A100),DATE(B2,B1,0)A1-DAY(A1)), "doesn't exist", MATCH(DATE(B2,B1,1),A1:A100,-1)) Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you find the position of the first value (< 0) in a list | Excel Discussion (Misc queries) | |||
Array to find relative position - is there a better way? | Excel Worksheet Functions | |||
How do I find the position of the lowest value in a row? | Excel Discussion (Misc queries) | |||
find nth position of a string | Excel Discussion (Misc queries) | |||
Use MATCH to find position of max in 2D range? | Excel Worksheet Functions |