Find the First Date entry for a given month

  • Thread starter Thread starter Leo com>
  • Start date Start date
L

Leo com>

What I have is two columns of dates & times. Column 1 is
the start date/time for that day column 2 is the end
Date/time for that day. What I need to do is search column
1 and fine the first entry for a particular month, then
search Column 2 to find the last entry for that month.
These dates will enventually extend thruout the year, but
I want to be able to specify which month I am searching
for. Any help would be greatly appreciated. I have named
the first column (200 Lines) "BeginDate" and the second
column "EndDate".

Column 1 Column 2
1/21/2004 10:16 1/21/2004 21:16
1/27/2004 4:15 1/27/2004 23:10
1/28/2004 5:20 1/28/2004 5:31
1/28/2004 10:32 1/28/2004 10:43
1/28/2004 11:25 1/28/2004 13:03
1/29/2004 4:20 1/29/2004 23:08
1/30/2004 4:30 1/30/2004 23:18
2/1/2004 0:01 2/1/2004 0:01
2/29/2004 23:59 2/29/2004 23:59
3/4/2004 16:15 3/4/2004 23:27
3/8/2004 2:15 3/8/2004 10:31
3/11/2004 2:15 3/11/2004 23:15
 
Hi
first: be careful while posting: You have create numerous entries of
your post
---
To get the minimum value (lets say for January) from column 1 try
=MIN(IF(MONTH(A1:A100)=1,A1:A100))
enter this as array formula (CTRL+SHIFT+ENTER)

for the maximum use
=MAX(IF(MONTH(B1:B100)=1,B1:B100))
 
Thank You very much the code works great, with one
exception. When I do the "Min" for January the answer is
always 01/01/1900. is there something Special I need to do
to find the actual date, which in this case the first date
for January was 1/28/2004. Thank you Frank.

Leo
 
Hi Leo
i assume you have blank cells (which are evaluate to a date 1.1.1900).
Try the following instead:
=MIN(IF((MONTH(A1:A100)=1)*(A1:A100<>"")),A1:A100))
ans
=MAX(IF((MONTH(A1:A100)=1)*(A1:A100<>"")),A1:A100))
 
Back
Top